0

In Access 2013 VBA I am trying to open the connection: 

        Set cn = New ADODB.Connection
        connectionString = "ODBC;DSN=MyDSN;Trusted_Connection=Yes;APP=Microsoft Office 2013;DATABASE=MyTest"
        cn.Open (connectionString)

I have validated and used "MyDSN" in other code so I know that the DSN settings are correct. When I execute the code above I get a run-time 80004005 error telling me that the "Data source name not found and no default driver specified"

Can I use a DSN connection with an ADODB connection?

HansUp
  • 95,961
  • 11
  • 77
  • 135
John S
  • 7,909
  • 21
  • 77
  • 145
  • Since the VBA is in Access, are you trying to connect to the same database? If so, you can just do: Set cn = New ADODB.Connection Set cn = CurrentProject.Connection – Wayne G. Dunn Apr 15 '14 at 16:20

2 Answers2

0

Discard "ODBC;" from the connection string. (I think supplying a DSN value already establishes that you want an ODBC connection.)

This example fails at cn.Open on my system with the same error message you reported ...

ConnectionString = "ODBC;DSN=sqlserver_local;Trusted_Connection=Yes;DATABASE=testbed"

But discarding "ODBC;" allowed cn.Open to succeed ...

ConnectionString = "DSN=sqlserver_local;Trusted_Connection=Yes;DATABASE=testbed"
HansUp
  • 95,961
  • 11
  • 77
  • 135
0

I am using the following connection string (sample only) which is working fine with Access 2003 and 2013 versions:

DRIVER= {Microsoft Access Driver (*.mdb)}; DBQ= C:\Test.mdb; DefaultDir = C:\; UID= admin; PWD= admin; FIL = MS Access; MaxBufferSize = 2048; MaxScanRows = 8; PageTimeout = 5; SafeTransactions = 0; Threads = 4; UserCommitSync = Yes;
rchacko
  • 1,965
  • 23
  • 24