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

Solution 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

Solution 2

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!

Comments

[Tony Toews]

Post your code that you are using to do the update as we may be able to give you some suggestions there.

[Icode4food]

That is a good point. But, I am actually updating a single record with DAO that opens a linked table through the network. The network isn't the latest and greatest but I am doing hourly work and have no power over that issue.

[Raj More]

Where does the actual table reside?

[Icode4food]

Your ODBC connection suggestion seems hopeful. But...When I try to create a file DSN and try to link tables, Access gives me the following error: "You cannot use ODBC to import from, export to, or link an external Microsoft Office Access or ISAM database table to your database." What am I missing?

[Raj More]

@LanguaFlash answer edited to add sample

[David-W-Fenton]

This answer is just WRONG. If you're using ODBC linked tables, DAO is certainly your best choice, and Jet will hand off to the server any request that Jet knows can be handled on the server. A batch update like in the sample ADO code here will always be properly handed off to the server. Using ADO to run ODBC seems completely wrongheaded to me -- if you opt for ADO, then use OLEDB and connect directly and avoid ODBC entirely! And in any event, you should try a passthrough first before mucking about in code.

[Raj More]

apologies.. OTTOMH. I corrected to use ADO