Link to home
Start Free TrialLog in
Avatar of Rick Rudolph
Rick RudolphFlag for United States of America

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.
ASKER CERTIFIED SOLUTION
Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial