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
......

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
.....