ms-accessms-access-2010ms-access-2013ms-access-data-macro

MS Access event-driven Data Macro to update table (example)


I have imported some data into Access from Excel. Now I want to write a code similar to trigger which will update another table whenever I get new data from Excel.

I know I cannot write a trigger in Access, so I am trying to use a Data Macro. Can anyone provide me help in how to do this using a data macro?


Solution

  • Let's say that you have a table named [Events] and you are importing data from Excel and appending it to the table

    ID  EventName              EventType           EventDate 
    --  ---------------------  ------------------  ----------
     1  New Staff Orientation  Training: in-house  2013-06-07
     2  TGIF barbecue lunch    Social              2013-06-14
    

    Say that you also have a table named [EventTypes] to keep track of the categories that can be assigned to events. The [EventType] values coming in from the Excel data need to be approved to avoid unnecessary duplication, typos, etc.. Your [EventTypes] table looks like this

    EventType           Added                Approved
    ------------------  -------------------  -------------------
    Training: in-house  2013-06-01 09:15:33  2013-06-01 09:37:16
    Social              2013-06-07 10:01:23  2013-06-07 10:22:00
    

    You can create an "After Insert" data macro on the [Events] table to insert new [EventType] values into the [EventTypes] table like this:

        SetLocalVar
                Name  NotFound
                Expression  = True

        Look Up A Record In  EventTypes
                Where Condition  =[EventTypes].[EventType]=[Events].[EventType]

            SetLocalVar
                    Name  NotFound
                    Expression  = False

        If  [NotFound]  Then

            Create a Record In  EventTypes

                SetField
                        Name  EventTypes.EventType
                        Value  = [Events].[EventType]

                SetField
                        Name  EventTypes.Added
                        Value  = Now()

        End If

    Now if you import an Event with a new EventType...

    ID  EventName              EventType           EventDate 
    --  ---------------------  ------------------  ----------
     1  New Staff Orientation  Training: in-house  2013-06-07
     2  TGIF barbecue lunch    Social              2013-06-14
     3  Bathtub races          Team Building       2013-06-15
    

    ...the data macro will automatically add it to the EventTypes table

    EventType           Added                Approved
    ------------------  -------------------  -------------------
    Training: in-house  2013-06-01 09:15:33  2013-06-01 09:37:16
    Social              2013-06-07 10:01:23  2013-06-07 10:22:00
    Team Building       2013-06-11 08:38:37