Access VBA: Export Access tables using ODBC

If you want to export Access tables using ODBC/DSN connections, use the following code. This procedure uses the File DSN and ODBC connection to export Access tables using DAO object TableDef.

Sub ExportTbls()

Dim sTblNm As String
Dim sTypExprt As String
Dim sCnxnStr As String, vStTime As Variant
Dim db As Database, tbldef As DAO.TableDef

On Error GoTo ExportTbls_Error

sTypExprt = “ODBC Database” ‘Export Type
sCnxnStr = “ODBC;DSN=DSNName;UID=userID;PWD=password” ‘Create the connection string

vStTime = Timer
Application.Echo False, “Visual Basic code is executing.”

Set db = CurrentDb()

‘need a reference to Microsoft DAO 3.x library
For Each tbldef In db.TableDefs
‘Don’t export System and temporary tables
If Left(tbldef.Name, 4) “MSys” And Left(tbldef.Name, 4) “~TMP” Then
‘Debug.Print tbldef.Name
sTblNm = tbldef.Name
DoCmd.TransferDatabase acExport, sTypExprt, sCnxnStr, acTable, sTblNm, sTblNm
End If
Next tbldef

MsgBox “Done! Time taken=” & Timer – vStTime

On Error GoTo 0
SmoothExit_ExportTbls:
Set db = Nothing
Application.Echo True
Exit Sub

ExportTbls_Error:
MsgBox “Error ” & Err.Number & ” (” & Err.Description & “) in procedure ExportTblsODST”
Resume SmoothExit_ExportTbls
End Sub

About the Author

The author of Tableau Data Visualization Cookbook and an award winning keynote speaker, Ashutosh R. Nandeshwar is one of the few analytics professionals in the higher education industry who has developed analytical solutions for all stages of the student life cycle (from recruitment to giving). He enjoys speaking about the power of data, as well as ranting about data professionals who chase after “interesting” things. He earned his PhD/MS from West Virginia University and his BEng from Nagpur University, all in industrial engineering. Currently, he is leading the data science, reporting, and prospect development efforts at the University of Southern California.

  • n8_Mills says:

    http://nandeshwar.info/wp-comments-post.php

    Thanks, this saved me a pantload of time. However, the copy/paste wasn’t all that friendly, I had to do a find/replace on all the quotes and commas since they aren’t the same as the VBA IDE. Also, “If Left(tbldef.Name, 4) “MSys” And Left(tbldef.Name, 4) “~TMP” Then” is missing the “not equals” signs, it should be:

    If Left(tbldef.Name, 4) “MSys” And Left(tbldef.Name, 4) “~TMP” Then

    Hopefully you can repost with these changes so more noobish peeps can find it useful.

    Best,

    n8

  • n8_Mills says:

    Interestingly, even my posts change the quotes and “equals” / “not equals” signs. Something about this web site is very VBA un-friendly.

  • a7n9 says:

    Sorry, n8, I did not update the code after I changed the encoding of the VBA code. I will edit those soon. Thanks for the heads up.

  • Stickman says:

    Some things this technique won’t do:

    1. Copy primary key definitions – target tables will have no primary keys.

    2. It the table has data, you therefore get a popup on each table asking the user to define primary keys for the table.

    3. Indexes of any kind – not transfered.

    4. nullable/not nullable property. All fields will be nullable.

    5. Many other field properties.

    6. Table relationships. There will be none.

    SSMA is a better option, but also doesn’t do a few of these things.

  • >