Solved

Overwrite Access data from an Excel Spreadsheet

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

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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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 34

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

910 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

19 Experts available now in Live!

Get 1:1 Help Now