Import Excel Data to Access Table - Retain Primary Keys

chrisk1979

New Member
Joined
Aug 8, 2019
Messages
1
I am working on an inventory database in Access. I have a build of materials list in Excel. The part numbers have prefixes, bases and suffixes. Some of the part numbers are on the build of materials multiple times. Some of the part numbers change occasionally (suffix only; prefix and base will always stay the same). The changes happen often enough (not only part suffix changes, but parts being added, deleted or moved around) that I want to automate the import process of the parts on this build of materials on a daily basis to make sure I keep up with all changes automatically.

In Access, I am going to be keeping track of stock levels, invoicing, receiving new quantities, etc. - all kinds of activity which requires these parts to have a static primary key because I need to retain history and accurate stock levels of each part.

How can I import this data from Excel into Access on a regular basis and have Access:
1) Append any new parts (add to the end)
2) Ignore any existing parts that haven't changed at all
3) Replace or update part number (suffix) on those that have changed suffixes and retain its primary key, and therefore all its history, etc.

AND I also only want every part on the build of materials in my Access table only ONCE regardless of how many times it appears on the build of materials Excel file - I don't want more than one instance of a part number with a different primary key for each.

Please let me know if anyone can help or if any more clarification is needed.

Thanks!
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

123DavidRhodes

New Member
Joined
Aug 8, 2019
Messages
13
I am working on an inventory database in Access. I have a build of materials list in Excel. The part numbers have prefixes, bases and suffixes. Some of the part numbers are on the build of materials multiple times. Some of the part numbers change occasionally (suffix only; prefix and base will always stay the same). The changes happen often enough (not only part suffix changes, but parts being added, deleted or moved around) that I want to automate the import process of the parts on this build of materials on a daily basis to make sure I keep up with all changes automatically.

In Access, I am going to be keeping track of stock levels, invoicing, receiving new quantities, etc. - all kinds of activity which requires these parts to have a static primary key because I need to retain history and accurate stock levels of each part.

How can I import this data from Excel into Access on a regular basis and have Access:
1) Append any new parts (add to the end)
2) Ignore any existing parts that haven't changed at all
3) Replace or update part number (suffix) on those that have changed suffixes and retain its primary key, and therefore all its history, etc.

AND I also only want every part on the build of materials in my Access table only ONCE regardless of how many times it appears on the build of materials Excel file - I don't want more than one instance of a part number with a different primary key for each.

Please let me know if anyone can help or if any more clarification is needed.

Thanks!


Hello,
Based on the dynamic nature of the excel file, if it were me I would link the excel file in Access. Any changes to the excel file will be realized in real time in the access table. Go here to learn how to link your excel file.
https://support.office.com/en-gb/ar...workbook-a1952878-7c58-47b1-893d-e084913cc958

Search: Link to data in Excel on the web page
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,824
Office Version
  1. 2019
Platform
  1. Windows
If prefix + base doesn't change then just make that the primary key.

Although off the record I do wonder what problems may show up down the road if you are ignoring or otherwise updating suffixes. Don't they have some purpose?
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,127,167
Messages
5,623,130
Members
415,956
Latest member
Footballtend

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top