multi-thread in MS Access, async processing

Question

I know that title sounds crazy but here is my situation.

After a certain user event I need to update a couple tables that are "unrelated" to what the user is currently doing. Currently this takes a couple seconds to execute and causes the user a certain amount of frustration. Is there a way to perform my update in a second process or in a manner that doesn't "freeze" the UI of my app while it is processing?

Thanks

Answer 1

I would work on the heart of the problem - Tune the data update queries to run faster.

Having said that, MS Access does not support multi-threading.

So, when you make a blocking call to a procedure, MS Access will freeze the screen until the call returns.

edit

DAO isn't really your best friend if you are updating a large table over a slow network connection. You might want to consider switching to using an ODBC connection and running a optimized update statement.

edit 2

when you use ODBC, you have to write ADO style code to make this work. Note this sample this code is OTTOMH.

dim myConn as ADODB.Connection
dim myCmd as ADODB.Command

set myConn = new ADODB.Connection
myConn.ConnectionString = "Provider=SQLOLEDB;Server=MyServerName;Initial Catalog=MyCatalogName;UID='XXX';PWD='YYY'"
myConn.Open

set myCmd =  new ADODB.Command (myConn)
myCmd.SQL = "Update MyTable Set MyColumn = '" & MyDataVariable & "' Where MyPK = '" & MyPKVariable & "'"
myCmd.Execute

myCmd.close
myConn.close

Answer 2

Using docmd.hourglass true before your update statement and docmd.hourglass false after to change the curasor back. Also be sure to put docmd.hourglass false in your error handling routine.

Not the fanciest solution but it might help with the user expectation level.

Answer 3

Does the client need confirmation that the info was updated? If not, then you could open a shell routine which handles the update for you. i.e.

Shell("'C:\Reports\SomeOtherAccessDB.MDB' /x 'SomeMacro'", 1)

By default, this is asynchronous, so all the user will see is a second .mdb on the taskbar for the few seconds that it takes to run.

EDIT

Oh, and Kudos for actually caring about your user's time!