VBA SQL Update procedure updating with wrong data

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 & ";"
         
    dbs.Close

If newCustomerID Mod 100 = 0 Then
    Debug.Print newCustomerID & " has been updated."
End If
    
End Sub

Sub UpdateMe()
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)
  

  rs.MoveNext
Loop
 
rs.Close
Set rs = Nothing
Set db = Nothing
End Sub

Open in new window


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

Massimo
Database1.accdb
Massimo ScolaInternshipAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Instead of looping, just use this SQL to update tblOrders to the new ID value for tblCustomers:

UPDATE tblCustomers INNER JOIN tblOrders ON tblCustomers.OldID = tblOrders.KundenID SET tblOrders.KundenID = [tblCustomers].[KundenID];

Before updating data in this fashion, however, please be sure to make a backup, just in case something goes wrong.
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Massimo ScolaInternshipAuthor Commented:
OK that works! Thanks .. yet I still wonder why the loop doesn't work?
I thought that I would give it a try before asking for help.
0
Massimo ScolaInternshipAuthor Commented:
Best solution - works like a charm.
Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.