Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Overwrite Access data from an Excel Spreadsheet

Posted on 2014-04-08
11
Medium Priority
?
6,914 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 510 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 495 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 39

Assisted Solution

by:PatHartman
PatHartman earned 495 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 39

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

604 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