Update Access Table with daily excel spread sheet

[stildawn]

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:

ID	HB	Finished
1	5A	Yes
2	5B	No
3	5C	No
4	5D	No

Excel Dump looks like this:

HB	Finished
5A	No
5B	Yes
5C	Yes
5D	Yes
5E	No

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

After the "update" Access should look like this:

ID	HB	Finished
1	5A	No
2	5B	Yes
3	5C	Yes
4	5D	Yes
5	5E	No

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: http://www.mrexcel.com/forum/microso...ml#post3790734

[stildawn]

In an update to this, ideally each time I do this update I would like it to also use the following 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.


Also, an idea could be to have the excel file import into a new "temp" table each time and then update the main table from this temp table or something? Also I will get rid of the autonumber ID and just use HB as that will be better as a unique key.

[aellistechsupport]

From my take I think I would link the generically named Excel file to Access.
Create an Update query with it linked on the same key fields.
Same thing but make it an Append query with Is Null on the key field(s) from the Access table you're updating / adding to to not duplicate data

Or create an Import from the Excel to the Access table and save that, call that out in a Macro.

Just throwing out some thoughts

[ItsMe]

Linking an Excel file to an Access file will cause the Excel file to be read only while the Access file is open. Using the Wizard to create and save an import procedure may be an option. You could bring the daily Excel file into a temp table and then run your update query. Afterwards, delete all of the records in your temp table.

[stildawn]

Linking an Excel file to an Access file will cause the Excel file to be read only while the Access file is open. Using the Wizard to create and save an import procedure may be an option. You could bring the daily Excel file into a temp table and then run your update query. Afterwards, delete all of the records in your temp table.

I'm leaning towards the Temporary "Import Table" then updating the main from that. From other sources I have mashed together this example code which seems to work in the simplified basic example I set up:

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

This first run updates the Main table, the second run adds in new records I believe.

I'm still don't totally understand what they are doing however. Like the update one (first .RunSQL) that only updates one field "Status", but in the real database it would need to update at least 5 or 6 different fields potentially.

Also the second add records run, the Import Temp table will only have like 6 fields (with basic information for each record), but the Main table will have those fields, plus like 6 yes/no fields so users can tick off tasks they have performed. I dont know if the second run does default No values or what?

And ideally, during the updating, I would like a 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:

ID	HB	FieldName	OldValue	NewValue	UserName	TimeStamp
1	55	ETA	29/04/14	30/04/14	tbaker	29/04/14 10.35 AM

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. Obviously there will be lots of field names.

[ItsMe]

Yah, you are going to need more SQL to get the additional fields. Why not just build a couple query objects using the Query Builder? Then call the query objects using VBA
CurrentDB.Execute "MySavedQueryObject"

It will probably run more efficiently. You could always copy the SQL from the query object/builder and use
CurrentDB.Execute "Copied_SQL_Goes_Here"


Either way, use the query builder to include fields to Update in addition to your [Status] field.

[stildawn]

Thanks ItsMe, unfortunately Access isnt my strong suit, how would I do these query objects things you talk about?

[aellistechsupport]

I do the same, a couple of queries.

I would create an Update query linking the Access table and the Excel and link it on your field HB
Do an equal link from Access table to Excel file (this is after doing an Import Link to your Excel file). I don't see an issue with the Excel file being locked when you do this. In my opinion that would be ideal anyway

For the Append query where the data does not exist, do the LEFT OUTER JOIN from Access table to the Excel file WHERE the HB Is Null in the Access table.

Create a Macro to do the Update query first then the Append query second.

Then just run your Macro.

[ItsMe]

In Access, at the top of the window, there is a Ribbon. This is where all of your tools, options, etc. are. Click the "Create" tab in the Ribbon. Now you will see an option to create a query, as well as forms, tables, etc. Click the "Query Design" option and add your temp table to the design area. With that, click the "Design" tab in the ribbon (should default there). There is an option for "Update".

Now you can add fields to your query and save it. You will be prompted to tell Access what table name you would like to associate your Update query with. Associate a table and give your new query a name. Now verify the "Update To" fields in the grid, near the bottom of the window.

[stildawn]

Sorry ItsMe, This is where I go to:



But from here Access didnt ask me to associate any table, and I don't know what to put in the Update To: fields in the grid?

[ItsMe]

Sorry about that. I was confusing an Append query with an update query. Let me take a look at it and see if I can come up with some syntax.

[aellistechsupport]

Why don't you do it the easy ... beginner way without code and just using the built in Queries?

Do the Update based on your field HB to your Access table linked to your Excel file on HB
Do the Append based on your field HB where it Is Null in your Access table linked to your Excel file on HB

Really simple and easy.

[stildawn]

Thanks ItsMe, yeah Access really isnt my strong suit, I have basically only covered basic database, basic queries and forms thus far, but I'm trying to learn the more complex side of things.

[ItsMe]

Go ahead and add your second table onto the query design. Create a join (it will default to an Inner Join and this should be fine). Your temp table will not have the ID field your permanent table has so join on HB.

In the grid at the bottom of the window, clear the fields for the temp table and replace them with one from the permanent table. Now, in the "Update To" field type the name of the Temp Table. Intellisense will show up to help with your expression. Type a period to get the next list of Intellisense. Click or type the field name you want.


Save your Update query and use the Big Red Exclamation button in the Ribbon to run your query.

You should be able to add fields using your query builder now. Also, you will probably want to add criteria.

Post back with your progress.

[stildawn]

Hi ItsMe

This is what I've done:

Main Table View For Your Reference:


Temp Table View For Your Reference:


(obviously this is only an example DB, but once I figure out the concept I'm sure I can scale it up with trials lol.

Query View as it stands:


Think I did this right?

But then when running I get this:


And afterwards, nothing has changed. The Temp table does not show on the Main Table, they are exactly as before??