Importing SQL To Excel Almost There

Hello Everyone,

Here is my code in Excel VBA:


Sub command_dyer3()
Dim conn As New Connection
Dim rec As New Recordset
Dim comm As New Command
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("command")
conn.Open "Provider=microsoft.jet.oledb.4.0;" & _
"Data Source=" + ThisWorkbook.Path + "FromDyer.mdb;"
Set comm.ActiveConnection = conn
comm.CommandText = _
strSQL = "USE CHEC" & _
"SELECT DISTINCT" & _
"DAT01.[_@051] AS Branch," & _
"DAT01.[_@550] AS LoanType," & _
"DAT01.[_@040] AS Date," & _
"DAT01.[_@LOAN#] AS LoanNum" & _
"FROM DAT01 INNER JOIN [DATE_CONVERSION_TABLE_NEW]" & _
"ON DAT01.[_@040] = [_@040]" & _
"INNER JOIN [SMT_BRANCHES]" & _
"ON DAT01.[_@051] = SMT_BRANCHES.[BranchNbr]" & _
"WHERE" & _
"DAT01.[_@040] Between '06/01/2006' And '06/30/2006'" & _
"AND DAT01.[_@051] = '540' " & _
"And DAT01.[_@LOAN#] Like '2%' " & _
"And DAT01.[_@550] = '3' " & _
"Group BY" & _
"DAT01.[_@051]," & _
"DAT01.[_@550]," & _
"DAT01.[_@TP]," & _
"DAT01.[_@040]," & _
"DAT01.[_@LOAN#]" & _
"ORDER BY --[DATE_CONVERSION_TABLE_NEW].MONTH," & _
"DAT01.[_@051]"
comm.CommandText = strSQL
'comm.Parameters(0) =
rec.Open comm
ws.[a1].CopyFromRecordset rec
rec.Close: conn.Close
End Sub


I get the following error mesage:

Command text was not set for the command object on the following line:

rec.Open comm

What am I doing wrong here?

TIA



Kurt