Convert Access to PostgreSQL?

Question

A client has outgrown their Access database, and now I need to convert it to PostgreSQL. I don't have a lot of Access experience, so at first blush it seems like the best solution is to just export the data using text files or something.

Does anyone have any good suggestions for an easy way to get the DDL and data out of Access and into something real?

Answer 1

Based on http://www.postgresonline.com/journal/archives/24-Using-MS-Access-with-PostgreSQL.html but updated for Access 2019 with more details:

1. Install the PostgreSQL ODBC drivers.

   1. You may have this already installed from installing PostgreSQL. If not, you can install them from https://www.postgresql.org/ftp/odbc/versions/msi/.
   2. You should install the 32-bit driver if your Access is 32-bit and the 64-bit driver if Access is 64-bit. You can see if Access is 32-bit or 64-bit through File > Account > About Access.
   3. Uninstall old versions of the PostgreSQL driver before installing a new one.
   4. See Setting up PostgreSQL ODBC on Windows for more tips.

2. External Data > New Data Source > From Other Sources > ODBC Database. The keyboard shortcut for this is Alt > X > N1 > T > C.

3. Choose Link to the data source by creating a linked table, and press OK.

4. Next to DSN Name, click New...

5. Choose PostgreSQL Unicode if your databases is UTF-8 or a non Latin Encoding. Choose PostgreSQL ANSI if your database encoding is SQL_ASCII, EUC_JP, BIG5, Shift-JIS, or a LATIN character set. Databases made from Access 2000 or later are in UTF-8. Choose the 64-bit version if Access is 64-bit. Click Next >.

7. As the dialog says, "Type in the name of the file data source you want to save the connection to. Or, find the location to save to by clicking Browse." Click Next >. Click Finish.

8. Fill in the fields Database, Server, Port, User Name, and Password.

9. Click Connection and uncheck Bools as char.

9. Click Page 2 and check True is -1, and uncheck Updatable Cursors. Click OK. Click OK.

10. If you get an error that says "A connection could not be made using the file data source parameters entered", open ODBC Data Sources (64-bit) from the Start menu (or ODBC Administrator from Control Panel for Windows 7 or earlier), click Add..., choose PostgreSQL Unicode(x64), click Finish, enter the details for your database, and click Test.

11. Select the newly created .dsn file and click OK.

12. Now select the tables you want and click Save Password. If you are missing primary keys on tables, Access will prompt you for what fields or set of fields you would like to use as the primary key. This doesn't make any structural changes to the actual table, but in the linked structure, Access will pretend this is the primary key and use that accordingly for table updates and such. This is particularly useful for views where the concept of primary keys does not exist and you want your updateable views to be updateable from Access. If you click OK or Cancel to the question without picking a set of fields, that table will be marked as readonly, which is the desired behavior for a lot of reporting views.

To export data from an Access table to PostgreSQL:

1. Select the table.

2. Rename the table to what you want it to be named.

3. Make sure the default schema of the user you are using in Postgres, is the schema you want to export the data to.

4. External Data > More (in the Export section) > ODBC Database and select the DSN you created.

   1. One gotcha here is that PostgreSQL will maintain the casing of the fields in the table and the table name, so it's best to rename all your fields to lowercase first so you don't have to be quoting them every time you use them.