Correcting Primary Key Fields

[Adrianna]

Okay, my inital dataset really should have been scrubbed, but the system is up and running and now I've got to go back and fix some serious issues.

I have a locationID in the TblLocation that is passed to the same field in all of my TblITAssets which of course have their own primary keys.

The issue is that some of the "locations" have been duplicated due to entry errors so there are a few entries for the same location that had been assigned different locationIDs passed as the primary key linking all 30 different TblITAssets. Now there are tousands of records and I have to merge these "locations" together across all of the tables. The problem is HOW do I change the duplicate "locationID"s to be the same when the primary key prevents it. It had to be changed in all subsequent tables as well.

I was thinking of doing a form (for reuse if we find more errors later) to allow my team to select the table where the error exists, enter the "LocationID" to search for, and then enter the replacement "LocationID". This issue would be ensuring that my team would have to do this for every table that the incorrect "locationID" exists in prior to deleting the record from the primary table (to prevent orphan records).

Please help!

 

[FoFa]

Ok, it is always better to prevent rather than fix. But not always possible. If you think this could be an issue going forward (no way to prevent without rewrite etc) than maybe you could create a form that has pull downs for Bad location, replace with Location and it runs a macro of (how ever many) queries you need to update the tables and then delete the offending location.
Just my 2 cents.

 

[Adrianna]

New Approach

Okay, I made a form for users to select from a textbox the LocationID to be replace (held in a IDToReplace variable) and also a text box for the LocationID that the incorrect one is being replaced by (held in a IDReplacement).

I just validated that entries were provided for the find and the replace and then ran an update query.

The issue is that I have 32 tables and I would like this to run across all of them. So, I need to loop through all 32 tables performing the following:
Check If (IDToReplace)exist in the table Then (if it does)
Run the update query for the specific table
Else
Go to the Next Table

I assume that I would use a loop, but how do I loop through tables. I can create a table or specify all 30 names for it to run on. I just need to know which method is recommended and how to "go to the next table"

 

[FoFa]

You can use vba to handle it, you can loop through the table collection and dynamically generate the SQL for each table affected, OR (I like the simple approach) I would an create an update query for each table (taking the information from the form) and use a macro could be controlled by a condition and dlookup (doesn't have to, if the query does not find any rows it won't update). Then on some button that says GO FOR IT on your form, have it run the Macro. I just find later, maintaining this is easier than 3 or 4 routines in VBA (it never turns out to be 1). Don't get me wrong I love VB and VBA, it is just maintenance is easier some times NOT using it (IMHO).

 

[Adrianna]

Okay...here is the mess

OKay this is what I have so far, but I'm going nuts debugging. The idea was as described above to loop through the tables finding records where the LocationID field matches ID2Replace, replacing it with ID4Replace and repeating this through all of the tables in tDataTableNames.

If IsNull(ID2Replace) Then
MsgBox "You must select the LocationID to be replaced", vbExclamation, "Missing Entry"
Else
If IsNull(ID4Replace) Then
MsgBox "You must select the correct LocationID", vbExclamation, "Missing Entry"
Else

Dim dbITArchitectureData As DAO.Database
Dim rst As DAO.Recordset
Dim strCritera As String, strCritera2 As String
Dim rst1 As DAO.Recordset
Set dbITArchitectureData = CurrentDb
Dim TblNm As String
Dim FinalNo As Integer
Dim SQL As String
FinalNo = 1

Do Until FinalNo = 34
DoCmd.OpenQuery "qSelectTableName"
TblNm = TableName <<----------Can't assign query value to variable

Set rst = dbITArchitectureData.OpenRecordset((TblNm), dbOpenDynaset)
Do Until rst.EOF
strCritera = (ID2Replace)
rst1.FindFirst rst1!LocationID = strCritera
Do Until rst1.NoMatch
If rst1!LocationID = strCritera Then
SQL = "UPDATE TableName " & _
"SET TableName.LocationID = (ID4Replace)" & _
"WHERE TableName.LocationID = (ID2Replace)"
DoCmd.RunSQL SQL
Else
End If
rst1.FindNext rst1!LocationID = strCritera
Loop
rst.MoveNext
Loop
rst.Close
rst1.Close
FinalNo = FInalNo + 1
Loop
End If
End If