Avatar of Rick Rudolph
Rick Rudolph
Flag 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.
DatabasesMicrosoft AccessVBA

Avatar of undefined
Last Comment
Jim Dettman (EE MVE)

8/22/2022 - Mon