why is my recordset empty?

[SandyR]

I am trying to get records from an SQL database into an excel worksheet using
VBA. I can connect to the database without errors, but I don't get any
records back (recordcount is -1 and BOF and EOF are both true). If I run the
same query in query analyzer, it returns records. Here is my code:

Set CN = New ADODB.Connection
CN.ConnectionString = "DRIVER={SQL
SERVER};SERVER=TAXSVR\MUNIS;DATABASE=mu_live;UID=userid;PWD=password"
strSQL = "select * from pr_earn_history where a_employee_number = 20390 "
' run query to get data into the recordset
CN.Open
Set RST = CN.Execute(strSQL)

Set WS = wb.Worksheets("SHEET1")
WS.Activate
NROWS = wb.Worksheets("SHEET1").Range("B1").End(xlDown).Row

' get the recordset into the worksheet
Set STARTLOC = WS.Cells(NROWS + 1, 1)
STARTLOC.CopyFromRecordset RST
CN.Close

 

[krazymike]

Why are you using execute() and not open()?
http://www.codefixer.com/tutorials/recordcount.asp

This code queries the table, populates the header row with the field
names and then proceeds to populate the sheet with the data:

Public Sub FillFromSQL()
' by krazymike

Dim conn As New ADODB.Connection, rsA As New ADODB.Recordset
conn.ConnectionString = "Provider=MSDASQL.1;Password=PASSWORE;User
ID=USERNAME;Data Source=clx;Extended
Properties=""DSN=clx;UID=USERNAME;PWD=PASSWORD;APP=Microsoft Office
2003;WSID=WS-LOGINNAME"""
conn.Open
rsA.ActiveConnection = conn
rsA.CursorLocation = adUseServer
rsA.Open "select * from [DBname].dbo.tablename", conn, 1, 3
For i = 0 To rsA.Fields.Count - 1
Sheet1.Cells(1, i + 1) = rsA.Fields(i).Name
Next
i = 2
rsA.MoveFirst
Do Until rsA.EOF
With rsA
For j = 0 To rsA.Fields.Count - 1
Sheet1.Cells(i, j + 1) =
rsA.Fields(rsA.Fields(j).Name)
Next j
End With
i = i + 1
rsA.MoveNext
Loop
rsA.Close
MsgBox "Done importing data", vbInformation
End Sub

 

[SandyR]

Thanks for your reply.

i used execute() because I had used it successfully for a different SQL
database. I changed the syntax to do it the way that you did, but it still
isn't pulling up any records. The same query in SQL query analyzer does pull
up records.