ODBC connection by udl
 

I am working on Excel Macro that run query and retrieve
data from SQL server. I made a udl file in the same folder
as the Excel file and it can successfully connect to the
server.
The excel macro code as below. Can someone give me some
hints why it cannot connect through udl? Or is there any
way to solve? Thanks a lot.



Dim rstRecordset As ADODB.Recordset
Dim cn As ADODB.Connection

Set cn = New ADODB.Connection
With cn
.ConnectionString = "File Name=edoc.udl;"
.CursorLocation = adUseServer
.Open
End With

Set rstRecordset = New ADODB.Recordset
With rstRecordset
.CursorType = adOpenStatic
.LockType = adLockReadOnly
End With
......

ODBC connection by udl
 

Try using the path in the connection string e.g.

.ConnectionString = "File Name=" & ThisWorkbook.Path & _
Application.PathSeparator & _
"edoc.udl;"

--

"Dorothy" wrote in message ...
I am working on Excel Macro that run query and retrieve
data from SQL server. I made a udl file in the same folder
as the Excel file and it can successfully connect to the
server.
The excel macro code as below. Can someone give me some
hints why it cannot connect through udl? Or is there any
way to solve? Thanks a lot.



Dim rstRecordset As ADODB.Recordset
Dim cn As ADODB.Connection

Set cn = New ADODB.Connection
With cn
.ConnectionString = "File Name=edoc.udl;"
.CursorLocation = adUseServer
.Open
End With

Set rstRecordset = New ADODB.Recordset
With rstRecordset
.CursorType = adOpenStatic
.LockType = adLockReadOnly
End With
.....