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

  1. Create a File DSN
  2. Create a query in design view
  3. Go to Query->SQL Specific->Pass-through: ms access sql pass-thru
  4. Click properties and the ... and pick the DSN you created in 1. ms access properties
  5. Your connection string is what shows in the ODBC field. ms access sql pass-thru ODBC Connect Str

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

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"))
End Sub


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