Error message Command text was not set with the command obje

Kurt

Well-known Member
Joined
Jul 23, 2002
Messages
1,664
Hello Everyone,

I am trying to important some data from SQL. Here is my code:
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

Well-known Member
Joined
Jul 23, 2002
Messages
1,664
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
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,305
Office Version
  1. 365
Platform
  1. Windows
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.:eek:
 

Kurt

Well-known Member
Joined
Jul 23, 2002
Messages
1,664
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,305
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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).
 

Forum statistics

Threads
1,143,654
Messages
5,720,101
Members
422,266
Latest member
Mattyw

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top