I am a fairly strong Access programmer. I have a question, however, which I do not know how to solve unless I do more programming than I would like to do. I have 7 .MDB files each with three tables that are identical in structure. The problem is that the original developer of these .MDB files wanted to have Referential Entegrity in it and he or she put in an AutoNumber key as the primary key. For instance, each table that is the parent table in each of the MDB files has a field called RECNUM, which is the primary key of the parent table and it is of type Auto-Increment. MS Access does support primary key/foreign key concepts, in a limited way, by using relationships.
The problem that I have is that each of the tables has a value of let's say 1 through a 1000 as the value in the field for RECNUM. They are also set to auto-number. How can I get around this problem? The problems with this database are the following:
A. once I take the auto-number feature out, I cannot put it back for the
B. I can add a value of let's say 10,000, 20000, 30000, etc to each auto-
number but I thought the idea of an auto-number is that it be sequential
so this may be a problem.
Could someone please give me some ideas where I can append the tables together? I know ADO and VBA, but I really want a simple solution.
You can get around the AutoNumber problem by changing all the AutoNumber fields to Long. Then modify the RECNUM field so that you now have unique records for all of the tables (I would do something like adding 10,000 to the RECNUM for one table 20,000 to the RECNUM of the second table, etc.). Then combine them into one table, make sure the RECNUM is unique by trying to make it the Primary Key. Then when you are satisfied that the RECNUM is unique, copy the combined table, Structure Only (not the data), change the RECNUM field of the structure table to AutoNumber. Then Append all of your records from the one table to the new table.
As far as gaps goes, you can have gaps in your AutoNumber field.