Solved

New to Access 2013 - Need help updating database using excel import

Posted on 2015-01-06
9
150 Views
Last Modified: 2015-01-19
I am trying to learn Access (newbie here), so please answer as if answering a newbie.
(Access Table with updates from Excel)

My current table has about 15 columns, my "key" is "APP ID", but when I want to update my database all the other columns could change.

Here are some of the columns I am using:
APP ID (Key)
Technician
Status
Group

Every day I need to update this table.  There are always completely APP ID's, but there are also changes to old ones.

For example:
App ID "1" may change it's status from open to closed, the group may change from Tier 1 to Tier 2.

How do I update the old record in a database?
What I would like to do is find the duplicates and rewrite the old data with the new data, and ultimately end up with one table with the most up to date data.

Thanks in advance for your help.
0
Comment
Question by:rnhturner1
  • 3
  • 3
  • 3
9 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 40533928
1. Import the excel file to a temp table  "tmpTable"
   here is the code to import the excel file

   docmd.transferspreadsheet acimport, , "tmpTable", <path and name of the excel file>, true

2. to update old records and add new records to your table, use an update query with a LEFT join

    Update yourTable Left join tmpTable On yourTable.[APP ID]=tmptable.[APP ID]
    Set yourtable.Technician=tmpTable.Technician,
           yourtable.Status=tmpTable.Status,
           yourtable.[group]=tmpTable.[group]

            other fields here
0
 

Author Comment

by:rnhturner1
ID: 40533944
New to ACCESS -  I am sure that is a perfect answer but I am not sure where to put that code?

If someone has time, I need a paint by numbers approach.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40533963
Do you update the data via other means once it is imported?  If you never update it after you import it, simply delete it and import a new version.  Compact after you do this to remove dead space.
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 40533984
correction in the update query

 Update tmpTable Left join yourTable On tmptable.[APP ID]=yourTable.[APP ID]
    Set yourtable.Technician=tmpTable.Technician,
           yourtable.Status=tmpTable.Status,
           yourtable.[group]=tmpTable.[group]
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:rnhturner1
ID: 40534034
Pat
"Do you update the data via other means once it is imported?  If you never update it after you import it, simply delete it and import a new version.  Compact after you do this to remove dead space."

It is a database that is over 2 years old and only the last two months is updated as the file is way too large to pull and import daily.  It is not too large for ACCESS, but the program that gathers the data can't handle the load to get the data all at once.
0
 
LVL 34

Accepted Solution

by:
PatHartman earned 500 total points
ID: 40534079
If it is two years old, how have you been updating it during that time?

When you can't replace the table, then you have to use update and append queries as Rey has suggested.  If you are not familiar with SQL, then you should use the QBE to build your queries.

..Add the destination table to the grid.
..Add the update table to the grid.
..Draw a join line between the unique ID.
..Change the join type to RIGHT.  Yes, Right.  That translates to select all rows from the update table and matching rows from the destination table.  This is a trick that will allow you to combine the add/update into a single query.  Due to the right join, the query will actually create records in the destination table when no match is found.
..Change the query type to Update
..Double click to select each column in the destination table (except the autonumber if you have one)
..If the update table has matching column names, Access will automatically populate the "Update To" line.  If the column names are different, you will have to manually enter the source column names.  Use the format: tblUpdate.ColName.  Use square brackets if you have used non-standard column or table names.
..Save the query.
..Run the update (you did back up first didn't you?)

Deletes are a different problem.  Let us know if you have to handle them.
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 40534207
;-)
0
 

Author Comment

by:rnhturner1
ID: 40544664
Rey,

Is there a way to just update all fields that are associated with that one APP ID?
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40544738
No.  You have to specify each column you want to update.  As long as the column names are the same, using the QBE as I described is pretty quick since you select all the fields by double clicking and Access will automatically map them.  It obviously gets more tedious if you have to map field by field because the names are different.
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

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

912 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

24 Experts available now in Live!

Get 1:1 Help Now