• Status: Solved
  • Priority: High
  • Security: Private
  • Views: 46
  • Last Modified:

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
0
Massimo Scola
Asked:
Massimo Scola
  • 2
1 Solution
 
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
 
Massimo ScolaAuthor 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 ScolaAuthor Commented:
Best solution - works like a charm.
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now