Thread: PostgreSQL accessing a M$ Access DB?
Has anyone successfully accessed a M$ Access DB from PostgreSQL using ODBC or any other means? I have PostgreSQL running with postmaster on a Linux x86 box networked to a Windows95 x86 box that has both a Jet database (*.mdb that can be accessed using M$ACCESS version 2.0) and another Jet database (*.mdb that can be accessed using Access97). I am assuming that the former uses 16-bit access while the latter uses 32-bit access. Anyway, I am searching for a solution that can allow access to these *.mdb databases from the Linux box. I have a copy of the PostODBC from Insight on the Windows95 box and I am thinking of having iODBC on my Linux box to use ODBC across the network; however, I wanted to check with anyone, if there may be an easier way. Any suggestions? TIA, Frank -- --------------------- Frank Shim fshim@cawthra.com ---------------------
Francis K Shim wrote: > > Has anyone successfully accessed a M$ Access DB from PostgreSQL using ODBC or > any other means? I have PostgreSQL running with postmaster on a Linux x86 box > networked to a Windows95 x86 box that has both a Jet database (*.mdb that can be > accessed using M$ACCESS version 2.0) and another Jet database (*.mdb that can > be accessed using Access97). I am assuming that the former uses 16-bit access > while the latter uses 32-bit access. Anyway, I am searching for a solution > that can allow access to these *.mdb databases from the Linux box. > > I have a copy of the PostODBC from Insight on the Windows95 box and I am > thinking of having iODBC on my Linux box to use ODBC across the network; > however, I wanted to check with anyone, if there may be an easier way. > > Any suggestions? > > TIA, > Frank The PostgreSQL ODBC driver from Insight will allow direct access to PostgreSQL from the client machine (Win95), assuming your starting the postmaster with the -i option (for TCP/IP connectivity) on port 5432. You can import/export/link tables to/from Access 97 without the need for having iODBC on the Linux server. The PostgreSQL ODBC driver from Insight is a 32-bit driver and works nicely with Access 97. I am unaware of any 16-bit ODBC drivers for use with Access 2, though. For what its worth, Mike Mascari
Mike Mascari wrote: > > Francis K Shim wrote: > > > > Has anyone successfully accessed a M$ Access DB from PostgreSQL using ODBC or > > any other means? I have PostgreSQL running with postmaster on a Linux x86 box > > networked to a Windows95 x86 box that has both a Jet database (*.mdb that can be > > accessed using M$ACCESS version 2.0) and another Jet database (*.mdb that can > > be accessed using Access97). I am assuming that the former uses 16-bit access > > while the latter uses 32-bit access. Anyway, I am searching for a solution > > that can allow access to these *.mdb databases from the Linux box. > > > > I have a copy of the PostODBC from Insight on the Windows95 box and I am > > thinking of having iODBC on my Linux box to use ODBC across the network; > > however, I wanted to check with anyone, if there may be an easier way. > > > > Any suggestions? > > > > TIA, > > Frank > > The PostgreSQL ODBC driver from Insight will allow direct access > to PostgreSQL from the client machine (Win95), assuming your > starting the postmaster with the -i option (for TCP/IP > connectivity) on port 5432. You can import/export/link tables > to/from Access 97 without the need for having iODBC on the Linux > server. The PostgreSQL ODBC driver from Insight is a 32-bit > driver and works nicely with Access 97. I am unaware of any > 16-bit ODBC drivers for use with Access 2, though. > > For what its worth, > > Mike Mascari > Sorry. I see now you want to access the data the other way around - "pulling" from Access instead of "pushing" into PostgreSQL...Haven't tried it. Sorry, Mike Mascari
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
On Sun, 5 Mar 2000, Mike Mascari wrote:
> Mike Mascari wrote:
> >
> > Francis K Shim wrote:
> > >
> > > Has anyone successfully accessed a M$ Access DB from PostgreSQL using ODBC or
> > > any other means? I have PostgreSQL running with postmaster on a Linux x86 box
> > > networked to a Windows95 x86 box that has both a Jet database (*.mdb that can be
> > > accessed using M$ACCESS version 2.0) and another Jet database (*.mdb that can
> > > be accessed using Access97). I am assuming that the former uses 16-bit access
> > > while the latter uses 32-bit access. Anyway, I am searching for a solution
> > > that can allow access to these *.mdb databases from the Linux box.
> > >
> > > I have a copy of the PostODBC from Insight on the Windows95 box and I am
> > > thinking of having iODBC on my Linux box to use ODBC across the network;
> > > however, I wanted to check with anyone, if there may be an easier way.
> > >
> > > Any suggestions?
> > >
> > > TIA,
> > > Frank
> >
> > The PostgreSQL ODBC driver from Insight will allow direct access
> > to PostgreSQL from the client machine (Win95), assuming your
> > starting the postmaster with the -i option (for TCP/IP
> > connectivity) on port 5432. You can import/export/link tables
> > to/from Access 97 without the need for having iODBC on the Linux
> > server. The PostgreSQL ODBC driver from Insight is a 32-bit
> > driver and works nicely with Access 97. I am unaware of any
> > 16-bit ODBC drivers for use with Access 2, though.
> >
> > For what its worth,
> >
> > Mike Mascari
> >
>
> Sorry. I see now you want to access the data the other way around
> - "pulling" from Access instead of "pushing" into
> PostgreSQL...Haven't tried it.
>
> Sorry,
>
> Mike Mascari
>
> ************
>
>
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