3

Info: I am setting up a new MS Access database for a small team (~10 person) project. There is a table "A" which is never modified but has relations to another table based on the field "Tag Number". This other table "B" contains all the dynamic information which is modified regularly by the team. Once a week, we receive a new Excel spreadsheet that contains the latest engineering export, which contains the entire contents of the table A. Some new records will be added and some records will be gone every week.

My question is this:

How do I replace the entire contents of the A table? I want to ensure that the relation to B table stays intact. I could delete the entire table and copy/paste in from excel (70000 rows), or I can import the excel spreadsheet to a new table and then use a combination of Update/Append/Delete queries. (Update to modify existing records, Append to add the new non existent records and Delete to remove records no longer present in the latest spreadsheet.)

Or some other, better, method.

Help please?

1
  • I'd like to add a suggestion. (I work with a lot of similar tasks ). If this will become a regualr thing, you might want to automate the task. My advice would be that you should create a staging table to import the data, and then write your merge/upsert queries to insert, update and "retire" the rows you don't need. If the input file is relatively consistent you should be able to make this a reuseable process. Commented Aug 17, 2021 at 22:02

2 Answers 2

3

As Access does not support UPSERT semantics you're stuck with something along the lines:

insert ... into tbl select ... from tmp_tbl where not exists (select 1 from tbl where tbl.id = tmp_tbl.id)`

And similar for updates and deletes using outer joins where appropriate.

That means three SQL-statements.

See this question on SO.

Edit:

I would recommend not to delete records, but instead to mark them 'invalid', that way you can decide, what to do with tags in your other table, if necessary.

1
  • I agree. Set a flag on the records you want to retire. e.g. "IsActive = 1" or similar, Consider adding a key or date field when inserting new records, so you can query/ filter/ report on past "inactive" transactions. Commented Aug 17, 2021 at 22:05
0

This is a two step process; UPSERT and DELETE. You could chain them with VBA that calls both queries.

First you want to UPSERT using an UPDATE LEFT JOIN like this:

UpdateQueryExample

UPDATE A
LEFT JOIN B ON A.ID = B.ID 
SET A.FirstName = [B].[FirstName], 

Now that you have updated all records and inserted any missing ones you would have to delete the ones that don't exist in B.

DeleteQueryExample

DELETE A.ID, *
FROM A LEFT JOIN B ON A.ID = B.ID
WHERE B.ID IS NULL

The WHERE B.ID IS NULL should include only things from A that are not found in B. So it will delete things deleted from B.

Now let's do them both in one transaction with the chance to rollback if we encounter an error.

Public Sub UpsertExample()
    DAO.DBEngine.BeginTrans
    
    On Error GoTo tran_Err
    
    CurrentDb.Execute "UpdateQueryExample", dbFailOnError   
    CurrentDb.Execute "DeleteQueryExample", dbFailOnError

    DAO.DBEngine.CommitTrans
    
Exit Sub
    
tran_Err:
    
    DAO.DBEngine.Rollback

    MsgBox "Transaction failed. Error: " & Err.Description
End Sub
3
  • Why was I downvoted without comment? The accepted answer isn't even an answer. They just point to an answer on a different site that was closed as a duplicate.
    – HackSlash
    Commented Aug 17, 2021 at 19:50
  • 1
    While it would be great if people would explain why they downvote, there is no requirement to do so. FYI, I appreciate the answer!
    – Hannah Vernon
    Commented Aug 17, 2021 at 20:29
  • Also, you're free to downvote answers you don't think are helpful or are incorrect, etc. Votes are how the best answers rise to the top.
    – Hannah Vernon
    Commented Aug 17, 2021 at 20:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.