Re: [GENERAL] PostgreSQL accessing a M$ Access DB?

Stan Jacobs wrote:
>
> Hmm, will the Insight ODBC driver let you copy an Access DB directly into
> a Postgres db? Mike, it sounds like this is what you're explaining below,
> but I'm relatively new to this and feel like I'm missing something. :-)
> Shouldn't there be a client app using the odbc driver to do the copying,
> etc? Or does Insight provide that app as well?
>
> Or perhaps I should just rephrase the real question: Given an Access .mdb,
> what's the easiest and/or best way to import that data into a Postgres db?
>
> -stan
>

The way I've moved data from Access 97 to PostgreSQL is to use
the File->Export option in Access. When the File dialog appears,
the last choice in the file type drop-list (this is from memory)
is ODBC. After choosing ODBC, you will be prompted for which data
source to use. You can then export the schema as well as the data
to PostgreSQL from Access 97. One side effect of this is that the
table and column names will be case-sensitive in PostgreSQL. So,
I always make sure that the table and field names in Access are
all in lower-case before exporting. Once exported, you can
continue to use Access 97 as a front-end to PostgreSQL by using
"linked tables" to PostgreSQL.

Also, there is the following link to an upsizing tool, although I
have never used it:

http://dialspace.dial.pipex.com/town/lane/yd80/pgupt/pgupt.shtml

Still, I think Frank wants to be able to write Linux-side code to
pull data out of an .mdb file using the iODBC interface, which I
have not used. Last time I checked, (which was a LONG time ago)
iODBC only had a few drivers, such as Oracle, but maybe a
Linux-native iODBC .mdb driver exists by now.

Hope that helps,

Mike Mascari