ODBC call failed?

[tyo]

what's wrong with this code, i was trying to create a connection to sql server 7 database in vba code. but when i run it, it keeps telling me run time error , ODBC call failed? anybody could hele me, thank u very much!

Sub QueryFN()

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;database=QuantumIreland;DSN=deals;UId=sa;PWD=sa"
Set dealsConnection = _
wrkODBC.OpenConnection("deals", , False, X)

Dim SQLStmt As String
SQLStmt = "SELECT deal_no, entity"
SQLStmt = SQLStmt & "FROM deals"
SQLStmt = SQLStmt & "WHERE deal_no = 16031"
dealsConnection.Execute 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

 

[rasage]

look at your SQL string, there is no space after your list of columns and the FROM keyword.

SQLStmt = "SELECT deal_no, entity"
SQLStmt = SQLStmt & "FROM deals"
SQLStmt = SQLStmt & "WHERE deal_no = 16031"
dealsConnection.Execute SQLStmt

this is read like...
SELECT deal_no, entityFROM dealsWHERE deal_no = 16031