By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,961 Members | 2,492 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,961 IT Pros & Developers. It's quick & easy.

Append and Update an access table importing from excel

P: 77
Please advice me on this.
I need to create a new access database that will used to collect the employee hours from various locations. Every friday supervisors from various locations will be sending a spreadsheet that has the following fields;Agency ID,Name,In Date,Out Date, Feb1 week,Feb2 week..until Jun5 week.Data from these spreadsheets needs to be imported and appended/updated to a master table in the access database.You have to keep in mind when the spreadsheets that come with feb2 week data can have updated or new data in any of the columns until 'Feb2 week'. I know how to import and load data into an access table from a spreadsheet. By issue here is how to update/append all the rows/columns in the table every week.

PLease help.

Thanks in advance
Feb 7 '09 #1
Share this Question
Share on Google+
2 Replies


DonRayner
Expert 100+
P: 489
How about importing your spreadsheet to a temp table and then using an update query to update/append the main table. Here is a SQL example that would Update/Append From Table1a to Table1b.

Expand|Select|Wrap|Line Numbers
  1. sqlString = "UPDATE Table1b " & _
  2. "RIGHT JOIN Table1a ON Table1b.Field1 = Table1a.Field1 " & _
  3. "SET Table1b.Field1 = [Table1a]![Field1], Table1b.Field2 = [Table1a]![Field2], Table1b.Field3 = [Table1a]![Field3];"
  4.  
  5. DoCmd.RunSQL sqlString
Feb 7 '09 #2

NeoPa
Expert Mod 15k+
P: 31,492
It's not a design I'd advise, but if that's what you have to work with then the concept would be to :
  1. Import data into an intermediate table (can be created on the fly if preferred, or re-use the same one over again).
  2. Run an update query on your main table with an INNER JOIN betwen matching records (You don't indicate what a match is so you'll have to manage those details yourself).
  3. Run an append query where the key of the intermediate table is LEFT JOINed to that of the main table, and the WHERE clause only allows those records with a Null key in the main table to be processed.
  4. Clear down the intermediate table for next time. This will mean either clearing all the records or deleting the table, depending on your choice in step 1 above.
Feb 8 '09 #3

Post your reply

Sign in to post your reply or Sign up for a free account.