Converting from .MDB Access to PostgreSQL

Converting from .MDB Access to PostgreSQL

Post by Bob Puf » Thu, 11 Jul 2002 08:41:21



Hello,

I'm somewhat a newbie to databases, but not to Linux or Windows.  Have a client that is running
software on the Win32 platform that is currently using ODBC to access .MDB "MS Access database
files" on a samba fileserver.  They are getting random record locking errors, and the software
vendor is blaming the number of computers, saying that the ODBC access to database files is only
good for a handful of computers, not the 8 that we have.

Their recommendation is to use MS SQL database, and set up the entries in the ODBC panel for the
clients to point to the MS server.  They provide a little utility to create and copy the .MDB file
data to the MS SQL database server.

I want to use PostgreSQL, not MS's product.  I've got the server set up, and have the ODBC drivers
installed on a test client, but now I need to get the data from the .MDB files into the PostgreSQL.
  There are several databases.

What is the most pain-free way one can accomplish this?  I think the Access database files are an
older format, because when I go to open them with MS Access 2000, it states that the files need to
be converted to the new format (which I obviously did not do - didn't want to break the app).

I assume there are two distinct functions that need to be done:
   1. Create the databases, with the appropriate tables and columns
   2. Move the data within the database.

I did do some searching on the site, but most of the stuff didn't quite "click".  Any advice given
would be greatly appreciated.  Thanks!

Bob

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

 
 
 

Converting from .MDB Access to PostgreSQL

Post by Lamar Ow » Thu, 11 Jul 2002 08:46:55



Quote:> What is the most pain-free way one can accomplish this?  I think the Access
> database files are an older format, because when I go to open them with MS
> Access 2000, it states that the files need to be converted to the new
> format (which I obviously did not do - didn't want to break the app).

PgAdmin can do the migration, if I remember correctly. In particular, there is
a database migration wizard plugin -- see
http://pgadmin.postgresql.org/pgadmin2.php?ContentID=15 for more details.
--
Lamar Owen
WGCR Internet Radio
1 Peter 4:11

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate

message can get through to the mailing list cleanly

 
 
 

Converting from .MDB Access to PostgreSQL

Post by Chris Alberts » Thu, 11 Jul 2002 09:54:11


I'm sure you can get MS Access to dump its data into a
comma or pipe delimited ASCII file.  If so, Postgresql's
COPY command (see "man copy") can read these files.  It is
mostly painless unless there are many tables.


<SNIP>

Quote:

> I assume there are two distinct functions that need to be done:
>    1. Create the databases, with the appropriate tables and columns
>    2. Move the data within the database.

> I did do some searching on the site, but most of the stuff didn't
> quite "click".  Any advice given
> would be greatly appreciated.  Thanks!

> Bob

> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

=====
Chris Albertson

  Cell:   310-990-7550

__________________________________________________
Do You Yahoo!?
Sign up for SBC Yahoo! Dial - First Month Free
http://sbc.yahoo.com

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

 
 
 

Converting from .MDB Access to PostgreSQL

Post by Patrick Welc » Thu, 11 Jul 2002 23:26:50



> I'm sure you can get MS Access to dump its data into a
> comma or pipe delimited ASCII file.

.. or even File / Save As/Export / External .. Database
Save as type: ODBC Databases
...
-> Data source pointing to the database on the postgresql server.

Cheers,

Patrick

---------------------------(end of broadcast)---------------------------

 
 
 

Converting from .MDB Access to PostgreSQL

Post by Booth, Rober » Fri, 12 Jul 2002 02:02:17


Bob,
        I just went through this exact process, and am still feeling the
pain of it.  So maybe I can be of some help.  I didn't know about the
pgAdminII conversion wizard and so did this myself with a little java app
that I wrote.  I'd suggest using the wizard
(http://pgadmin.postgresql.org/pgadmin2.php?ContentID=15) it'll make the
first step easier.

        If you run into any issues feel free to ask and I'll do my best to
help out.  Watch out for bad table/column names in your Access database.
Access allows for almost any characters to be used in the table/column names
and Postgres does not, unless you surround them with "".  If you do use
names that need to be quoted they will have to be quoted everywhere.

Rob

> -----Original Message-----

> Sent: Tuesday, July 09, 2002 4:50 PM

> Subject: [GENERAL] Converting from .MDB Access to PostgreSQL

> Hello,

> I'm somewhat a newbie to databases, but not to Linux or
> Windows.  Have a client that is running
> software on the Win32 platform that is currently using ODBC
> to access .MDB "MS Access database
> files" on a samba fileserver.  They are getting random record
> locking errors, and the software
> vendor is blaming the number of computers, saying that the
> ODBC access to database files is only
> good for a handful of computers, not the 8 that we have.

> Their recommendation is to use MS SQL database, and set up
> the entries in the ODBC panel for the
> clients to point to the MS server.  They provide a little
> utility to create and copy the .MDB file
> data to the MS SQL database server.

> I want to use PostgreSQL, not MS's product.  I've got the
> server set up, and have the ODBC drivers
> installed on a test client, but now I need to get the data
> from the .MDB files into the PostgreSQL.
>   There are several databases.

> What is the most pain-free way one can accomplish this?  I
> think the Access database files are an
> older format, because when I go to open them with MS Access
> 2000, it states that the files need to
> be converted to the new format (which I obviously did not do
> - didn't want to break the app).

> I assume there are two distinct functions that need to be done:
>    1. Create the databases, with the appropriate tables and columns
>    2. Move the data within the database.

> I did do some searching on the site, but most of the stuff
> didn't quite "click".  Any advice given
> would be greatly appreciated.  Thanks!

> Bob

> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

---------------------------(end of broadcast)---------------------------

 
 
 

Converting from .MDB Access to PostgreSQL

Post by Bob Puf » Fri, 12 Jul 2002 10:19:27


Thanks for everyone's comments.  I tried using Access to export to the odbc database, but no go.  I then tried PgAdmin, which has sucessfully converted a few databases, but
I am still stuck on some others.

The ones that won't convert have tables with entries of type "OLE Object", of which I don't see a corresponding field type in Postgres.  How do I get past this?

Bob

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

 
 
 

Converting from .MDB Access to PostgreSQL

Post by Bob Puf » Sat, 13 Jul 2002 03:43:39


HI Bob (& all),

I do not know how large the data could be.  It could be a picture, it could be anything... I would say it should be under 250k long.  What type should I use for this?  Is
there not a generic "byte" type variable that can handle say a meg of undefined data?

Bob

> Bob,

>         What data is being stored in these fields?  You might be able to get
> away with using a BLOB field, but you'll need to know what is being stored
> and how it's being used to make sure you'll get the same functionality out
> of storing it this way.

> Rob

> > -----Original Message-----

> > Sent: Wednesday, July 10, 2002 6:37 PM

> > Subject: Re: [GENERAL] Converting from .MDB Access to PostgreSQL

> > Thanks for everyone's comments.  I tried using Access to
> > export to the odbc database, but no go.  I then tried
> > PgAdmin, which has sucessfully converted a few databases, but
> > I am still stuck on some others.

> > The ones that won't convert have tables with entries of type
> > "OLE Object", of which I don't see a corresponding field type
> > in Postgres.  How do I get past this?

> > Bob

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate

message can get through to the mailing list cleanly