Written by 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.
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
Related posts:
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
Interestingly, even my posts change the quotes and “equals” / “not equals” signs. Something about this web site is very VBA un-friendly.
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.