X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Retaining Autonumber When Creating A New Table, 2000    
post Oct 3 2016, 12:41 PM

Posts: 805
Joined: 21-September 04
From: Vancouver, Canada

I have three different Household tables. Each has a HouseholdID Autonumber field.

I want to merge all three of these into table with a HouseholdType field. My problem is for the first table I would like to retain the current HouseholdID as I don't want to have update all the other tables that are related to it via the HouseholdID. The 2nd and third table do not have many rows so I can fix the other tables manually. If I create the table originially as a number, I am not allowed to change it later to Autonumber after I have added data. If I create it with Autonumber right away, my original HouseholdID's will change.

Any solution?????
Go to the top of the page
post Oct 3 2016, 12:44 PM

Access Wiki and Forums Moderator
Posts: 71,196
Joined: 19-June 07
From: SunnySandyEggo


If you append the records from the first table including their ID numbers, then you should be okay.

Hope it helps...
Go to the top of the page
post Oct 3 2016, 12:47 PM

UtterAccess Editor
Posts: 17,902
Joined: 29-March 05
From: Wisconsin


Unfortunately, I think you'll need to update the other related tables as you move the household data from the "extra" tables into the correct one, especially if there's any overlap of HouseholdID values across the tables. The good news is, once you've got your household data consolidated into one table, you won't have to worry about this again.

Here's a basic VBA strategy I would use:
1. Create a Recordset of the first "extra" household table.
2. Loop through the records one at a time.
3. Note what the current/old HouseholdID value is.
4. Append the current household record to the one table you're consolidating to.
5. Note what the new HouseholdID value is for the record you just added is.
6. Run Update queries using the old and new HouseholdID values on each of the other related tables so the foreign key values are updated correctly.
7. End your loop here
8. Repeat 1-7 using the other "extra" household table.

Hope this helps,


EDIT: Here's a skeleton that should help you get started. You just need to add all of the fields for the tblHouseholds table and the extra household tables, and customize the update queries' SQL to use the correct foreign key field names.

Public Sub ConsolidateHouseholds()

    ConsolidateHouseholdData "tblHouseholds2"
    ConsolidateHouseholdData "tblHouseholds3"

End Sub

Public Sub ConsolidateHouseholdData(strTableName As String)

    Dim db As DAO.Database, rst As DAO.Recordset, rstH As DAO.Recordset, strSQL As String
    Set db = CurrentDb

'   Create a Recordset of the table we're reading from
    Set rst = db.OpenRecordset(strTableName)

'   Create a Recordset of the table we're writing to
    Set rstH = db.OpenRecordset("tblHouseholds")
    While Not rst.EOF
'       Add a new record of household data to the main table from the "extra" table
'       specified as the argument to this sub
        rstH!strHouseholdName = rst!strHouseholdName
'       Add more lines here for each field that appears in both the main table and the "extra" table
'       Update the related table data to swap out the old primary key value for
'       the new primary key value
        strSQL = "UPDATE tblHouseholdMembers SET lngHouseholdID = " & rstH!HouseHoldID & _
                    " WHERE lngHouseholdID=" & rst!HouseHoldID
        CurrentDb.Execute strSQL, dbFailOnError
        strSQL = "UPDATE tblHouseholdPhones SET lngHouseID = " & rstH!HouseHoldID & _
                    " WHERE lngHouseID=" & rst!HouseHoldID
        CurrentDb.Execute strSQL, dbFailOnError
'       Move on to the next household to be transferred to the main table
'   Cleanup
    Set rst = Nothing
    Set rstH = Nothing
    Set db = Nothing

End Sub

Hope this helps,

Go to the top of the page
post Oct 3 2016, 02:14 PM

UA Admin
Posts: 31,191
Joined: 20-June 02
From: Newcastle, WA

In addition to the excellent advice already given, make sure you do a Compact & Repair after appending records with existing AutoNumber values into the final destination table. This will reset the counter so that Access will not try to regenerate previously used Autonumbers.
Go to the top of the page
post Oct 3 2016, 03:00 PM

UtterAccess Editor
Posts: 17,902
Joined: 29-March 05
From: Wisconsin

And, of course, make a backup before doing anything this drastic, just in case.

Go to the top of the page
post Oct 4 2016, 12:26 PM

Posts: 805
Joined: 21-September 04
From: Vancouver, Canada

Thanks everyone for the great advice. I am off to see what I can do.
Go to the top of the page

Custom Search
RSSSearch   Top   Lo-Fi    11th December 2017 - 02:50 AM