[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

0
edit delete
are you sure?
asked lee avery

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.

0
edit delete
are you sure?
answered Krysztof Spaliński

Post an answer but please log in first

Post Answer
  1. Please log in to post answer

Similar Questions

Tagsfor this question

Forumall questions