Solved

Overwrite Access data from an Excel Spreadsheet

Posted on 2014-04-08
11
5,647 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 165 total points
Comment Utility
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
Comment Utility
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 7

Expert Comment

by:COACHMAN99
Comment Utility
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 34

Expert Comment

by:PatHartman
Comment Utility
@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
Comment Utility
got it - thanks :-) - Pat's reply is 100%
0
 

Author Comment

by:kredeker
Comment Utility
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
Comment Utility
Pat - yes, my comment stands on its own :)
0
 

Author Comment

by:kredeker
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

763 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now