Moving A Table from MS Access to PostgreSQL
The Microsoft Access database file (.mdb) that is located in the project folder is for testing purposes only. It can only hold not more than 2GB of data. In order to migrate the data stored in a MS Access to PostgreSQL, follow these steps below.
- First, open the Microsoft Access database file (.mdb). I will use datamap.mdb for this example.
- Next, right-click on the datamap table in the left plane. Select Export and select ODBC Database.
- An Export popup will appear with the selected table name.
- Make sure that the table name is correct and click OK.
- At the Select Data Source popup, click on Machine Data Source tab.
- We will be adding a new machine data source by clicking New.
- A Create New Data Source popup will appear.
- Select System Data Source and click Next.
- Next, select PostgreSQL Unicode and click Next.
- Click Finish.
- Next, we will configure the PostgreSQL driver in the PostgreSQL Unicode ODBC (psqlODBC) Setup window.
- Fill in the details with the correct info and give the data source a name.
- Next, click on Test. If the information filled is correct, you will get a message saying “Connection successful”.
- Click OK and click Save.
- Now you will see the newly created data source in the popup.
- Select it and click OK.
- Finally, an Export – ODBC Database popup will appear. Click Close.
Open PosgreSQL and check the database that was used in the steps above. You will find the datamap table from Microsoft Access has been exported to PostgreSQL.
If this doesn’t work or if you have any questions, please don’t hesitate to contact us at [email protected].