Ms Access, using Excel to modify tables; dealing with ID autonumber

Question

TLDR: I summarized the long question at the end of the question.

My business is using an Ms Access DB properly set with relationships, PKs, and FKs. I sometimes have to make major modifications to tables (adding fields, conducting column operations, you know, stuff that I believe only Excel can do: string operations (LEFT, RIGHT, etc.), INDEX/MATCH (VLOOKUPS), auto population of fields, etc...

What I do then is I export the tables to Excel to apply, conduct the modifications. My problem arises when I'm done and I want to import back the tables. I get a problem with IDs and the AutoNumber feature in Access. When I import the table, I cannot use the previous ID column and have to make a new one so that I can obtain a working AutoNumber ID column. The problem is that if there was any change in the order of the records, or in the number of records (i.e. if some records were deleted), then the new automatically generated IDs won't be the same as the old ones, creating problems with my relationships. The FKs won't point to the right PKs anymore.

I tried to search on ways to handle this problem, but didn't find anything, as if nobody else have this problem. I'm probably not doing it right I guess.

Short version: PK/FK problem when I export-import tables Access-Excel. During the import, I cannot re-use my initial PK column as AutoNumber, and thus I create a new ID autonumber column, leading to the problem that some IDs will change, creating problems with FKs (relationships).

Answer 1

Assuming that your addition of new fields is a one-off (or infrequent) operation, then create these fields manually in the existing table first. Import all your new data (username, role, other attributes) into a temporary table. Then use update queries to get the relevant data into the new columns in your original table, joining the two tables based on whatever matching you would have done in Excel. You could do all the string operations in your new data in Excel before importing if you're more comfortable with this, but your temporary new data table has to have one or two columns that can exactly match something in your existing table to be able to perform the updates.

Answer 2

PK/FK problems during synchronization normally solved by importing all required data from Excel to temporary table with not Autonumber ID column and then do 3 queries:

• Update records, where IDs are the same
• Delete rows in destination table if destination ID doesn't exist in source
• Insert rows to destination table if source ID doesn't exist in destination

If you are sure, that imported ID data is unique, you can insert IDs even in autonumber field using INSERT SQL, but this is not good solution. It would be better to use separate key column for Excel ID.

As of problem with multiple columns, you can consider using one table with Attribute-Value columns. In this case it doesn't matter how many attributes do you have.

Excel is good for collecting information from users and for primary data analysys, but main calculations it would be better to do in Access, it has very powerful tools. Using queries, forms and VBA may be implemented almost all, what can be done in Excel and much more