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.
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 :
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
This is only a preview. Your comment has not yet been posted.
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.
Posted by: |