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
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
Kurt

I don't know if it's the cause of the problem but your SQL string isn't right.
 
Upvote 0
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
 
Upvote 0
Kurt

Are you sure the workbook isn't in use?

Perhaps it has been opened via code and isn't visible?
 
Upvote 0
workbook already opened

Hello Norie,

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

TIA

Kurt
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,514
Messages
6,114,078
Members
448,547
Latest member
arndtea

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