Thread: Re: [GENERAL] Import table from MS Access?
We've had good luck with something we found at: http://www.sevainc.com/ David Boerwinkle -----Original Message----- From: Mike Mascari <mascarm@mascari.com> To: Chris Carbaugh <cjdesigns@sprintmail.com> Cc: pgsql-general@postgreSQL.org <pgsql-general@postgreSQL.org> Date: Sunday, December 26, 1999 6:08 PM Subject: Re: [GENERAL] Import table from MS Access? >Chris Carbaugh wrote: > >> What is the best way to import a table from Microsoft Access 2000? >> >> I was able to export to a text file from access, but this was only the >> data. Can I export/import the table definition as well? >> >> I have been using pgaccess to administer my DB. It seems I can't tell >> it to import a comma delimited file? Is there any way around this? >> >> Any help is greatly appreciated. >> >> Chris >> > >One way is to use the PostgreSQL ODBC driver from Insight (search >yahoo.com for: postgres Insight ODBC), and use the File->Export function >in Access to export the tables to PostgreSQL. There are a few problems >with this method, though, if I recall correctly: > >1. Table and field names will be case-sensitive, so if you have a table >in Access called Employees with a field HireDate, then in PostgreSQL, >you must refer to this as "Employees"."HireDate", not employees.hiredate, >although you could programmatically rename the tables by performing an >update on pg_class and pg_attribute. > >2. Column constraints are not exported. If I recall (its been some time), >column constraints are not exported from Access when the tables are >created. And, unfortunately, there's no easy way to add them in >PostgreSQL using an ALTER TABLE statement. > >Nevertheless, it might be easier to perform the export in Access using >ODBC, pg_dump the database to a text file, perform whatever cleanup is >necessary, and then reimport. > >Also, I rember that there's a PostgreSQL upsizing tool somewhere that >does all this stuff for you. But for the life of me I can't remember >where... > >Hope that helps, > >Mike Mascari > > > >************ >
I have some troubles to access PostgreSQL tables linked to M$-Access2000. Does anyone have any ideas, how to do that? Thanks. davidb@vectormath.com wrote: > We've had good luck with something we found at: > > http://www.sevainc.com/ > > David Boerwinkle > > -----Original Message----- > From: Mike Mascari <mascarm@mascari.com> > To: Chris Carbaugh <cjdesigns@sprintmail.com> > Cc: pgsql-general@postgreSQL.org <pgsql-general@postgreSQL.org> > Date: Sunday, December 26, 1999 6:08 PM > Subject: Re: [GENERAL] Import table from MS Access? > > >Chris Carbaugh wrote: > > > >> What is the best way to import a table from Microsoft Access 2000? > >> > >> I was able to export to a text file from access, but this was only the > >> data. Can I export/import the table definition as well? > >> > >> I have been using pgaccess to administer my DB. It seems I can't tell > >> it to import a comma delimited file? Is there any way around this? > >> > >> Any help is greatly appreciated. > >> > >> Chris > >> > > > >One way is to use the PostgreSQL ODBC driver from Insight (search > >yahoo.com for: postgres Insight ODBC), and use the File->Export function > >in Access to export the tables to PostgreSQL. There are a few problems > >with this method, though, if I recall correctly: > > > >1. Table and field names will be case-sensitive, so if you have a table > >in Access called Employees with a field HireDate, then in PostgreSQL, > >you must refer to this as "Employees"."HireDate", not employees.hiredate, > >although you could programmatically rename the tables by performing an > >update on pg_class and pg_attribute. > > > >2. Column constraints are not exported. If I recall (its been some time), > >column constraints are not exported from Access when the tables are > >created. And, unfortunately, there's no easy way to add them in > >PostgreSQL using an ALTER TABLE statement. > > > >Nevertheless, it might be easier to perform the export in Access using > >ODBC, pg_dump the database to a text file, perform whatever cleanup is > >necessary, and then reimport. > > > >Also, I rember that there's a PostgreSQL upsizing tool somewhere that > >does all this stuff for you. But for the life of me I can't remember > >where... > > > >Hope that helps, > > > >Mike Mascari > > > > > > > >************ > > > > ************
Download and install the latest Microsoft Data Access Componants from Microsoft. That mostly fixed me when I was having problems. But, I should also add that when reformated my entire hard drive and reinstalled Windows, Office 2000, and MDAC, things got a lot better. I hate to think what that means. David Barnes -----Original Message----- From: owner-pgsql-general@postgreSQL.org [mailto:owner-pgsql-general@postgreSQL.org]On Behalf Of Jose Soares Sent: Wednesday, January 05, 2000 11:45 AM To: davidb@vectormath.com Cc: Mike Mascari; Chris Carbaugh; pgsql-general@postgreSQL.org Subject: Re: [GENERAL] Import table from MS Access? I have some troubles to access PostgreSQL tables linked to M$-Access2000. Does anyone have any ideas, how to do that? Thanks. davidb@vectormath.com wrote: > We've had good luck with something we found at: > > http://www.sevainc.com/ > > David Boerwinkle > > -----Original Message----- > From: Mike Mascari <mascarm@mascari.com> > To: Chris Carbaugh <cjdesigns@sprintmail.com> > Cc: pgsql-general@postgreSQL.org <pgsql-general@postgreSQL.org> > Date: Sunday, December 26, 1999 6:08 PM > Subject: Re: [GENERAL] Import table from MS Access? > > >Chris Carbaugh wrote: > > > >> What is the best way to import a table from Microsoft Access 2000? > >> > >> I was able to export to a text file from access, but this was only the > >> data. Can I export/import the table definition as well? > >> > >> I have been using pgaccess to administer my DB. It seems I can't tell > >> it to import a comma delimited file? Is there any way around this? > >> > >> Any help is greatly appreciated. > >> > >> Chris > >> > > > >One way is to use the PostgreSQL ODBC driver from Insight (search > >yahoo.com for: postgres Insight ODBC), and use the File->Export function > >in Access to export the tables to PostgreSQL. There are a few problems > >with this method, though, if I recall correctly: > > > >1. Table and field names will be case-sensitive, so if you have a table > >in Access called Employees with a field HireDate, then in PostgreSQL, > >you must refer to this as "Employees"."HireDate", not employees.hiredate, > >although you could programmatically rename the tables by performing an > >update on pg_class and pg_attribute. > > > >2. Column constraints are not exported. If I recall (its been some time), > >column constraints are not exported from Access when the tables are > >created. And, unfortunately, there's no easy way to add them in > >PostgreSQL using an ALTER TABLE statement. > > > >Nevertheless, it might be easier to perform the export in Access using > >ODBC, pg_dump the database to a text file, perform whatever cleanup is > >necessary, and then reimport. > > > >Also, I rember that there's a PostgreSQL upsizing tool somewhere that > >does all this stuff for you. But for the life of me I can't remember > >where... > > > >Hope that helps, > > > >Mike Mascari > > > > > > > >************ > > > > ************ ************