Rick Rudolph
asked on
MS Access TransferDatabase with ODBC - Receive Run-time error '2507'
I am trying to create a local version of an ODBC Table using VBA. I am able to use the External Database "Wizard" to connect to the table, either import or link. When I manually link to the table the DSN string looks like this:
Pub_Inv-image (DSN=Jenark_ODBC)
This is the VBA code I am using to create a local version of the table:
Public Function ResetLocalTables()
Dim sTblNmin As String
Dim sTblNmOut As String
Dim sTypExprt As String
Dim sCnxnStr As String, vStTime As Variant
Dim DestDatabase As String
sTblNmin = "Pub_inv-image"
sTblNmOut = "PUB_Local_inv-image"
sTypExprt = "ODBC Database"
sCnxnStr = "ODBC;DSN=ZZZZZZ_ODBC;UID= ZZZCon;PWD =Letmein"
DestDatabase = "S:\POimage\APImage.accdb"
DoCmd.TransferDatabase acImport, sTypeExprt, sCnxnStr, acTable, sTblNmin, sTblNmOut
End Function
When I run this code, I receive a Run-Time error '2507': The type isn't an installed database type or doesn't support the operation you chose
Any suggestions on how to debug this or correct what I have would be appreciated.
Pub_Inv-image (DSN=Jenark_ODBC)
This is the VBA code I am using to create a local version of the table:
Public Function ResetLocalTables()
Dim sTblNmin As String
Dim sTblNmOut As String
Dim sTypExprt As String
Dim sCnxnStr As String, vStTime As Variant
Dim DestDatabase As String
sTblNmin = "Pub_inv-image"
sTblNmOut = "PUB_Local_inv-image"
sTypExprt = "ODBC Database"
sCnxnStr = "ODBC;DSN=ZZZZZZ_ODBC;UID=
DestDatabase = "S:\POimage\APImage.accdb"
DoCmd.TransferDatabase acImport, sTypeExprt, sCnxnStr, acTable, sTblNmin, sTblNmOut
End Function
When I run this code, I receive a Run-Time error '2507': The type isn't an installed database type or doesn't support the operation you chose
Any suggestions on how to debug this or correct what I have would be appreciated.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.