Excel Query to update a SQL db

I am using Excel to update a SQL DB table (I do not have the luxury of moving to a different tool to accomplish this for the user).  The code works fine if I only make one update.  However, I need to step through a number of updates and that's when I get an error:
"Runtime Error 1004: the operation cannot be done because the data is refreshing in the background."

If I step through the code in debug mode, I don't get any errors and all items get updated.
If I change .BackgroundQuery = True to .BackgroundQuery = False I get another error, essentially telling me the same thing.

Is there anything I can do in my code to allow for multiple updates without this error?

Sub UpdateSampleTable()
    Dim strText As String
    Dim iRow As Integer
    For iRow = 1 To 10
        strText = "update computers set testvar = " & iRow
        With ActiveWorkbook.Connections("SampleQuery").ODBCConnection
            .BackgroundQuery = True
            .CommandText = Array(strText)
            .CommandType = xlCmdSql
            .Connection = "ODBC;DRIVER=SQL Server;SERVER=sql;UID=username;Trusted_Connection=Yes;APP=Microsoft Office 2010;WSID=USERNAME"
            .RefreshOnFileOpen = False
            .SavePassword = False
            .SourceConnectionFile = ""
            .SourceDataFile = ""
            .ServerCredentialsMethod = xlCredentialsMethodIntegrated
            .AlwaysUseConnectionFile = False
        End With
        ActiveWorkbook.Connections("SampleQuery").Refresh
    Next
End Sub
malcolm29Asked:
Who is Participating?
 
Rory ArchibaldConnect With a Mentor Commented:
That's an unusual way of doing things. I would use ADO:

   Dim conn1 As Object
    
    ' Create the Connection object.
   Set conn1 = CreateObject("ADODB.Connection")
   
   With conn1
      .Provider = "sqloledb"
      .ConnectionString = "DRIVER={SQL Server};Server=SERVER=sql;UID=username;Trusted_Connection=Yes;APP=Microsoft Office 2010;WSID=USERNAME"
      .Open
   End With
For iRow = 1 to 10
   Conn1.Execute "update computers set testvar = " & iRow
next iRow

Open in new window

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.