Follow dba-ninja.com

Subscribe to RSS feed  Follow @jackvamvas - Twitter

*Use the Comments section for questions

dba-ninja.com Links

Dba_db2_button

Sqlserver_dba_button

A simple guide to migrate MS Access to PostgreSQL

07 April,2022 by Rambler

There are a number of challenges to migrating MS Access tables to PostgreSQL.     Here is a simple step by step that I used to successfully migrate a series of tables and data to a PostgreSQL database. 

Before we start - there are plenty of different tools available, including developing a DIY approach . This is one tool - focused on tables\data

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. 

 

Postgresql_driver

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 

      A2P_1

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

 

6)    Destination PostgreSQL Database

A2P_3

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

A2P_4

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 . One of the post-migration tasks should include a review of the table & column names. If the table names are , for example "MyTable"  - then the application will need to need to use the format "select * from schema1."MyTable"  , same applies for the columns  - i.e if you had a column  "Col1" than you'll need to reference as select "Col1" from schema1."MyTable"

Use this link to generate code to assist in changing the table  and column names to lower case - MSSQL to PostgreSQL Migration - upper versus lower case

 

 


Author: Rambler (http://www.dba-ninja.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on A simple guide to migrate MS Access to PostgreSQL


dba-ninja.com