CODEWITHSUNDEEP
sqlms-accessasynchronousvbams-access-2010
Kai
Kai

Reputation: 2168

MS Access Asynchronous Queries

I have a basic SELECT query fired from an Access form that takes a while to execute, so I would like to run the query asynchronously and allow my users to continue using the form (or at least keep them updated on the progress).

The problem is that Access freezes the application when code is being executed, appearing to the users to have crashed - even to the point of Windows marking it 'Not Responding' and offering to kill it. Obviously not very user-friendly! I have tried using the code listed on the MSDN here and a variant method here.

Both these solutions do seem to run the query 'asynchronously' (the code block firing the async query completes, and the rs_FetchProgress and rs_FetchComplete events (or cn_ExecuteComplete event in the second solution) fire and run... but Access's interface still locks up until the query is done executing. Calling Repaint and DoEvents in various places (such as the rs_FetchProgress event) does not seem to have any effect.

I doubt it's relevant information, but the view being SELECTed from is in SQL Server, the view doesn't return a huge amount of data but does take about 20 seconds to process.

Upvotes: 2

Views: 1689

Answers (1)

HansUp
HansUp

Reputation: 97101

Do not pull a huge recordset when you open the form. Base the form on a query which pulls only a few or even no records when it first loads. Then give the users a method to select a different reasonably-sized subset of records.

Try to avoid pulling huge recordsets regardless of whether your data source is a linked Access table or a client-server database.

Upvotes: 1

Related Questions