Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 163
  • Last Modified:

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

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
rnhturner1
Asked:
rnhturner1
  • 3
  • 3
  • 3
1 Solution
 
Rey Obrero (Capricorn1)Commented:
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
 
rnhturner1Author Commented:
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
 
PatHartmanCommented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Rey Obrero (Capricorn1)Commented:
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
 
rnhturner1Author Commented:
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
 
PatHartmanCommented:
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
 
Rey Obrero (Capricorn1)Commented:
;-)
0
 
rnhturner1Author Commented:
Rey,

Is there a way to just update all fields that are associated with that one APP ID?
0
 
PatHartmanCommented:
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

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

  • 3
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now