MS Access to PostgreSQL Tutorial

MS Access to PostgreSQL converter provides two options to convert Microsoft Access database into PostgreSQL data source:

• Migrate to PostgreSQL server directly - use this approach to migrate data from the source database to PostgreSQL server directly. To use this option you need direct access to PostgreSQL server and necessary privileges to create a database on the target server.
• Export into script file - use this approach to get more control over the process. Following this way, MS Access to PostgreSQL converter exports the contents of the source database into a local script file (.sql) instead of moving it to PostgreSQL server directly. The resulting file contains SQL statements to create all tables and to fill them with the data. See How to import PostgreSQL script file article for related information.

You can choose one of these approaches on the first page of MS Access to PostgreSQL wizard using the corresponding radio buttons.

This wizard screen collects information to connect to PostgreSQL server. MS Access to PostgreSQL converter is able to connect to both local and remote PostgreSQL servers.

By default the program uses local connection mode. It is indicated by radio button "Local" selected. To remote connection select "Remote" radio button, specify the host (network name or IP address) and the port. The port value is necessary only if it differs from the default PostgreSQL port number 5432. Otherwise you may leave this field as is.

Enter user name and password or leave these fields blank for anonymous connection.

By default PostgreSQL server connects to database with name equal to the username specified. If there is no such database or PostgreSQL server does not allows access to it, custom database may be specifies on this wizard page.

On this wizard screen you should enter source and destination database names. If you specify the name of existing PostgreSQL database it will be overwritten.

If the source MS Access database is password protected you should select "Use database password" checkbox and enter the password in the field below.

Enter logfile name in the appropriate field or leave it empty to disable logging.

On this wizard screen you can customize the conversion process using the following settings:

• Convert boolean value as - use this setting to customize how Microsoft Access booleans will be converted. It can be either ANSI standard (true=1,false=0) or Microsoft standard (true=-1,false=0)
• Convert table definitions only - select this checkbox to convert only table definitions of FoxPro/DBF source. All the data will not be migrated.
• Convert views - select this checkbox to convert views
• Skip converting indexes - select this checkbox if you don't want to convert indexes

Also, you can specify PostgreSQL schema according to one of the following options:

• Use existing: specify existing schema to import all tables there.
• Create new: create new schema to import all tables there.

This wizard screen allows to manage MS Access tables to convert. In the left part of the window there is "Available tables" list box. It contains all tables of MS Access database except selected ones. On the right there is "Selected tables" list box.

To add new MS Access table for converting into PostgreSQL format select it in "Available tables" list box and click "Add" button (or double-click the highlighted item). Use "Add all" button to select all MS Access tables for conversion.

To remove an item from "Selected tables" list select it and click "Remove" button. Use "Remove all" button to remove all tables from "Selected tables" list box.