ConvertToPostgreSQL

A tool to convert the tables of a MS Access 97 .mdb to PostgreSQL. This application was originally written by Michael Davis of Seva.inc. But it did not solve our problem - the conversion of an MS Access database with a lot of "deep" relations (tables with foreign keys relating to tables with foreign keys relating to other tables and so on). So I changed the way this tool works by arranging parts of the original code in a new way: First the code creates all tables in the postgres-database and copies the data to the tables. After that it alters the tables on the postgresql database by adding the foreign keys and indexes to the tables. Michael Davis allowed me to publish this changed Conversion Tool via the internet. Thank You Mr Davis!

Download ConvertToPostgreSQL2.zip

If you like the tool because it helps you to save a lot of work 
and you would like to donate something to me, here´s my wishlist at 
www.amazon.de.

What it does

• Process multiple Access97 databases at the same time.
• It creates your Access tables and indexes directly in PostgreSQL.
• It creates a sequence for every Auto Number field in Access and sets the sequence vales up correctly when copying the data.
• It creates foriegn key constraints in PostgreSQL and in the .sql create table statements (with the "ON DELETE CASCADE" clause) when table relationships have been defined in your Access database.
• It copies the data from Access97 to PostgreSQL.
• It creates the indexes after it imports the data.
• It creates users and permissions for all tables for all users.

What it does not

• It will not port Access queries to PostgreSQL views.
• It will not port any code to PostgreSQL.
• It will not port any Access97 forms to PostgreSQL or PgAccess.

• It does not create any files containing the schema of your database in sql. If you need this feature, look at the original at Seva.inc!

Problems

Naming

Due to the reduced length of names in PostgreSQL (to 32 chars), ConvertToPostgreSQL will run in failures, if the names of the tables in Access are too long. ConvertToPostgreSQL uses (and reduces) the original tablename to create names for tables, indexes (with ending "_i"), sequences (with endig "_s") and foreign keys. I myself encountered problems with several tables like:

"tbLieferantenBestellung_Preis"
"tbLieferantenBestellung_Preis_alt"

which caused ConvertToPostgreSQL to create identical names for indexes or sequences. Instead of recoding ConvertToPostgreSQL, I used a great tool (Find and Replace 8.0) from http://www.rickworld.com/ to rename the tables in Access like:

"tbLieBePr"
"tbLieBePrAlt"

(which is - by the way - in german language a much better name, because it contains "Liebe" in meaning of "love" :-) )

May another programmer solve this ;-)

Type Conversion

I for me decided to do some type conversion in another way as Michael Davis recommended. For "Currency" in Access I simply used "Float8" (and in forms, reports etc. for this explicit field the format "Currency"), for "Boolean" "int2". Look at the getTypeName(...) function to change this behavior.

Good Luck and happy programming!

ChrisTof