Before we start – there are plenty of different tools available, including developing a DIY approach . This is one tool – focused on tablesdata

1) Download and install the Access to PostgreSQL tool from bullzip.com – Access to PostgreSQL tool  (A2P)  – > Don’t forget to donate some money to them

2) To transfer the tables & data  directly to Postgresql you will also need the postgresql odbc drivers , which the A2P tool uses to communicate with PostgreSQL and transfer the objects. Link to the Postgresql odbc , download and install the drivers.  After you’ve successfully installed the drivers , check the odbc interface and you should see a reference to the PostgreSQL drivers.

3) Before you start – ensure you have an accessible path to an MS Access database and a Postgresql database with a login with relevant privileges.

4)Start the  A2P program – nice & simple

5)Reference   the  MSAccess    database. Ensure  the db  is   on  a path   with  privileges  , otherwise  a “cannot  lock  database”   message  will  appear

6)    Destination PostgreSQL Database

6) SELECT  MS Access tables – The next screen will take you to the SELECT tables .

Let’s say you have an Access table called – “WeeklySummary”   – the list will display it with the  Upper Case Lower Case breakdown .  Meaning – that is the way the table will be named in the target PostgreSQL database.

As we know postgresql     will not  allow : select * from public.WeeklySummary ,    whereas  select * from public.”WeeklySummary”  works. I’ve made a note to investigate whether it’s possible

There is a good feature – where you can doubleclick the tablename to specify which fields

Notes :

1) The steps above have not considered other application level  coding.

2) There are other methods – for example if you don’t have many tables – try :

• Install Postgres ODBC driver on Windows computer.
• Create a data source with “PostgreSQL Unicode” driver to your new database
• For every table:
  • Use “File -> Export” choose type “ODBC Databases”
  • Confirm table name
  • Choose “Computer data source”
  • Select your data source

3) PostgreSQL is case-sensitive – A big change for developers from a Windows environment  is that PostgreSQL is case-sensitive whereas MS Access in-general isn’t

Author: Jack Vamvas