.Refresh BackgroundQuery:=False - Debug Fault

Hi, I am very new to VBA programming and I would like to query a database to import data into excel.  I have the code which runs until it gets to '.Refresh BackgroundQuery:=False' and then highlights yellow.  I was wondering if anyone could help out.  Thanks in advance.

Here is my code:

Sub Report_CIP_Time()
'
' Macro recorded 7/25/07 J Holmes/B Durbin
'
Sheets("Download").Select
Range("A1").Select
    With Selection.QueryTable
        .Connection = Array(Array("ODBC;DRIVER=SQL Server;SERVER=DESLCDSUMES2;UID=" & Range("Report!A4").Text & _
        ";APP=Microsoft Office XP;WSID=DESLCDSUWK25;DATABASE=Sara_Lee_TimeLog;Trusted_Connection=Yes"))
        .CommandText = Array("SELECT " & _
        "slTS_CIP.TCIP_ID, slTS_CIP.TCIP_DateTime, slTS_CIP.TCIP_OB_Name, slTS_CIP.TCIP_TT_CAT, slTS_CIP.TCIP_LT_CAT, " _
        , "slTS_CIP.TCIP_TT_CT_1 , slTS_CIP.TCIP_LT_CT_1, slTS_CIP.TCIP_CIT_O_A, slTS_CIP.TCIP_CIT_O_B, slTS_CIP.TCIP_CIT_O_C, " _
        , "slTS_CIP.TCIP_CIT_CT_1 , slTS_CIP.TCIP_TPV_CTH_1_OP, slTS_CIP.TCIP_TPV_CTH_1_SP, slTS_CIP.TCIP_CIT_CT_2, " _
        , "slTS_CIP.TCIP_TPV_CTH_2_OP , slTS_CIP.TCIP_TPV_CTH_2_SP, slTS_CIP.TCIP_TT_CT_2, slTS_CIP.TCIP_LT_CT_2, " _
        , "slTS_CIP.TCIP_FT_IP_A , slTS_CIP.TCIP_FT_IP_B, slTS_CIP.TCIP_FT_IP_C, slTS_CIP.TCIP_PIT1_IF_A, slTS_CIP.TCIP_PIT1_IF_B, " _
        , "slTS_CIP.TCIP_PIT1_IF_C , slTS_CIP.TCIP_PIT2_IF_A, slTS_CIP.TCIP_PIT2_IF_B, slTS_CIP.TCIP_PIT2_IF_C, slTS_CIP.TCIP_TT_CPT, " _
        , "slTS_CIP.TCIP_LT_CPT , slTS_CIP.TCIP_TU_O_A, slTS_CIP.TCIP_TU_O_B, slTS_CIP.TCIP_TU_O_C, slTS_CIP.TCIP_FU_IP_A_CO_OP, " _
        , "slTS_CIP.TCIP_FU_IP_A_CO_SP , slTS_CIP.TCIP_FU_IP_B_CO_OP, slTS_CIP.TCIP_FU_IP_B_CO_SP, slTS_CIP.TCIP_FU_IP_C_CO_OP, " _
        , "slTS_CIP.TCIP_FU_IP_C_CO_SP , slTS_CIP.TCIP_TU_CT_1, slTS_CIP.TCIP_TU_CT_2, " _
        & Chr(13) & "" & Chr(10) & "FROM Sara_Lee_TimeLog.dbo.slTS_CIP slTS_CIP" & Chr(13) & "" & Chr(10) & _
        "WHERE " _
        , "(slTS_CIP.TCIP_DateTime>={ts '" & Range("Report!A1").Text & "'}) AND " _
        , "(slTS_CIP.TCIP_DateTime<={ts '" & Range("Report!A2").Text & "'}) " _
        & Chr(13) & "" & Chr(10) & "ORDER BY slTS_CIP.TCIP_DateTime ")
        .Refresh BackgroundQuery:=False
       
    End With
        Sheets("Report").Select
        Range("H1").Select
End Sub
bdurb75Asked:
Who is Participating?
 
RobSampsonConnect With a Mentor Commented:
Hi,

I am sorry for the continued non-success, but I will keep trying!  I have set up a bit of a test case this time, and I have also looked at the MSDN articles, which do not state that the Connection property, or the CommandText property, are actually supposed to be an Array.
http://msdn2.microsoft.com/en-us/library/aa214213(office.11).aspx
http://msdn2.microsoft.com/en-us/library/aa193405(office.10).aspx

Also, by outputting your query string to a text file, I have discovered that there are too many comma's in your string.

Have a go at this section, which is just the With block....:
'==========
    With Selection.QueryTable
        .Connection = "ODBC;DRIVER=SQL Server;SERVER=DESLCDSUMES2;UID=" & Range("Report!A4").Text & _
        ";APP=Microsoft Office XP;WSID=DESLCDSUWK25;DATABASE=Sara_Lee_TimeLog;Trusted_Connection=Yes"
        .CommandText = "SELECT " & _
        "slTS_CIP.TCIP_ID, slTS_CIP.TCIP_DateTime, slTS_CIP.TCIP_OB_Name, slTS_CIP.TCIP_TT_CAT, slTS_CIP.TCIP_LT_CAT, " & _
        "slTS_CIP.TCIP_TT_CT_1 , slTS_CIP.TCIP_LT_CT_1, slTS_CIP.TCIP_CIT_O_A, slTS_CIP.TCIP_CIT_O_B, slTS_CIP.TCIP_CIT_O_C, " & _
        "slTS_CIP.TCIP_CIT_CT_1 , slTS_CIP.TCIP_TPV_CTH_1_OP, slTS_CIP.TCIP_TPV_CTH_1_SP, slTS_CIP.TCIP_CIT_CT_2, " & _
        "slTS_CIP.TCIP_TPV_CTH_2_OP , slTS_CIP.TCIP_TPV_CTH_2_SP, slTS_CIP.TCIP_TT_CT_2, slTS_CIP.TCIP_LT_CT_2, " & _
        "slTS_CIP.TCIP_FT_IP_A , slTS_CIP.TCIP_FT_IP_B, slTS_CIP.TCIP_FT_IP_C, slTS_CIP.TCIP_PIT1_IF_A, slTS_CIP.TCIP_PIT1_IF_B, " & _
        "slTS_CIP.TCIP_PIT1_IF_C , slTS_CIP.TCIP_PIT2_IF_A, slTS_CIP.TCIP_PIT2_IF_B, slTS_CIP.TCIP_PIT2_IF_C, slTS_CIP.TCIP_TT_CPT, " & _
        "slTS_CIP.TCIP_LT_CPT , slTS_CIP.TCIP_TU_O_A, slTS_CIP.TCIP_TU_O_B, slTS_CIP.TCIP_TU_O_C, slTS_CIP.TCIP_FU_IP_A_CO_OP, " & _
        "slTS_CIP.TCIP_FU_IP_A_CO_SP , slTS_CIP.TCIP_FU_IP_B_CO_OP, slTS_CIP.TCIP_FU_IP_B_CO_SP, slTS_CIP.TCIP_FU_IP_C_CO_OP, " & _
        "slTS_CIP.TCIP_FU_IP_C_CO_SP , slTS_CIP.TCIP_TU_CT_1, slTS_CIP.TCIP_TU_CT_2 " & _
        Chr(13) & "" & Chr(10) & "FROM Sara_Lee_TimeLog.dbo.slTS_CIP slTS_CIP" & Chr(13) & "" & Chr(10) & _
        "WHERE " & _
        "(slTS_CIP.TCIP_DateTime>={ts '" & Range("Report!A1").Text & "'}) AND " & _
        "(slTS_CIP.TCIP_DateTime<={ts '" & Range("Report!A2").Text & "'}) " & _
        Chr(13) & "" & Chr(10) & "ORDER BY slTS_CIP.TCIP_DateTime "

       'MsgBox .Connection(0)
        'MsgBox .CommandText(0)
     
        .Refresh BackgroundQuery:=False
       
    End With
'========

Regards,

Rob.
0
 
RobSampsonCommented:
Just before the .Refresh, try putting
MsgBox .Connection(0)
MsgBox .CommandText(0)

That should output the statement you've used.  Double-check that the string is formatted correctly.
I suspect there may be an incorrect syntax in those arguments.

Regards,

Rob.
0
 
bdurb75Author Commented:
I get a run-time error '451'

Property let procedure not defined and property get procedure did not return an object

0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
RobSampsonCommented:
Hmmm, not sure about that one.  Try just using
MsgBox "Connection string is:" & vbCrLf & "ODBC;DRIVER=SQL Server;SERVER=DESLCDSUMES2;UID=" & Range("Report!A4").Text & _
        ";APP=Microsoft Office XP;WSID=DESLCDSUWK25;DATABASE=Sara_Lee_TimeLog;Trusted_Connection=Yes"
MsgBox "CommandText is:" & vbCrLf & "SELECT " & _
        "slTS_CIP.TCIP_ID, slTS_CIP.TCIP_DateTime, slTS_CIP.TCIP_OB_Name, slTS_CIP.TCIP_TT_CAT, slTS_CIP.TCIP_LT_CAT, " _
        , "slTS_CIP.TCIP_TT_CT_1 , slTS_CIP.TCIP_LT_CT_1, slTS_CIP.TCIP_CIT_O_A, slTS_CIP.TCIP_CIT_O_B, slTS_CIP.TCIP_CIT_O_C, " _
        , "slTS_CIP.TCIP_CIT_CT_1 , slTS_CIP.TCIP_TPV_CTH_1_OP, slTS_CIP.TCIP_TPV_CTH_1_SP, slTS_CIP.TCIP_CIT_CT_2, " _
        , "slTS_CIP.TCIP_TPV_CTH_2_OP , slTS_CIP.TCIP_TPV_CTH_2_SP, slTS_CIP.TCIP_TT_CT_2, slTS_CIP.TCIP_LT_CT_2, " _
        , "slTS_CIP.TCIP_FT_IP_A , slTS_CIP.TCIP_FT_IP_B, slTS_CIP.TCIP_FT_IP_C, slTS_CIP.TCIP_PIT1_IF_A, slTS_CIP.TCIP_PIT1_IF_B, " _
        , "slTS_CIP.TCIP_PIT1_IF_C , slTS_CIP.TCIP_PIT2_IF_A, slTS_CIP.TCIP_PIT2_IF_B, slTS_CIP.TCIP_PIT2_IF_C, slTS_CIP.TCIP_TT_CPT, " _
        , "slTS_CIP.TCIP_LT_CPT , slTS_CIP.TCIP_TU_O_A, slTS_CIP.TCIP_TU_O_B, slTS_CIP.TCIP_TU_O_C, slTS_CIP.TCIP_FU_IP_A_CO_OP, " _
        , "slTS_CIP.TCIP_FU_IP_A_CO_SP , slTS_CIP.TCIP_FU_IP_B_CO_OP, slTS_CIP.TCIP_FU_IP_B_CO_SP, slTS_CIP.TCIP_FU_IP_C_CO_OP, " _
        , "slTS_CIP.TCIP_FU_IP_C_CO_SP , slTS_CIP.TCIP_TU_CT_1, slTS_CIP.TCIP_TU_CT_2, " _
        & Chr(13) & "" & Chr(10) & "FROM Sara_Lee_TimeLog.dbo.slTS_CIP slTS_CIP" & Chr(13) & "" & Chr(10) & _
        "WHERE " _
        , "(slTS_CIP.TCIP_DateTime>={ts '" & Range("Report!A1").Text & "'}) AND " _
        , "(slTS_CIP.TCIP_DateTime<={ts '" & Range("Report!A2").Text & "'}) " _
        & Chr(13) & "" & Chr(10) & "ORDER BY slTS_CIP.TCIP_DateTime "


and make sure that syntax is correct.

Regards,

Rob.
0
 
bdurb75Author Commented:
Now I get a Compile error:  Wrong number of arguments or invalid property assignment.   It highlights the first line.
0
 
RobSampsonCommented:
Hi, I've examined the strings. The Connection String is OK, but the Command Text is not.  There are no ampersands at the end of each line, and the lines starting with the commas should have the quote moved to just before that comma.
Try these:
MsgBox "Connection string is:" & vbCrLf & "ODBC;DRIVER=SQL Server;SERVER=DESLCDSUMES2;UID=" & Range("Report!A4").Text & _
        ";APP=Microsoft Office XP;WSID=DESLCDSUWK25;DATABASE=Sara_Lee_TimeLog;Trusted_Connection=Yes"
MsgBox "CommandText is:" & vbCrLf & "SELECT " & _
        "slTS_CIP.TCIP_ID, slTS_CIP.TCIP_DateTime, slTS_CIP.TCIP_OB_Name, slTS_CIP.TCIP_TT_CAT, slTS_CIP.TCIP_LT_CAT, " & _
        ", slTS_CIP.TCIP_TT_CT_1 , slTS_CIP.TCIP_LT_CT_1, slTS_CIP.TCIP_CIT_O_A, slTS_CIP.TCIP_CIT_O_B, slTS_CIP.TCIP_CIT_O_C, " & _
        ", slTS_CIP.TCIP_CIT_CT_1 , slTS_CIP.TCIP_TPV_CTH_1_OP, slTS_CIP.TCIP_TPV_CTH_1_SP, slTS_CIP.TCIP_CIT_CT_2, " & _
        ", slTS_CIP.TCIP_TPV_CTH_2_OP , slTS_CIP.TCIP_TPV_CTH_2_SP, slTS_CIP.TCIP_TT_CT_2, slTS_CIP.TCIP_LT_CT_2, " & _
        ", slTS_CIP.TCIP_FT_IP_A , slTS_CIP.TCIP_FT_IP_B, slTS_CIP.TCIP_FT_IP_C, slTS_CIP.TCIP_PIT1_IF_A, slTS_CIP.TCIP_PIT1_IF_B, " & _
        ", slTS_CIP.TCIP_PIT1_IF_C , slTS_CIP.TCIP_PIT2_IF_A, slTS_CIP.TCIP_PIT2_IF_B, slTS_CIP.TCIP_PIT2_IF_C, slTS_CIP.TCIP_TT_CPT, " & _
        ", slTS_CIP.TCIP_LT_CPT , slTS_CIP.TCIP_TU_O_A, slTS_CIP.TCIP_TU_O_B, slTS_CIP.TCIP_TU_O_C, slTS_CIP.TCIP_FU_IP_A_CO_OP, " & _
        ", slTS_CIP.TCIP_FU_IP_A_CO_SP , slTS_CIP.TCIP_FU_IP_B_CO_OP, slTS_CIP.TCIP_FU_IP_B_CO_SP, slTS_CIP.TCIP_FU_IP_C_CO_OP, " & _
        ", slTS_CIP.TCIP_FU_IP_C_CO_SP , slTS_CIP.TCIP_TU_CT_1, slTS_CIP.TCIP_TU_CT_2, " & _
        Chr(13) & "" & Chr(10) & "FROM Sara_Lee_TimeLog.dbo.slTS_CIP slTS_CIP" & Chr(13) & "" & Chr(10) & _
        "WHERE " & _
        ", (slTS_CIP.TCIP_DateTime>={ts '" & Range("Report!A1").Text & "'}) AND " & _
        ", (slTS_CIP.TCIP_DateTime<={ts '" & Range("Report!A2").Text & "'}) " & _
        Chr(13) & "" & Chr(10) & "ORDER BY slTS_CIP.TCIP_DateTime "

Regards,

Rob.
0
 
bdurb75Author Commented:
Hey Rob, I've tried to insert this code into the macro and get a bunch of compile errors.  I fixed the compile errors and still get the same error.  Thank you for your continued support...
0
 
RobSampsonCommented:
Hi,

When I use the MsgBox with the strings I posted above I do not get any compile errors, and the SQL string looks OK to me.  Did it work with the MsgBox's?  Does the string look OK to you?

Regards,

Rob.
0
 
bdurb75Author Commented:
With the MsgBox alone, I didn't get the compile errors, it was when I put in the string to actually get the information.
0
 
bdurb75Author Commented:
Sub Macro6()
'
' Macro6 Macro
' Macro recorded 7/31/2007 by bdurbin
'
MsgBox "Connection string is:" & vbCrLf & "ODBC;DRIVER=SQL Server;SERVER=DESLCDSUMES2;UID=" & Range("Report!A4").Text & _
        ";APP=Microsoft Office XP;WSID=DESLCDSUWK25;DATABASE=Sara_Lee_TimeLog;Trusted_Connection=Yes"
MsgBox "CommandText is:" & vbCrLf & "SELECT " & _
        "slTS_CIP.TCIP_ID, slTS_CIP.TCIP_DateTime, slTS_CIP.TCIP_OB_Name, slTS_CIP.TCIP_TT_CAT, slTS_CIP.TCIP_LT_CAT, " & _
        ", slTS_CIP.TCIP_TT_CT_1 , slTS_CIP.TCIP_LT_CT_1, slTS_CIP.TCIP_CIT_O_A, slTS_CIP.TCIP_CIT_O_B, slTS_CIP.TCIP_CIT_O_C, " & _
        ", slTS_CIP.TCIP_CIT_CT_1 , slTS_CIP.TCIP_TPV_CTH_1_OP, slTS_CIP.TCIP_TPV_CTH_1_SP, slTS_CIP.TCIP_CIT_CT_2, " & _
        ", slTS_CIP.TCIP_TPV_CTH_2_OP , slTS_CIP.TCIP_TPV_CTH_2_SP, slTS_CIP.TCIP_TT_CT_2, slTS_CIP.TCIP_LT_CT_2, " & _
        ", slTS_CIP.TCIP_FT_IP_A , slTS_CIP.TCIP_FT_IP_B, slTS_CIP.TCIP_FT_IP_C, slTS_CIP.TCIP_PIT1_IF_A, slTS_CIP.TCIP_PIT1_IF_B, " & _
        ", slTS_CIP.TCIP_PIT1_IF_C , slTS_CIP.TCIP_PIT2_IF_A, slTS_CIP.TCIP_PIT2_IF_B, slTS_CIP.TCIP_PIT2_IF_C, slTS_CIP.TCIP_TT_CPT, " & _
        ", slTS_CIP.TCIP_LT_CPT , slTS_CIP.TCIP_TU_O_A, slTS_CIP.TCIP_TU_O_B, slTS_CIP.TCIP_TU_O_C, slTS_CIP.TCIP_FU_IP_A_CO_OP, " & _
        ", slTS_CIP.TCIP_FU_IP_A_CO_SP , slTS_CIP.TCIP_FU_IP_B_CO_OP, slTS_CIP.TCIP_FU_IP_B_CO_SP, slTS_CIP.TCIP_FU_IP_C_CO_OP, " & _
        ", slTS_CIP.TCIP_FU_IP_C_CO_SP , slTS_CIP.TCIP_TU_CT_1, slTS_CIP.TCIP_TU_CT_2, " & _
        Chr(13) & "" & Chr(10) & "FROM Sara_Lee_TimeLog.dbo.slTS_CIP slTS_CIP" & Chr(13) & "" & Chr(10) & _
        "WHERE " & _
        ", (slTS_CIP.TCIP_DateTime>={ts '" & Range("Report!A1").Text & "'}) AND " & _
        ", (slTS_CIP.TCIP_DateTime<={ts '" & Range("Report!A2").Text & "'}) " & _
        Chr(13) & "" & Chr(10) & "ORDER BY slTS_CIP.TCIP_DateTime "


Sheets("Download").Select
Range("A1").Select
    With Selection.QueryTable
        .Connection = Array(Array("ODBC;DRIVER=SQL Server;SERVER=DESLCDSUMES2;UID=" & Range("Report!A4").Text & _
        ";APP=Microsoft Office XP;WSID=DESLCDSUWK25;DATABASE=Sara_Lee_TimeLog;Trusted_Connection=Yes"))
        .CommandText = Array("SELECT " & _
        "slTS_CIP.TCIP_ID, slTS_CIP.TCIP_DateTime, slTS_CIP.TCIP_OB_Name, slTS_CIP.TCIP_TT_CAT, slTS_CIP.TCIP_LT_CAT, " & _
        ", slTS_CIP.TCIP_TT_CT_1 , slTS_CIP.TCIP_LT_CT_1, slTS_CIP.TCIP_CIT_O_A, slTS_CIP.TCIP_CIT_O_B, slTS_CIP.TCIP_CIT_O_C, " & _
        ", slTS_CIP.TCIP_CIT_CT_1 , slTS_CIP.TCIP_TPV_CTH_1_OP, slTS_CIP.TCIP_TPV_CTH_1_SP, slTS_CIP.TCIP_CIT_CT_2, " & _
        ", slTS_CIP.TCIP_TPV_CTH_2_OP , slTS_CIP.TCIP_TPV_CTH_2_SP, slTS_CIP.TCIP_TT_CT_2, slTS_CIP.TCIP_LT_CT_2, " & _
        ", slTS_CIP.TCIP_FT_IP_A , slTS_CIP.TCIP_FT_IP_B, slTS_CIP.TCIP_FT_IP_C, slTS_CIP.TCIP_PIT1_IF_A, slTS_CIP.TCIP_PIT1_IF_B, " & _
        ", slTS_CIP.TCIP_PIT1_IF_C , slTS_CIP.TCIP_PIT2_IF_A, slTS_CIP.TCIP_PIT2_IF_B, slTS_CIP.TCIP_PIT2_IF_C, slTS_CIP.TCIP_TT_CPT, " & _
        ", slTS_CIP.TCIP_LT_CPT , slTS_CIP.TCIP_TU_O_A, slTS_CIP.TCIP_TU_O_B, slTS_CIP.TCIP_TU_O_C, slTS_CIP.TCIP_FU_IP_A_CO_OP, " & _
        ", slTS_CIP.TCIP_FU_IP_A_CO_SP , slTS_CIP.TCIP_FU_IP_B_CO_OP, slTS_CIP.TCIP_FU_IP_B_CO_SP, slTS_CIP.TCIP_FU_IP_C_CO_OP, " & _
        ", slTS_CIP.TCIP_FU_IP_C_CO_SP , slTS_CIP.TCIP_TU_CT_1, slTS_CIP.TCIP_TU_CT_2, " & _
        Chr(13) & "" & Chr(10) & "FROM Sara_Lee_TimeLog.dbo.slTS_CIP slTS_CIP" & Chr(13) & "" & Chr(10) & _
        "WHERE " & _
        ", (slTS_CIP.TCIP_DateTime>={ts '" & Range("Report!A1").Text & "'}) AND " & _
        ", (slTS_CIP.TCIP_DateTime<={ts '" & Range("Report!A2").Text & "'}) " & _
        Chr(13) & "" & Chr(10) & "ORDER BY slTS_CIP.TCIP_DateTime "

       'MsgBox .Connection(0)
        'MsgBox .CommandText(0)
     
        .Refresh BackgroundQuery:=False
       
    End With
        Sheets("Report").Select
        Range("H1").Select

End Sub
0
 
RobSampsonCommented:
Hmmm, without testing it, it looks like you just need a closing bracket on the end of the .connection text, which would go right after the bottom line, so this:
Chr(13) & "" & Chr(10) & "ORDER BY slTS_CIP.TCIP_DateTime "

would become this:
Chr(13) & "" & Chr(10) & "ORDER BY slTS_CIP.TCIP_DateTime ")

and then the text should not be red anymore, and you should not get a compile error.

Regards,

Rob.
0
 
bdurb75Author Commented:
Rob,   I made the change of the closing bracket and put an underscore at the end of:

.CommandText = Array("SELECT " & _

It made the text black but I still get the compile error.  I changed the string back to the original and I get the same error with  .Refresh BackgroundQuery:=False
0
 
bdurb75Author Commented:
Rob, Thank you very much, it works!!!

Only one thing, when I erase the data manually (I have some graphs that depict the data and it automatically changed the source data to the colums that are after the new data that the import brings in) it has an error of 'With Selection.QueryTable'.

I think I can just update the graphs for the new references though
0
 
RobSampsonCommented:
Maybe when you delete the data manually, you are erasing the reference to the Query Table, so it cannot be selected anymore, because it doesn't exist.

Is there another way to clear the contents of the cells without wiping out the Query Table reference? Maybe just use Edit --> Clear --> Contents?

Regards,

Rob.
0
 
bdurb75Author Commented:
Thank you Rob for your determination!!!!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.