about execute sql and copy from recordset

[xq]

i have to connect to the database then query it, why there is an error which says 'object requied' on the recordset, , i don't know what's wrong with that? thanks a lot for any help!
here's the code.

Sub QueryFN()

ThisWorkbook.Activate
Dim numberOfRows
Dim wrkODBC As Workspace
Set wrkODBC = CreateWorkspace("ODBCWorkspace", "", "", dbUseODBC)

Dim X As String
Dim dealsConnection As Connection
Dim rs As Recordset

X = "ODBC;filedsn=c:\Program Files\Common Files\ODBC\Data Sources\QuantumIreland;DBQ=QuantumIreland;DSN=deals;UId=sa;PWD=sa"
Set dealsConnection = _
wrkODBC.OpenConnection("deals", , False, X)

'Set rs = dealsConnection.OpenRecordset("deals", dbOpenDynamic)

Dim SQLStmt As String
SQLStmt = ""
SQLStmt = SQLStmt & "SELECT deal_no"
SQLStmt = SQLStmt & " FROM deals"
SQLStmt = SQLStmt & " WHERE deal_no = 15236"

Set rs = dealsConnection.OpenRecordset(SQLStmt)

With ThisWorkbook.Sheets("Sheet1&quot
With .Cells(2, 1)
.CurrentRegion.Clear
numberOfRows.CopyFromRecordset (rs)
End With
End With

rs.Close
dealsConnection.Close
End Sub

 

[Rolliee]

The SELECT would indicate that you wanted only the deal_no field from the table. Then you appear to want more of the fields. If you want all the fields, SELECT * From deals;


Rolliee