473,287 Members | 1,581 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,287 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 6212
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,554 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...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
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...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
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)...

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.