0

I have the following update statements which are execute via VBA. It seems to be sub-optimal to have multiple update statements. How do i consolidate these into a single statement? The statements update an Access database.

strSqlLoc = "UPDATE table1 AS type SET type.Value='" & Range("C" & i).Value & "' WHERE PropertyID=" & Range("B" & i).Value & ";"
strSqlEnv = "UPDATE table1 AS type SET type.Value='" & Range("E" & i).Value & "' WHERE PropertyID=" & Range("D" & i).Value & ";"
strSqlClass = "UPDATE table1 AS type SET type.Value='" & Range("G" & i).Value & "' WHERE PropertyID=" & Range("F" & i).Value & ";"

Set rs = cn.Execute(strSqlLoc)
Set rs = cn.Execute(strSqlEnv)
Set rs = cn.Execute(strSqlClass)
PeanutsMonkey
  • 6,919
  • 23
  • 73
  • 103
  • I don't know if it will work, but you might be able to write one long batch statement and execute the whole batch at once. – RubberDuck May 07 '14 at 01:41

1 Answers1

1

Another Update

For multiple row updates it looks like Batch Update could do the trick. 

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset

Dim strSQL As String
Dim lngUpdated As Long

rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenKeyset
rst.CursorLocation = adUseClient
rst.LockType = adLockBatchOptimistic
rst.Open ("Select * from table1 ")

lngUpdated = 1

rst.Find "PropertyID=" & Range("B1")

Do Until rst.EOF
    rst("Value") = Range("C" & lngUpdated)
    lngUpdated = lngUpdated + 1
    rst.Find "PropertyID=" & Range("B" & lngUpdated), 1, adSearchForward
Loop

// Try repeating above for the other two columns?

rst.UpdateBatch

rst.Close
Set rst = Nothing

Can't really get much better than this, unless the data you are updating is already somewhere in the DB. You could try the case statement technique described here

Update:

I think my intuition behind that batch upload thought was that if you can somehow get the data on the server side in one shot, you could then join on it and update another table in one update statement. if you are only updating three values - look into creating a query on access side and passing in your six parameters in a single call, then use the technique described here inside the query

Community
  • 1
  • 1
Alex M
  • 690
  • 4
  • 18
  • 1
    Hmm, that would mean i would validate each record against the database before updating it. Wouldn't that place additional load and is it really necessary? – PeanutsMonkey May 07 '14 at 01:33
  • @PeanutsMonkey I believe you're correct about that. – RubberDuck May 07 '14 at 01:42
  • you're right guys, I made some clarifying updates above – Alex M May 07 '14 at 01:47
  • @Alex M - The intention is to upload it to the database in one go using Excel as the frontend. There will be more than 3 queries. Potentially up to 30 hence the want to optimize it. – PeanutsMonkey May 07 '14 at 02:12
  • made some updates with untested BatchUpdate attempt, probably far from the truth, but should give the idea – Alex M May 07 '14 at 02:47