I need to update several millions records in SQL Server from MS ACCESS table. I use MS ACCESS as a frontend and SQL as linked tables. So as I understand I need to create pass-through query to execute something like this:
UPDATE SQLtbl SET SQLtbl.col1 = MDBtbl.Col1 FROM SQLtbl INNER JOIN MDBtbl ON SQLtbl.ID = MDBtbl.ID WHERE SQLtbl.col1 != MDBtbl.Col1
It works extremely slow so I need to convert int somehow to BULK UPDATE Please advice, Thank you
Similar to a recent related question here, this is another one of those cases where treating ODBC linked tables just like native Access tables can result in slow performance.
For two identical Access tables named [SQLtbl] (ODBC linked to SQL Server) and [MDBtbl] (native Access) with 9999 rows each, the following code took around 5.5 minutes to execute:
Sub UpdateViaJoin()
Dim con As ADODB.Connection
Dim t0 As Single
Set con = CurrentProject.Connection
con.CommandTimeout = 0
t0 = Timer
con.Execute _
"UPDATE " & _
"SQLtbl INNER JOIN MDBtbl " & _
"ON SQLtbl.ID = MDBtbl.ID " & _
"SET SQLtbl.Col1 = MDBtbl.Col1"
Debug.Print Format(Timer - t0, "0.0") & " seconds"
Set con = Nothing
End Sub
To see if the JOIN itself was a problem, I ran the following which took just over 5 minutes to complete:
Sub UbdateViaDLookup()
Dim cdb As DAO.Database
Dim t0 As Single
Set cdb = CurrentDb
t0 = Timer
cdb.Execute _
"UPDATE SQLtbl SET Col1 = DLookup(""Col1"", ""MDBtbl"", ""ID="" & ID)"
Debug.Print Format(Timer - t0, "0.0") & " seconds"
Set cdb = Nothing
End Sub
On the other hand, the following code that uses a pass-through query and a native T-SQL prepared statement consistently ran in under 2 seconds (that is, more than 100 times faster):
Sub UpdateViaPassThroughQuery()
Dim cdb As DAO.Database, rst As DAO.Recordset, qdf As DAO.QueryDef
Dim SQL As String, statementHandle As Long, i As Long, updateList As String
Dim t0 As Single
Set cdb = CurrentDb
t0 = Timer
SQL = "SET NOCOUNT ON;"
SQL = SQL & "DECLARE @statementHandle int;"
SQL = SQL & "EXEC sp_prepare @statementHandle OUTPUT, N'@P1 nvarchar(50), @P2 int', N'UPDATE SQLtbl SET Col1=@P1 WHERE ID=@P2';"
SQL = SQL & "SELECT @statementHandle;"
Set qdf = cdb.CreateQueryDef("")
qdf.Connect = cdb.TableDefs("SQLtbl").Connect
qdf.SQL = SQL
qdf.ReturnsRecords = True
Set rst = qdf.OpenRecordset(dbOpenSnapshot)
statementHandle = rst(0).Value
rst.Close
Set rst = cdb.OpenRecordset("SELECT ID, Col1 FROM MDBtbl", dbOpenSnapshot)
i = 0
updateList = ""
Do Until rst.EOF
i = i + 1
updateList = updateList & "EXEC sp_execute " & statementHandle & ", N'" & Replace(rst!Col1, "'", "''") & "', " & rst!id & ";"
If i = 1000 Then
qdf.SQL = updateList
qdf.ReturnsRecords = False
qdf.Execute
i = 0
updateList = ""
End If
rst.MoveNext
Loop
If i > 0 Then
qdf.SQL = updateList
qdf.ReturnsRecords = False
qdf.Execute
End If
rst.Close
Set rst = Nothing
qdf.SQL = "EXEC sp_unprepare " & statementHandle & ";"
qdf.ReturnsRecords = False
qdf.Execute
Set qdf = Nothing
Debug.Print Format(Timer - t0, "0.0") & " seconds"
Set cdb = Nothing
End Sub
Edit
To tweak the above code to handle Nulls you would need to update the line ...
updateList = updateList & "EXEC sp_execute " & statementHandle & ", N'" & Replace(rst!Col1, "'", "''") & "', " & rst!id & ";"
... to ...
updateList = updateList & "EXEC sp_execute " & statementHandle & ", " & _
FormatArgForPrepStmt(rst!Col1) & ", " & _
rst!id & ";"
... and add a little formatting function something like this:
Private Function FormatArgForPrepStmt(item As Variant) As String
If IsNull(item) Then
FormatArgForPrepStmt = "NULL"
Else
Select Case VarType(item)
Case vbString
FormatArgForPrepStmt = "N'" & Replace(item, "'", "''") & "'"
Case vbDate
FormatArgForPrepStmt = "N'" & Format(item, "yyyy-mm-dd Hh:Nn:Ss") & "'"
Case Else
FormatArgForPrepStmt = CStr(item)
End Select
End If
End Function
The poster clearly asks:
“I understand I need to crate pass-though queries”.
So we need to address how to create and run a pass-though query.
The simple solution is to thus save your query as pass-though. In fact, you can “change” your existing query to pass-though.
Bring up your existing query in design view, and hit the pass-thought button.
Eg this:
If you save your query as pass-though, then you running server side t-sql.
Note that the pass-though button is hit, then this also brings up the property sheet. I circled the pass-though button and the ODBC setting required in the property sheet. In fact you can COPY the odbc connection string from a linked table via cut + paste.
Thus: There is no need to setup connection strings in code There is NO need to write VBA code. There is in fact LITTLE IF ANY need to even re-write the sql, it should work as you have as native t-sql code.
One thus achieves rather desirable goals here:
You can re-use the EXISTING query.
You do not have to RE-WRITE the query.
You don’t have to adopt and write a WHOLE bunch of code.
You don’t even have to know VBA
You don’t introduce a WHOLE NEW ADO object model library into an existing application. It is un-likely that the existing application was using the ADO object library. You now have to introduce this WHOLE new object library into your existing application. Adding this new reference and VBA code will thus create significant potential to break and introduce bugs into the existing code base, this is EXPECIALLY the case if existing code did not qualify DAO recordsets.
I think to run one pass-though update, it far more simple and risk free to simply use ONE pass-though query and simply click on the ribbon then that of introducting whole new ADO reference, and object libriares and THEN introduce a bunch of VBA ADO code to simply run a simple update query.
