Access 2013: could I use a data macro to trigger an MS SQL SP without delaying the execution in Access?

Question

My client uses a multi-user split Access database (ie back end DB on server, client DB on each PC) as a point of sale system as well as for management functions.

He now needs it to update a remote MS SQL database but cannot afford to slow down the Access clients as customers are waiting. If I add code to each update / append / delete in the access client DBs to run the SQL SP it would slow down each transaction too much (I have tried that).

I am wondering whether I could use trigger macros on the back-end Access DB to run the SQL SPs without slowing down the client DB. Would the client DB have to wait for the trigger macro to run before it resumed its work, or would this be a good way to disconnect the client from an SQL update that is taking place on the server?

I have never used trigger macros and it is going to be a lot of work to research and create these on each table in order to test it so, if anyone can answer the above it could save me many hours of (possibly wasted) work!

Answer 1

I am wondering whether I could use trigger macros on the back-end Access DB to run the SQL SPs

Not directly, no. Event-driven Data Macros in Access cannot execute external code (VBA routines, pass-through queries, etc.) and cannot operate on linked tables.

What you could do is use event-driven Data Macros on the main table(s) to gather the information required by the stored procedures and save it in separate "queuing" table(s) in the back-end, then run a scheduled task every few minutes (or so) to call the stored procedures and pass them the "queued" information.

Answer 2

I simpler solution might be to use a linked table to the SQL db, then create and call a VBA function that build and executes the update query using. Using the SQLPassthough on the Linked table. This allows the function to return immediately, avoids maintenance and overhead in the SQL db, as well as all the initial setup time. Also the linked table can retain all connection including the username and password, if desired.

STUB of DAO SQLPassthrough call from Data Macro

VBA Function (generic code)

Public Function SetTimeStamp(ByVal RecordID as Variant)

   Dim strSQL As String
   Dim db As DAO.Database

   strSQL = "UPDATE tblName SET tblName.TimeStampField = " & Now()
   strSQL = strSQL & " WHERE RecordIDField = " & RecordID
   Set db = CurrentDB()
   db.execute strSQL, dbSQLpassThrough + dbRunAsync

End Function

To implement in the After Update event, use the SetLocalVar to call the Function Data Macro (generic code)

If Updated("MyField1") or Updated("MyField2") or Updated("MyField3")
   SetLocalVar 
            Name  varTemp
      Expression  =SetTimeStamp([TableName].[RecordIDField]
End If

This will cause the function to Execute. It in turn run the query using the SQLPassThrough and Asynchroneous options, which causes Zero slowdown to the Access app. It can easily be modified to pass in the Table Name and TimeStamp Field names as parameters, so that may be used on any table, write more fields, etc.

I find the advantage to a single Access function, is that if you decide to enhance it, you only need to add the Data field(s) to your tables and fix a single function. Also there are no scheduled tasks, or queues to maintain, making this a cleaner solution.

Art