Solved

Overwrite Access data from an Excel Spreadsheet

Posted on 2014-04-08
11
6,007 Views
Last Modified: 2014-04-14
We have an Access DB that houses imported data from a 3rd party vendor.  Unfortunately, some of the 3rd party data is incorrect.  Users have the correct data on an Excel spreadsheet.  

What we need is a method of overwriting the Access data with the data from the Excel spreadsheet.

The column headings are identical in the Access table and Excel spreadsheet.
We can't simply truncate and load the spreadsheet data because it's only some of the data that needs to be updated.

Walking through the scenario, the script or macro would have to compare the key field from the Excel file and match it with the key field in the Access DB.  If it finds it, overwrite the row data with the row data from Excel.  

It sounds simple but the only thing I've been able to find on Google is how to just import data and overwrite an entire Access table.  Not just specific rows of data.
0
Comment
Question by:kredeker
  • 4
  • 3
  • 2
  • +1
11 Comments
 
LVL 7

Expert Comment

by:COACHMAN99
ID: 39986272
You can create a 'Find Unmatched .' query; joining on the key, (to identify/list all keys that occur in the excel sheet, but not in the table).
Then create a second (append) query (joined on the key to the unmatched query) that will only append records that were found in the unmatched query.
0
 
LVL 19

Accepted Solution

by:
regmigrant earned 170 total points
ID: 39986286
I would approach it slightly differently - load the excel data into a new temporary table and generate a relationship between the two tables using the key; then you can join the tables and identify differences or walk the smaller table and check the larger - and potentially display in a form for confirmation before overwrite. this will limit the probability that the Excel data is actually wrong and you are overwriting good with bad. You could also generate an audit trail, offer 'undo' features and report the updates back to the vendor so they can fix their own data.
0
 
LVL 7

Assisted Solution

by:COACHMAN99
COACHMAN99 earned 165 total points
ID: 39986295
If you view the unmatched query before executing it you will see what will be appended.
Q1:
SELECT table2.ID, table2.vvv
FROM table2 LEFT JOIN Table1 ON table2.[ID] = Table1.[ID]
WHERE (((Table1.ID) Is Null));

Q2:
INSERT INTO Table1 ( ID, vvv )
SELECT [table2 Without Matching Table1].ID, [table2 Without Matching Table1].vvv
FROM [table2 Without Matching Table1];
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 35

Assisted Solution

by:PatHartman
PatHartman earned 165 total points
ID: 39986533
1. Import the spreadsheet.
2. Create a query that joins the spreadsheet to the table you want to update using the unique ID.  (no need to create a permanent relationship)  
3. Change the query to an Update query and pick the columns you want to update (not the PK) and enter the column names from the spreadsheet which will be the source of the data.
0
 
LVL 19

Expert Comment

by:regmigrant
ID: 39986635
no need for a permanent relationship agreed - but I suggest if this is a regular update you want to consider building a robust system rather than a one off you have to fettle each time.
0
 
LVL 7

Expert Comment

by:COACHMAN99
ID: 39986652
If you only want to append the ones you don't have in the original table then:
1. Import the spreadsheet into a table, ('table2')
2. create the 'Find unmatched' query (via the query wizard,)
3. create an append query based on joining table2 to your 'unmatched' query

qrytable2 Without Matching Table1:
SELECT table2.ID, table2.vvv
FROM table2 LEFT JOIN Table1 ON table2.[ID] = Table1.[ID]
WHERE (((Table1.ID) Is Null));

Q2:
INSERT INTO Table1 ( ID, vvv )
SELECT [qrytable2 Without Matching Table1].ID, [qrytable2 Without Matching Table1].vvv
FROM [qrytable2 Without Matching Table1];
0
 
LVL 35

Expert Comment

by:PatHartman
ID: 39986706
@coachman99
I think the OP wants to update existing data.

To add new rows, a simple append query would work.  It would raise errors about duplicates but those can be ignored.

Alternatively, a query with a right join will strangely enough both update existing rows and add new ones.

@regmigrant
You can't really have a permanent relationship with a table that is deleted and reimported or whose rows are deleted prior to each reimport.  Relationships are created to enforce RI and there is no RI to enforce in this particular case.  If you defined a relationship, you would have to drop it prior to the import/append and recreate it each time  so it doesn't actually do anything and therefore isn't necessary.
0
 
LVL 7

Expert Comment

by:COACHMAN99
ID: 39986718
got it - thanks :-) - Pat's reply is 100%
0
 

Author Comment

by:kredeker
ID: 39986729
Thanks for the reply all!  I will give give your solutions a try and let you know which one I went with then give points out appropriately.
0
 
LVL 19

Expert Comment

by:regmigrant
ID: 39988151
Pat - yes, my comment stands on its own :)
0
 

Author Comment

by:kredeker
ID: 39999959
I ended up doing the following:

1. Created a Saved Import that imports the Excel sheet into a table.
2. Created a SQL query that deletes matching records from the main table.  Created another query that appended the Excel table to the main table.

It seems to be working OK.  Thanks for the help with the solution!
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question