• 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
        ActiveWorkbook.Connections("SampleQuery").Refresh
    Next
End Sub
0
malcolm29
Asked:
malcolm29
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"
      .Open
   End With
For iRow = 1 to 10
   Conn1.Execute "update computers set testvar = " & iRow
next iRow

Open in new window

0

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