VBA problem relating to SQL linked table (1 Viewer)

[BusyBusyJr]

Background: I'm using an Access FE to manage data between two linked Excel data sets and a linked SQL table. The excel files = current master data, and the SQL table maintains current and historical data. Initially, I have queries created using the Access GUI to (1) check for changes between the excel files and the SQL table, if the data in the SQL table is different, the records are deleted. then (2) another append query inserts any record in the excel files not already in the SQL table. This approach takes a long time considering the excel data has about 25k records and the excel file has over 50k and growing. There are about 33 fields in both tables names the same. The SQL table has just one additional PK (ID) field. There is a unique (Index) field between all data sets which enable record comparison.

Solution being attempted is to use VBA to accomplish the data processing while displaying a status window showing the progress of the process.

This cost does not seem to want to talk to the SQL table and I get "run-time error '3622': You must use the dbSeeChanges option with OpenRecordset when accessing a SQL Server table that has an IDENTITY column."

ANY SUGGESTIONS WOULD BE HELPFUL ON WHAT I MAY BE DOING WRONG>

Private Sub UpdateOrAddRecords_Click() 'Called from the Main Menu of the Access FE

Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim fieldName As String
Dim fieldValue1 As Variant
Dim fieldValue2 As Variant
Dim addedCount As Long
Dim updatedCount As Long
Dim totalCount As Long
Dim cumulativeCount As Long

' Set your database file path
Set db = CurrentDb

' Open the recordsets for the two linked tables
Set rs1 = db.OpenRecordset("CombinedExcelData") 'this is a Query that unions two excel tables into one
Set rs2 = db.OpenRecordset("dbo_Daily Orders Booked - Combined", dbSeeChanges) 'this is the linked SQL table DEBUG Stops here!

' Initialize counts
addedCount = 0
updatedCount = 0
totalCount = rs1.RecordCount
cumulativeCount = 0

' Open status form
DoCmd.OpenForm "Processing Status", , , , , acDialog

' Loop through the records in the linked table
Do While Not rs1.EOF
' Find the corresponding record in the other linked table
rs2.FindFirst "ID = " & rs1!Index

' If the record is not found in the other linked table, add it
If rs2.NoMatch Then
rs2.AddNew
' Loop through each field in the record and copy the values
For Each fld In rs1.Fields
fieldName = fld.Name
fieldValue1 = fld.Value
rs2(fieldName) = fieldValue1
Next fld
rs2.Update
addedCount = addedCount + 1
cumulativeCount = cumulativeCount + 1
Debug.Print "Added new record for ID = " & rs1!Index
Else
' Record exists, check for differences and update if needed (similar to previous script)
For Each fld In rs1.Fields
fieldName = fld.Name
fieldValue1 = fld.Value
fieldValue2 = rs2(fieldName).Value

' Compare the values
If fieldValue1 <> fieldValue2 Then
' Update the value in the other linked table
rs2.Edit
rs2(fieldName) = fieldValue1
rs2.Update
updatedCount = updatedCount + 1
cumulativeCount = cumulativeCount + 1
Debug.Print "Updated " & fieldName & " for ID = " & rs1!Index
End If
Next fld
End If

' Move to the next record in the linked table
rs1.MoveNext

' Update the cumulative count on the status form
Forms("Processing Status").Controls("lblCumulativeCount").Caption = "Cumulative Records Processed: " & cumulativeCount

' Update the status form
DoCmd.RepaintObject acForm, "Processing Status"
Loop

' Close the status form
DoCmd.Close acForm, "Processing Status"

' Show a message with the counts
MsgBox addedCount & " records added and " & updatedCount & " records updated.", vbInformation

' Close the recordsets
rs1.Close
rs2.Close
Set rs1 = Nothing
Set rs2 = Nothing
Set db = Nothing
End Sub

 

[Minty]

Try specifying the type of recordset as well:

Set rs2 = db.OpenRecordset("dbo_Daily Orders Booked - Combined", dbOpenDynaset + dbSeeChanges)

 

[CJ_London]

Please post the code within code tags (the </> button) to preserve formatting

you are saying

if the data in the SQL table is different, the records are deleted. then (2) another append query inserts any record in the excel files not already in the SQL table.

but your code appears to be editing the data

' Compare the values
If fieldValue1 <> fieldValue2 Then
' Update the value in the other linked table
rs2.Edit
rs2(fieldName) = fieldValue1
rs2.Update

If you are concerned about performance - the VBA route is the slowest possible. So why not use sql to do your update/insert? You might want to look at an upsert query to do it in one pass.

I had a client who was typically updating sql server with around 1m rows a month. For that we stored a hash value (about 30 chars if I remember correctly) of all the fields in the sql server table and calculated it in the query from excel - it was then a simple left join between the calculated value from excel and the stored value in sql server to identify new and changed records. The process took around 20 minutes

 

[BusyBusyJr]

Yes that seemed to correct the problem: Changing the line to
< Set rs2 = db.OpenRecordset("dbo_Daily Orders Booked - Combined", dbOpenDynaset, dbSeeChanges)>

Now on to the next problem. Seems the line:
< rs2.FindFirst "ID = " & rs1!Index> 'does not match the records.
Tried variant of:
<rs2.FindFirst "Index = " & rs1!Index> 'since the matching fields in each are [INDEX] - Still no match (run-time error'3070': does not recognize 'A10020' as a valid field name or expression). 'A10020' is the first record in [INDEX] field of rs1. The matching record in rs2 is [Index] 'A10020' which is [ID] 25092.

 

[BusyBusyJr]

Please post the code within code tags (the </> button) to preserve formatting

you are saying


but your code appears to be editing the data



If you are concerned about performance - the VBA route is the slowest possible. So why not use sql to do your update/insert? You might want to look at an upsert query to do it in one pass.

I had a client who was typically updating sql server with around 1m rows a month. For that we stored a hash value (about 30 chars if I remember correctly) of all the fields in the sql server table and calculated it in the query from excel - it was then a simple left join between the calculated value from excel and the stored value in sql server to identify new and changed records. The process took around 20 minutes

The (1) and (2) under the background statement, is the current process being used, using several Access queries and a couple of pass-thru queries to SQL.
I would have though the the VBA approach is faster than the Access Query approach, but not as fast as a SQL approach. I may look into the SQL approach and then just call the actions from within Access using a pass-thru query?

 

[Gasman]

If you are trying to match A10020 you would need to surround it with single quotes.
I am suprised you did not get Type Mismatch?

 

[CJ_London]

Access query is sql. Slight variation to tSql which has more bells and whistles but just as fast, everything else being equal (such as network performance)