467,144 Members | 1,165 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,144 developers. It's quick & easy.

Append and Update an access table importing from excel

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
  • viewed: 5374
Share:
2 Replies
DonRayner
Expert 256MB
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 16PB
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.

Similar topics

1 post views Thread by DeSorrow Golden | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.