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?
Dim strText As String
Dim iRow As Integer
For iRow = 1 To 10
strText = "update computers set testvar = " & iRow
.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