[Microsoft][ODBC Driver Manager] Data source file not found and no default driver specified.

I have a VBA code (in excel 2016) that i use to grab information from a MS SQL server (2008R). The connection works fine on the computer i wrote it on. When i run it on another computer i get the error "[Microsoft][ODBC Driver Manager] Data source file not found and no default driver specified".

Below is the connection string:

Set Cn = New ADODB.Connection
Cn.Open "Driver={SQL Server};Server=" & Server_Name & ";Database=" & Database_Name & _
";Uid=" & User_ID & ";Pwd=" & Password & ";"

The server computer is on my local network along with both computers i am trying to run the VBA on.

I have spent a lot of time trying different ways to fix this problem. Downloading different driver packages installing them and changing the Driver in the connection. I am aware of the ODBC Data Source Administrator Software in my windows 10 computer. I have created different DSN's in both 32 and 64 bit just hoping something would make it work (not really understanding why i'm doing that).

I will note that the computer it does not work on has a software that was installed that also installed the SQL server 2008 express with the instillation. That computer has a 32 bit DSN entery (i assume during the install of the software) and its driver is "SQL Native Client".

Hope this information is not to cunfusing as I am a little out of the knowledge area.

Warm Regards,

Lee

1 answer

Hello Lee,
i'm new on this forum i hope that my answer isnt too late :)

So firstwall i dont use :

Set Cn = New ADODB.Connection

instead this (to avoid problems with libraries):

Dim Cn As Object

Dim RS AS Object

Dim ConnStr As String

Dim SQL1 As String

ConnStr="Driver={SQL Server};Server=" & Server_Name & ";Database=" & Database_Name & _
";Uid=" & User_ID & ";Pwd=" & Password & ";"

Set Cn=CreateObject("ADODB.connection")

Set RS= CreateObject("ADODB.RecordSet")

SQL1="...your sql querry.."

Cn.Open ConnStr

Set RS=Cn.Execute(SQL1) 'recordset with data from your sql querry

You can get easy data from RS eg. target(cells or range) .CopyFromRecordSet RS

or use loop through values

Do While Not RS.EOF

 For i=1 To RS.Fileds.Count Step 1
       
       Msgbox Cstr(RS.Fileds(i-1).Value)
       
 Next i

 RS.movenext

Loop

or simple destination=RS.GetRows()

Hope thats will little help.