Error message Command text was not set with the command obje

[Kurt]

Hello Everyone,

I am trying to important some data from SQL. Here is my code:

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

It stops on the line rec.Open comm and gives the error message in the subject line.

TIA for any help and have a great day!

Kurt

 

[Kurt]

Using ADO

Hello Norrie,

Yes I am using ADO.

However I am returning the recordset directly from a SQL Server table.

I am not getting it from inside the worksheet. I am getting the recordset from SQL Server.

What would I need to change in your code?

TIA

Kurt

 

[Norie]

Kurt

I'm a little confused the code you posted looked to me as though you were getting data from a worksheet not a database.

 

[Kurt]

sorry about that

Hello Norie,

Sorry about that.

How can we change the code so that it can see a strSQL connection?

TIA

Kurt

 

[Norie]

Kurt

Like I said I'm no expert on ADO - I rarely, if ever, use it.

I believe there are websites dedicated to connection strings for different data sources.

I suggest you try a search for them.

 

[Richard Schollar]

Kurt

Like I said before - record a macro using MSQuery to connect to the Db and look at the generated code. It should give you everything you need to use in an ADO connection string (although it will connect under ODBC - it is the constituent parts you are after).