• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 385
  • Last Modified:

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
End Sub
1 Solution
Rory ArchibaldCommented:
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"
   End With
For iRow = 1 to 10
   Conn1.Execute "update computers set testvar = " & iRow
next iRow

Open in new window


Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now