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
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
Show quoted text
-----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 PostgreSQLHello,
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
Import Notes
Resolved by subject fallback
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
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
Show quoted text
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 PostgreSQLThanks 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
Import Notes
Reference msg id not found: 419D2EB7B461D411A53B00508B69181D0623264C@sdex02.sd.intuit.com | Resolved by subject fallback
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:
Show quoted text
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?
Import Notes
Resolved by subject fallback