I have imported some data from Excel. The table that I have imported (and there are more to come) has a similar structure like a DB : each table has as its first column a unique ID, like the customer table and the order table.
When I imported the data and I was asked how to deal with the primary key issue, I set Access to create and use its own unique primary key, as I want Access to handle this. That is why for example the customer table has two IDs .. OldID and KundenID (the primary key).
Obviously, the imported customer ID and the one from Access do not match. Therefore, I decided to create a SQL UPDATE query ... which would be called from a loop:
UpdateMe - is the procedure that loops through the customer table and
UpdateTable - is the procedure that updates the old customer id in the order table (tbl_test in this example) and replaces it with the actual Access primary key.
Sub UpdateTable(oldCustomerID As Integer, newCustomerID As Integer)
Dim dbs As Database
Dim qdf As QueryDef
Set dbs = CurrentDb
dbs.Execute "UPDATE tbl_test " _
& "SET KundenID = " & newCustomerID _
& "WHERE KundenID = " & oldCustomerID & ";"
If newCustomerID Mod 100 = 0 Then
Debug.Print newCustomerID & " has been updated."
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim oldCustomerID As Integer
Dim newCustomerID As Integer
Set db = CurrentDb
strSQL = "SELECT * FROM tblCustomers" '
Set rs = db.OpenRecordset(strSQL)
If Not rs.EOF Then rs.MoveFirst
Do While Not rs.EOF
oldCustomerID = rs.Fields("OldID")
newCustomerID = rs.Fields("KundenID")
Call UpdateTable(oldCustomerID, newCustomerID)
Set rs = Nothing
Set db = Nothing
When I run UpdateTable() from the debug / direct input window, then it does update the correct IDs.
However, when I loop - it updates the table with the wrong IDs!
Can anyone please advise me what I am doing wrong?
Thanks for your help