Starting Over

[Lori]

Hello, I hope someone can give me a little direction. I built a database,
which has actually turned out to be very large. I have learned along the way
that I made some errors. For example, in one very large table I did not use
auto-number in the key field and I really should have. Is there a way to
start over on a clean database form and copy over the table and change it to
auto-number and import the data? Or is there a way to fix this on the current
database? Any suggestions would be great.

 

[Allen Browne]

You can use an Append query to import data into an AutoNumber table.

1. Firstly, if this is Access 2000 or later, ensure that the Name
AutoCorrect boxes are unchecked under:
Tools | Options | General.

2. On the Tables tab of the Database window, select your table and press
Ctrl+C (to copy) and Ctrl+V (to paste). Access pops up a dialog where you
will choose "Structure Only", and supply a name for the table.

3. Open the table in design view. Replace the Number field with an
AutoNumber field. Save. Close.

4. Create a query into the old table. In query design view, choose Append on
Query menu. Tell Access to append to the new table, created at step 2. Drag
all fields from the original table into the grid. If the AutoNumber field
has the same name as the previous number field, it should map automatically;
otherwise you can specify the name of the matching field in the new table.
Run the query.

5. Verify that the new table has the correct data. Remove any relationships
the old table was involved in (Relationships on Tools menu), and then delete
it.

6. Compact the database:
Tools | Database Utilities | Compact

7. Rename the new table with the same name as the old one if desired.

8. Recreate any relationships you deleted at step 5.