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
