Error message Command text was not set with the command obje

Kurt

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

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Kurt

You appear to have the "comm.CommandText = " line listed twice. try removing it and seeing if it helps:

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 
    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
 

Kurt

Well-known Member
Joined
Jul 23, 2002
Messages
1,648
Hello Richard,

I also beleive part of the problem is here:

Code:
conn.Open "Provider=microsoft.jet.oledb.4.0;" & _ 
        "Data Source=" + ThisWorkbook.Path + "\FromDyer.mdb;"

I am not using the access database any more it is directly coming from sql.

What do I need to change here?

TIA

Kurt
 

Kurt

Well-known Member
Joined
Jul 23, 2002
Messages
1,648
question on provider

Hello Richard,

I also beleive part of the problem is here:

Code:
conn.Open "Provider=microsoft.jet.oledb.4.0;" & _ 
        "Data Source=" + ThisWorkbook.Path + "\FromDyer.mdb;"

I am not using the access database any more it is directly coming from sql.

What do I need to change here?

TIA

Kurt
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707

ADVERTISEMENT

Kurt

It will depend on your exact circumstances ie Server Name etc. Easiest thing to do would be to record a macro of you using MSQuery to extract a sample of data (any data) from the relevant Db (ie go thru the wizard) and then look at the connection info this saves down in the macro code.

Can you do this?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,831
Office Version
  1. 365
Platform
  1. Windows
Kurt

I don't know if it's the cause of the problem but your SQL string isn't right.
 

Kurt

Well-known Member
Joined
Jul 23, 2002
Messages
1,648

ADVERTISEMENT

new error message

Hello Richard and everyone,

Here is the code again:

Code:
Sub command_dyer6()
    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
    Set comm.ActiveConnection = conn
    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 am now getting the error message:

The Microsoft Jet Engine database could not open the file c:\Documents and Settings\knichols\My Documents\RetailProcessCounts.xls It is already opened exclusively by another user , or you need permission to view its data.

This error message should not be happening.

Any thoughts out there anyone?

TIA

Kurt
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,831
Office Version
  1. 365
Platform
  1. Windows
Kurt

Are you sure the workbook isn't in use?

Perhaps it has been opened via code and isn't visible?
 

Kurt

Well-known Member
Joined
Jul 23, 2002
Messages
1,648
workbook already opened

Hello Norie,

Yes the workbook is already opened because I am using vba in that workbook.

TIA

Kurt
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,831
Office Version
  1. 365
Platform
  1. Windows
Kurt

Well that's probably the problem.

I take it you are using ADO?

I'm no expert on that I'm afraid, but perhaps there is some setting/parameter missing/wrong.

I'm able to use this simple code when the workbook is open.
Code:
Sub test()
Dim cn As ADODB.Connection
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
Set cn = New ADODB.Connection

With cn
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .ConnectionString = "Data Source=C:\My Documents\ADOTest.xls;" & _
"Extended Properties=Excel 8.0;"
    .Open
End With
    
rst.Open "Select * From [Sheet1$]", cn
While Not rst.EOF
    Set ws = Worksheets.Add
    ws.Range("A1").CopyFromRecordset rst, 2
Wend
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,109,028
Messages
5,526,332
Members
409,696
Latest member
EERS

This Week's Hot Topics

Top