troubleshooting Question

MS Access TransferDatabase with ODBC - Receive Run-time error '2507'

Avatar of Rick Rudolph
Rick RudolphFlag for United States of America asked on
DatabasesMicrosoft AccessVBA
1 Comment1 Solution284 ViewsLast Modified:
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
Join our community to see this answer!
Unlock 1 Answer and 1 Comment.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 1 Comment.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros