appending data to an existing table: renumber key

[Leathem]

I need to update a table that exists in Access with new data. The easiest way for me to do it is to run a deletion query that removes all the data from the existing table and then to repopulate the table from an Excel table. This works fine except for one thing: the table in Access has a numerical key in the first column (which is lacking in the Excel table). When I append the Excel data to the blank Access table, Access inserts a key. That's fine, except that each time I do this process the number of the first key entry is the last key value from the old table plus 1 and the key is autonumbered from there. I'm surprised that Access remembers the old value, but in any case, I'd like the new key to start at 1 each time I do the operation, or after a few iterations the key numbers will be quite large! How do I get Access to "forget" the last value in the old table?

 

[plog]

Do you use that key for anything? Sounds like you don't--why not just delete that field?

If however you want that field, I believe compacting and repairing the database after truncating the table will reset the autonumber in it.

 

[boblarson]

If this "key" you have is not being used as a foreign key anywhere then, as plog says, "why not just delete it?" It isn't like it is going to be keeping your Excel data in any particular order as the import isn't necessarily going to do that so it isn't like it is important for that. If you use it for numbering to keep a specific order of the spreadsheet then you would need to add the number to the spreadsheet BEFORE importing it. But if it isn't being used then it is just fluff.

 

[bijuvijayaraj]

you can remove the auto number field if it is of no use, but for the sake of a solution to this issue I suggest you to create an update query to reset the autonumber field. You can set the field value to the number you wish to start with. Make sure that you have deleted all records from the table before running the query.

 

[boblarson]

but for the sake of a solution to this issue I suggest you to create an update query to reset the autonumber field.

But my question is WHY as it has no function or use. And it won't necessarily number the records in the order of the spreadsheet. So, it is extra work for no gain. Just delete the field.

 

[Leathem]

I apologize for not being clearer. The key is indeed used, as a foreign key in other tables, so I'd like to keep it. I don't, however, care if the numbering is the same as it is in the spreadsheet. bijuvijayaraj's idea of using an update query to reset the numbers sounds appealing, but I'm not sure how to set up such a query.

 

[boblarson]

I apologize for not being clearer. The key is indeed used, as a foreign key in other tables, so I'd like to keep it. I don't, however, care if the numbering is the same as it is in the spreadsheet. bijuvijayaraj's idea of using an update query to reset the numbers sounds appealing, but I'm not sure how to set up such a query.

Well, first - you say it is used as a foreign key in other tables. So when you are importing this spreadsheet data are you clearing out the existing data in the other tables too? How do you intend to keep the foreign keys matching to the right records if you are clearing this first table out and reimporting the data? Something isn't going to be working right on this I fear.

 

[bijuvijayaraj]

here is the tip to create an
update query.
You seems to be familiar with delete and append queries. To create an update query, create a new query, select the query type as update from the menu, include the table to be updated to the grid and drag and drop the autonumber field to the first coloumn now in the "update to" row of the same column enter the desired starting number, save and be sure that you are running this only after the delete query.

 

[Leathem]

Thanks for the info. I think this will do the trick!

 

[Leathem]

I've been away for a while, but have returned, and I tried the update query that bijuvijayaraj suggested, but got an error message that the field could not be updated. I guess this is not unexpected, since it's an autonumber field. I suppose that as long as Access doesn't run out of numbers I'll just leave it alone.

To answer boblarson, yes, I do clear out all the other tables that use the key in my imported table as a foreign key.

But suddenly I'm getting a new problem. When I try to append the data I'm getting an error message that says "Field 'F12' doesn't exist in the destination table". But there isn't any field F12 in the table I'm trying to import.

 

[Leathem]

Oops. My mistake. I got rid of the spurious F12 by just deleting the rightmost column in the Excel spreadsheet (even though to the naked eye it was blank). Sorry.