Access VBA: Export Access tables using ODBC

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