Updating the Access Table with a excel spread sheet

Stildawn

Board Regular
Joined
Aug 26, 2012
Messages
197
Hi All

I am just starting out on a new Access project.

Basically what I have in Access is a table which lists jobs via their HB Number (as well as a ID number access gives them). For each job there is a bunch of details and some Yes/No fields.

Each day I will get a dump from another system that will list jobs like this, I then need to "Update" the Access table with any new information from the excel dump. The update would need to:

1. Insert new records (jobs) from Excel dump into Access Table
2. Update any of the records fields (except HB of course as its unique) in the access table from the Excel dump (the Excel dump obviously wont have access ID numbers, but will have the HB)

For a real basic example:

Access Table Like So:

IDHBFinished
15AYes
25BNo
35CNo
45DNo

<tbody>
</tbody>


Excel Dump looks like this:

HBFinished
5ANo
5BYes
5CYes
5DYes
5ENo

<tbody>
</tbody>

So some Yes/No's are different and there is a new HB called "5E"

After the "update" Access should look like this:

IDHBFinished
15ANo
25BYes
35CYes
45DYes
55ENo

<tbody>
</tbody>


This example is overly simple, the actually data has lots of different fields and many many many more records. But yeah basically need to update the table from an excel dump.

Any ideas on how to do this? I thought it was as simple as doing a Excel Import > Append Table thing in Access, but that just seems to add the new records and ignore the updated fields?

Thanks in advance

Cheers

Also posted here: Updating the Access Table with a excel spread sheet
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
In an update to this, ideally each time I do this update I would like it to also use the following code:

Code:
Function LogChanges(lngID As Long, Optional strField As String = "")    
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim varOld As Variant
    Dim varNew As Variant
    Dim strFormName As String
    Dim strControlName As String
    
    varOld = Screen.ActiveControl.OldValue
    varNew = Screen.ActiveControl.Value
    strFormName = Screen.ActiveForm.Name
    strControlName = Screen.ActiveControl.Name
    Set dbs = CurrentDb()
    Set rst = dbs.TableDefs("ztblDataChanges").OpenRecordset
    
    With rst
        .AddNew
        !FormName = strFormName
        !ControlName = strControlName
        If strField = "" Then
            !FieldName = strControlName
        Else
            !FieldName = strField
        End If
        !RecordID = lngID
        !UserName = Environ("username")
        If Not IsNull(varOld) Then
            !OldValue = CStr(varOld)
        End If
        !NewValue = CStr(varNew)
        .Update
    End With
    'clean up
    rst.Close
    Set rst = Nothing
    dbs.Close
    Set dbs = Nothing
End Function

This code works currently on forms, so that when you update a field it records each change in a separate "DataChanges" table.

Ideally the daily updates from Excel would also populate the "DataChanges" table using this code? So perhaps is there away to do it through a form? Or code using this code?

Thanks, Access is not my strong suit.
 
Upvote 0
What version of Access are you using?
You could set up an import spec if you're importing the same file each time - I'd import to a temporary table and then use an UPDATE query to push the new data from the temporary table out to your other tables.

I'd also lose the access autonumber that it creates - it only adds this as a suggestion as it doesn't know that your HB value is unique and a good candidate for the primary key.

You could also execute a query that checks the value of the existing 'Finished' field against the one in the temporary table - any differences are recorded in the change table.

So your first query would be to spot any differences and put that in the changes table:
Code:
INSERT INTO tbl_Changes ( HB, Finished )
SELECT         tbl_Main.HB, tbl_Main.Finished
FROM         tbl_Main RIGHT JOIN tbl_Import_TEMP ON tbl_Main.HB = tbl_Import_TEMP.HB
WHERE         tbl_Main.Finished<>tbl_Import_TEMP.Finished

Your second query updates the main data table with the new data:
Code:
UPDATE         tbl_Main LEFT JOIN tbl_Import_TEMP ON tbl_Main.HB = tbl_Import_TEMP.HB 
            SET tbl_Main.Finished = tbl_Import_TEMP.Finished
WHERE         tbl_Import_TEMP.Finished<>tbl_Main.Finished

Your final query adds new data to the main data table:
Code:
INSERT INTO tbl_Main ( HB, Finished )
SELECT         tbl_Import_TEMP.HB, tbl_Import_TEMP.Finished
FROM         tbl_Import_TEMP

As your HB field is set as a primary key (no duplicates) it will only add those records that don't already exist.
Also use DoCmd.SetWarnings False in your code to stop the queries reporting that they can't update/insert all the information.

Sorry, hope you can get the info out of the post - bit tied in at work at the moment. :)
 
Upvote 0
I'm using Access 2010

A temporary "Update" table sounds like a good idea. How do you build a Update Query off a temporary table? Sorry my access certainly isn't the greatest.

The example tables above with the Finished column is just an example though, when I build this there will be many many columns, like Vessel, Port, ETA, and then a bunch of Yes/No fields for different tasks to perform.

Your code snippets I cant make much sense of sorry :)
 
Upvote 0
Hi Darren, I have been playing with your code all morning trying to figure it out, and I think I have it lol, well mostly.

Using a VBA procedure like this example:

Code:
Sub test()

Dim strSQL As String
strSQL = "UPDATE tbl_Main LEFT JOIN tbl_Import_TEMP ON tbl_Main.HB = tbl_Import_TEMP.HB SET tbl_Main.Status = tbl_Import_TEMP.Status WHERE tbl_Import_TEMP.Status<>tbl_Main.Status"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True


strSQL = "INSERT INTO tbl_Main ( HB, Status, ETA) SELECT tbl_Import_TEMP.HB, tbl_Import_TEMP.Status, tbl_Import_TEMP.ETA FROM tbl_Import_TEMP"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True


End Sub

I think I have successfully implemented your last two codes???

Just want to check one thing, the tbl_Import_TEMP (coming from Excel via other system) will only have update to say 5 fields. Where as the tbl_Main will have these 5 fields, as well as about 5 more Y/N tick box fields.... When adding in a record using the code above, all the fields that are NOT on the tbl_Import_TEMP table need to have a default value of "NO" (or unticked). Does this do this now or will I have to amend the code some?

The changes one I am having issue with, ideally I would like the changes table to record the following field each time a change occurs: "ID (only cause I need a unique key)" "HB" "Field Name (so "Status" "ETA" "Finished" etc) "Old Value" "New Value" "Username" "TimeStamp"

So for example if HB 55 have an ETA of 29/04/14, and an update changes that to 30/04/14 then the changes table will have a record like:

IDHBFieldNameOldValueNewValueUserNameTimeStamp
155ETA29/04/1430/04/14tbaker29/04/14 10.35 AM

<tbody>
</tbody>

Does that make sense? So I can see from that record, that the ETA field of HB 55 was changed from 29/04/14 to 30/04/14 by myself at 10.35am on the 29th.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top