We help IT Professionals succeed at work.
Get Started

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

283 Views
Last Modified: 2017-04-13
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.
Comment
Watch Question
President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017
Commented:
This problem has been solved!
Unlock 1 Answer and 1 Comment.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE