nandeshwar.info

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

DelteTbls_Error:
    MsgBox “Error ” & Err.Number & ” (” & Err.Description & “) in procedure DelteTbls”
    Resume SmoothExit_DelteTbls
End Sub

sFileNm = Dir(sPath, vbNormal)
Do While sFileNm “”
If Right(sFileNm, 3) = “dbf” Then
sTblNm = Left(sFileNm, Len(sFileNm) – 4) ‘Extract the file name
‘Use the TransferDatabase option to link the tables from the specified directory
‘to your current Access DB
DoCmd.TransferDatabase acLink, “dBase III”, sPath, acTable, sTblNm, sTblNm
End If
sFileNm = Dir
Loop

Application.Echo True
End Sub