Microsoft Access to PostgreSQL Conversion

From PostgreSQL wiki
Jump to navigationJump to search

by Jon Hutchings 20th July 2001

Introduction

This page is intended as a quick user guide to converting an MS Access database for use with PostgreSQL. It is hoped that the reader has some experience of both Access and PostgreSQL, and this document is not intended to be a guide to the usage of those systems.

Stage 1 - on your Workstation running MS Access

The first stage in the conversion process is to download the PostgreSQL upsizing tool. This can be obtained from the authors website. I strongly recommend reading the authors documentation and also note the limitations listed below of this tool.

This tool takes the form of an MS Access 97 database.

Once opened, the main screen (frmmain) has a number of options relating to the SQL conversion.

Select the database that you want to convert., analyse it using the Analyse function and amend any parameters you need to change.

It is important to fill out the SQL database name field with the name you wish to give your final postgres database otherwise you will have more work to do later.

Check the Export data option to move the data across - amend the file locations in the Miscellaneous options section if necessary.

Use Create SQL to make the SQL files and then Export SQL. This will place all the files needed in stage 2 in the directory specified on the Miscellaneous tab of the amend Parameters screen within pgupt.

FTP these files to a suitable empty directory on your postgres server.

Stage 2 on your Linux Server running PostgreSQL

Log into your server and change to the directory where you have uploaded the files

If you did not give the database a name earlier then the following must be performed before proceeding

  • edit the loadd.sh file (for example joe loadd.sh will open the file in the joe editor).
  • Change all references to "test" in the file to the name which you want to give your database.
  • HINT - Don't include non alphanumeric charaters in your database name (i.e. don't use -_ '?/!"\| etc.)
  • Save and close the file.
  • Open the create_schema.sql file and examine the table structure. Ensure that the fieldnames and data types are correct.

Issue the following command to make the loadd.sh executable 

chmod u+x loadd.sh

Execute the the loadd.sh script using the following command at your shell prompt. 

./loadd.sh 2> output1

This will execute the script and put the errors and output in a file called output1. If everything is working correctly the screen should display a CREATE for each table in the database, if you only see two create statements and you have three tables , then one of the tables has not been converted. In this case examine the log file (output1 in this case), for details. A common cause for this is the naming of tables or fields using SQL reserved words. For a list of these see the Postgresql documentation for your version of postgres.

WARNING: Once you have succesfully converted your database and it is in use you should NOT rerun this script as it will reinitialize your database back to the state in which it was when you converted it. I suggest that when your database is working you remove the execute permissions on the loadd.sh script to prevent accidential execution.

Stage 3 - Testing your database at the server console.

Once you are happy that the conversion has worked, you can test your database by connecting to it with the Postgresql monitor in the normal way. For example, if you called your database "customerdata" you would use 

psql customerdata

at a shell prompt on your linux postgres server to connect (you will probably need other command line options for usernames and passwords etc.). Once connected you should use the \dt command to display a list of tables and compare it with the source database. If these look correct, try some simple queries such as 

select * FROM <tablename> ;

This will display (at some speed !) all the records, but more usefully at the end you will get a record count for that table which you can compare to ensure all the data has been converted correctly.

Stage 4 - ODBC Configuration

If you wish to continue to use Microsoft Access97 to interact with the converted database you will need to set up a suitable ODBC driver and datasource on the client PC(s).

The first stage is to download the PostgreSQL ODBC driver. This can be obtained via the PostgreSQL ODBC website.

Once installed, use the ODBC control panel on your Windows 9x/NT 4.0/Windows 2000 PC and add a new User DSN or user data source. Select the PostgreSQL driver and fill in the details with the server name, port, database name, username and password, for your postgresql server.

Stage 5 - MS Access Configuration.

To create an Microsoft Access based front end to the newly created PostgreSQL database, do the following:

Make a copy of your original Access database.

Open it in Microsoft Access.

Select the Tables tab.

Select New.

Select Link Table.

In the Files of Type list select ODBC Databases.

In the data sources dialogue find the data source you created earlier (on NT 4.0 this will typically be on the Machine Data Sources tab).

Select the tables which you wish to link.

Once linked you are free to remove the old local tables and compact the database.

NOTE - If any field names have changed during conversion (usually due to the use of non-alphabetic characters in the field and table names) then your queries, forms and reports etc. will need updating to reflect this.

Limitations

Firstly, I have only tested this script with Postgres 6.5.3 and Access 97, other combinations may work but are untested by me.

Secondly, ther are some limitations to this tool, which I have discovered, these are

  1. The tool expects at least one relationship, so simple single table database conversions will fail.
  2. The tool assumes you will be using the standard postgesql server port.
  3. Using this tool it is possible to generate trigger and function names longer than 32 characters which can cause postgresql some problems

Extension Links

  • ESF Database Migration Toolkit Migrate data from MS Access to PostgreSQL in wizards.
  • Full Convert Database conversion and synchronization between Microsoft Access, dBase, FoxPro, Microsoft Excel, Firebird, Interbase, MySQL, Oracle, Paradox, Microsoft SQL Server, PostgreSQL, SQL Server, SQL Server Azure, SQL Server Compact(SQLCE), SQLite, Delimited text files (CSV), XML and many more via ODBC.
  • DBConvert/DBSync for Access and PostgreSQL Migrate/ Sync your data between MS Access and PostgreSQL. Translate your Access queries to PostgreSQL views and vice versa automatically.
Retrieved from "https://wiki.postgresql.org/index.php?title=Microsoft_Access_to_PostgreSQL_Conversion&oldid=39313"