databasedev.co.uk - database solutions and downloads for microsoft access

Exporting a table from Microsoft Access to EnterpriseDB

Introduction

Postgres is the well known and most used Open Source OLTP database available today which is claimed to be as good as or even better than MySQL. EnterpriseDB is Postgres packaged differently to provide enterprise capabilities to Postgres users. EnterpriseDB is available for Windows platform as well. It has most of the necessary features of an enterprise class such as advanced development, monitoring, migration and administrative tools with a stable environment. In order to follow the article the author recommends downloading and using this software as detailed in his earlier article.

These days no software has monopoly and businesses use a software mix that makes business sense to them. Because of this businesses sometimes need to migrate, only a part or all of their data to another kind of software. There is therefore a need for moving data or migration of data. EnterpriseDB also provides a migration tool as described in yet another article. However it does not support Microsoft Access out-of-the box. A method was suggested in this article using the SQL Server Integration Services. However there were a few problems. In this article the built-in method of exporting tables in Microsoft Access is explored to take a table in Microsoft over to Postgres that is more successful than the previous one and which is a whole lot simpler. This article steps you through the process with a number of screen shots to guide you along the way.

It is assumed that you have downloaded the EnterpriseDB as outlined in the article. EnterpriseDB has come up with an updated version recently and it should work the same way. After that, follow the steps in this article to create a new database in Postgres. A MoveTable database was created in Postgres Studio for this article as shown. Presently it has no user tables.

Now you are ready to follow the steps. There are three major steps to follow:

1. Create an ODBC DSN for connecting to the database to which the table will be transferred
2. Exporting the table
3. Verifying the export

Creating the ODBC DSN

Click Start | Control Panel | Administrative Tools | Data Sources (ODBC). This opens the ODBC Data Source Administrator window as shown. The default tab is User DSN.

Click the Add... button to open the Create New Data Source window as shown.

Scroll down and choose EnterpriseDB 8.3 as the driver and click Finish. The EnterpriseDB ODBC Driver window shows up. You must provide a name for the DSN. Herein it is FromAccess. You will have to have your User Name/Password information to fill in the appropriate text boxes. The database is the name of the database you created earlier. Herein it is MoveTable. Accept other defaults.

Click and test the connection using the Test button. You should get a response as shown.

Click OK on the Connection Test window.

In the Options field click the Datasource button to open the Page1 of the Advanced Options as shown.

Click on Page2 and a window pops-up as shown.

Remove the check mark for the 'Server side prepare' check box. We shall see how this will influence the export later. Click Apply and then click OK.

You have created a User DSN 'FromAccess' as shown in the next figure.

Exporting the table

Open the Northwind.mdb (or any other mdb file) file as shown in the next figure.

Highlight the Customers table (or the table of your choice) and make a right click to bring up a drop-down menu as shown.

Click on Export… to open the 'Export Table 'Customers' To...’ window. Click on the Save as type drop-down to reveal the various file types that you can export to, as shown.

Click the item ODBC Databases () in the drop-down list. This opens the Export window as shown. Click OK.

This opens the Select Data Source window. Click on the Machine Data Source tab to show the various available DSN's.

Choose FromAccess and click OK.

The program returns you to MS Access's Northwind database without giving you an indication about the success/failure of the export. If there is an error, it however gives an error message.

Verifying and accessing the exported table

Go back to the Postgres Studio and refresh the databases node. Expand the databases node. Expand the MoveTable node. Expand the Schemas down to the tables as shown. You will see that the Customers table has been exported as shown.

Right click Customers and choose View Data | View Top 100 Rows as shown in the next figure.

You will see the following displayed which confirms that your table was successfully exported. The data types in Postgres and MS Access are different and you should study the differences.

In Page2 of the EnterpriseDB ODBC configuration we took the check mark off the Server side prepare check box. What if we had left it in place? Well, you can go and test it; this is what you will see.

The export would have failed. With this checked, perhaps the program tries to prepare a statement to be executed on the server that fails.

Summary

The article described exporting a table from MS Access 2003 to EnterpriseDB. In the process the reader also had an opportunity to experience a new database product. The ODBC configuration must be correct for the export to succeed; the default configuration will lead to an export error due to a failed ODBC call.