Scenario

Sometimes you have to overhaul your table to such an extent exporting to, say Excel--and reimporting back into Access once you've "cleaned it up"--becomes the necessary thing to do. Trouble is, Access doesn't like for the user to import data with pre-existing autoid values; it likes to assign new ones at the time of importing.

I had two instances of this, one in February 2001 and the other in September 2002. In the 1st incident, I had a table named "front_end" with a field called "project managers." This field linked to another table via an "autoid" type of value; this other table had a list of project managers as well as other fields which had nothing to do with project managers. I came to see that I would need a table for project managers all by itself, and so I wanted to create such a table with the original autoid values intact. This was necessary because the "front_end" linked to the project managers through this "autoid" value, and if they were to reset themselves in the typical "1.2.3.4.5.6" pattern throughout (gaps in the autoid numbers existed in the current layout), these links would be ruined.

The 2nd scenario--which I will use in the steps outlining my solution--was I had a table named "vendor_quotes," and this linked to a "vendors" table via an "autoid" value. The "vendors" table was highly "cleaned up," and doing this required exporting it to Excel 1st and then reimporting it again. Once again, if I were to do this in a "normal" manner, the original sequence of autoid values (which was something like "2.3.4.6.7.9.12.13.14") would be lost and reset back to "1.2.3.4.5.6." Again, this would create a problem since "vendor_quotes" was linked to "vendors" (the table being overhauled) via these values.

Common Restructuring Ideas that Don't Work, and Why

One common way a user may try & overcome this is to designate the "autoid" field's type in the new "target" table as being a "number" type rather than a "autonumber" type, because if a user attempts to copy & paste values into an "autonumber" type of field, Access won't allow this. As anyone knows, Access doesn't allow the values of an "autonumber" type of field to be changed; therefore, it stands to reason, why not just change the "autoid" field's type to a standard "numeric field", so its values can be changed (that is, so the user can paste the original values into the field)? Then, after the values have been pasted with the original values intact, just change the "autoid" field back to its original "autonumber" type.

However, that doesn't work because Access doesn't allow a user to change a "numeric" type of field to an "autonumber" type of field if there are already values present, as would be the case once the user has pasted. (The same thing probably holds for changing a "text" type of field to an "autonumber" type of field as well.)

Another way would be to leave the "target" table's structure with the "autoid" field having the normal "autonumber" type attributes, and copy/paste the data without the original autoid values. Doing this, however, results in the autoid sequence resetting itself back to "1.2.3.4.5" etc throughout the entire table.

How to Fix this

The way to fix this is to use an append query--a query that allows the user to add records from one table to another. Let me use the 2nd scenario as a case study to better explain this.

I have this table named "tbl_vendors" which I have exported to Excel and cleaned up. The Excel file has column headings which double as field names.The original table is still in Access. Here is what I would do:

In summary, what we are going to do is establish a "source" table--with the data now "cleaned up" and the autoid values intact--and also establish a "target" table which will be the table ultimately used. The "source" table will have its autoid field set as a regular numeric field, even though its name is still "autoid" and the values are intact. Ultimately, the "target" table will have these same values intact with the "autoid" field set as a normal "autonumber" type in its attributes, which is ultimately what we want to have happen.

Here are the steps:

Note: I have also found one can do this by copying/pasting the tables, and saying "structure only." This works if you're creating a new blank copy of the database and want to give the new user "fresh" autoids so that their intial entries don't start out with large autoid numbers.