473,322 Members | 1,566 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

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
2 6215
DonRayner
489 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
32,556 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

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

Similar topics

1
by: DeSorrow Golden | last post by:
How would I go about creating a macro to update an Oracle table using Excel?
4
by: Martin | last post by:
There is an Access table on the network. 15 users who do not have Access are connected to the network. Is there a way for each user to be able to enter one or more rows containing 3 or 4 columns to...
7
by: Keon | last post by:
Hoi, I'm using a database with alot of records in 1 table (more than 3000). If i want to export this table to excel i only get it till record 2385. Do someone know how i can solve this...
1
by: Pierre Maricq | last post by:
Hi, I am using Win2000 and Access2000. I need to build build a macro or write a VBA in Access that would screen all files contained in a directory on my C drive (files are structrured DAT...
1
by: john_liu | last post by:
How can I update an Access table based on a sheet in Excel by VBA in excel. Thanks
3
by: bwhite | last post by:
I have a temp table with one row of data that I need to export into Excel. I created the export to create the xls file as follows ... Dim FileName FileName = !! DoCmd.SetWarnings False...
1
by: cmcl95 | last post by:
HI Folks, I need some help I'm trying to develop a automated way to import from a excel spsh excluding the 1st 2 rows & two out of 41 columns. I got the 1st part with range see below ...
1
by: Esmi | last post by:
I am developing an application in Ms Excel 2003 that has a feature to update records in Ms Access. The problem is the table name I am trying to update contains a space and whenever I run the below...
3
by: sarah2855 | last post by:
Hello All, I'm looking for the vb code that export an access table to specific worksheet in Excel. I tried to search see if this question was answered before here, but didn't find anything that...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.