Thread: 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
On Tuesday 09 July 2002 07:49 pm, bob@nleaudio.com wrote: > 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
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. --- "Bob Puff@NLE" <bob@nleaudio.com> wrote: <SNIP> > > > 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 Home: 310-376-1029 chrisalbertson90278@yahoo.com Cell: 310-990-7550 Office: 310-336-5189 Christopher.J.Albertson@aero.org __________________________________________________ Do You Yahoo!? Sign up for SBC Yahoo! Dial - First Month Free http://sbc.yahoo.com
On Tue, Jul 09, 2002 at 05:54:08PM -0700, Chris Albertson wrote: > > 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
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-----
> From: Bob Puff@NLE [mailto:bob@nleaudio.com]
> Sent: Tuesday, July 09, 2002 4:50 PM
> To: pgsql-general@postgresql.org
> 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
>
Thanks for everyone's comments. I tried using Access to export to the odbc database, but no go. I then tried PgAdmin, whichhas 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 typein Postgres. How do I get past this? Bob
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 under250k 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----- > > From: Bob Puff@NLE [mailto:bob@nleaudio.com] > > Sent: Wednesday, July 10, 2002 6:37 PM > > To: pgsql-general@postgresql.org > > 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
Subject: Re: [GENERAL] Converting from .MDB Access to PostgreSQL From: Steven Citron-Pousty <sic4@pantheon.yale.edu> === Those are BLOB in postgres, but I am no expert in using or moving those. Bob Puff@NLE wrote: > 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 typein 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