Access Replacing Table Info with right primary keys

Murray Brown
Murray Brown used Ask the Experts™
on
Hi

I have an Access table called "Manufacturers". It has an AutoNumber column called "ManufacturerPK" and a text column called "Manufacturer Name".  I am constantly being given new lists in Excel that must be used to replace the entire table in the order that it appears in Excel. This creates a nightmare because
the key values in the "ManufacturerPK" column are already being used all over the database so I need to make sure that the same "ManufacturerPK" matches each "Manufacturer Name". How do I reload the table with new information without losing these matches?
Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
President / Owner CARDA Consultants Inc.
Distinguished Expert 2018
Commented:
Do you have any unique identifier in the Excel file that could be used to append/update the record in Access?  Are the names 100% unique?

If you have any control over the Excel files, insist they add such a column of data then you can easily perform and update and append query whenever you wish.
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012
Commented:
I am constantly being given new lists in Excel that must be used to replace the entire table in the order that it appears in Excel.
What does this mean exactly?
That there is a key field in Excel that must be used?
The specific "Sort Order" must be maintained? (why?)

Can you simply ask them to just add the new records in a new sheet each time...?
Then you can just append the new records from Excel to Access with the import wizard

Ultimately you need to move this Operation into an Access table.
Else whatever workaround you have to do, ...you must do forever...

Can you explain a bit more about why this is being done in this way, and why it cannot be moved to Access?
Mark EdwardsChief Technology Officer
Commented:
Once you bring in your new manufacturer names to add to the table of manufacturers, your ties to the Excel file should end.  To expect the list in the table to have to change to the exact order of the Excel file is ridiculous.  I also ask the question - "why"?  It doesn't make sense.  
Once you get the new values added to the table, why care what the order is in the Excel file?

That said, if you still need to do that - for whatever reason, then you'll need a "sort order" field on your manufacturers table that you can use to indicate the order according to their order in the Excel file (don't use the table's autonumber field to sort by).  To do that, you can try to import the list into a separate, empty table with an autonumber field in Access and hope to get the same order as in the list.  (It usually works that way as long as your import process does not apply an import sort preference.)  Use this empty table ONLY for import.  Then add your new items to the manufacturers table, and then populate the sort order column with the numbers in the import table's autonumber field.  The sort order field numbers do not have to begin with 1 - they just need to provide the same order as the excel file when sorted, and an autonumber field is the easiest way to get that.  Note:  This solution assumes that the items in the list are unique so you can match them 1-to-1 between the import table and your manufacturers table.
Murray BrownASP.net/VSTO Developer

Author

Commented:
Thanks for the guidance. I will do everything mentioned. This does need to be handled in Access with only new values added from Excel and sorting can be done by adding an additional column

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial