How to bulk export tables from MS Access

Despite all the nasty things people say about MS Access, it does have fairly database agnostic, easy to use import and export tools and a not too shabby query wizard and query tool. But of course, it's not a server side database so at a certain point won't scale for database storage. You can still use it as a front-end to a server-side database such as PostgreSQL or SQL Server. So once you outgrow it for data storage, you'll probably want a quick way to export your data out. Unfortunately, well at least in the 2003 version, while you can bulk link tables from an ODBC connection, you can only export one table at a time using the "select table" right-click export. In this article we'll show a quick and dirty export MS Access Visual basic sub routine we use to export all tables. This we've only tested on MS Access 2003, so if you are using higher, you might need to customize it a bit. This script should work fine for exporting to any database connection with modification of the connection string.

Figuring out the connection string to put in

To figure out what connection string to put in for any kind of database (not just PostgreSQL), follow these steps

Create a File DSN
Create a query in design view
Go to Query->SQL Specific->Pass-through:
Click properties and the ... and pick the DSN you created in 1.
Your connection string is what shows in the ODBC field.

VB Subroutine to export all your tables

Here is the script we use. You can replace the whole connection string parameter with what you got from SQL-Pass Thru, but this is what ours looks like using the free ODBC PostgreSQL driver you can download from http://www.postgresql.org/ftp/odbc/versions/msi/.

Sub modExportToPG()
    Dim rs As DAO.Recordset
 'We only want to export physical user defined visible and hidden tables
       Set rs = CurrentDb.OpenRecordset("SELECT Name " &  _
        " FROM MSysObjects " & _
        "   WHERE Type=1 AND Flags < 9 ORDER BY Name;")

    Do Until rs.EOF
        'export the tables but export as lower case table names
        DoCmd.TransferDatabase acExport, "ODBC Database" _
         , "ODBC;DRIVER={PostgreSQL Unicode};DATABASE=mydb;SERVER=myserver;PORT=5432;UID=myuser;PWD=mypwd" _ 
         , acTable, rs("Name"), LCase(rs("Name"))
        rs.MoveNext
    Loop
    rs.Close
End Sub

Caveats

There are a couple of things not ideal with MS Access export logic:

the casing of the fields when exported is preserved and this can be incredibly annoying since it requires quoting all the fields to use them in PostgreSQL. To fix this issue, we use the approach we described in Lowercasing table and column names
For really huge tables in the millions of records, we usually don't use this approach because MS Access tries to register a transaction and the back and forth is really slow, so for huge tables we export to some sort of delimeted format and then import with psql or PostgreSQL SQL COPY or FDW.
MS Access generally does a good job of mapping field types e.g Yes/No become boolean in PostgreSQL. However autonumber fields do not become serial keys automatically in PostgreSQL. We'll demonstrate how to create sequences and bind them (effectively making them serials) in PostgreSQL.
MS Access export doesn't recreate the primary keys, indexes, and foreign key constraints. We'll demonstrate how to generate an SQL script the recreation of the primary key and index keys from using a VB subroutine that scans the MS Access tables in the next article.