Access™ 2007 VBA Programmer's Reference

7.9. Testing the State Property

If you choose not to rely on events, you can always test the object's State property. It returns a value that indicates the status of the operation currently being carried out. For example, the following code segment tests the current state of a Connection object's Open operation.

cn.Open CurrentProject.Connection, , , adAsyncConnect
Do Until cn.State = adStateOpen
    DoEvents
Loop

rs.Open "Invoices", cn

Not what you'd call a professional approach, but every programmer is different, and you might like to do such things. Of course, the previous code could get into an endless loop if the connection is never opened, so it is usually a good idea to limit the number of loops to a finite number when using an asynchronous connection. The State property can return the following values.

Constant	Value	Description
adStateClosed	0	The object is closed.
adStateOpen	1	The object is open.
adStateConnecting	2	The object is connecting.
adStateExecuting	4	The object is executing a command.
adStateFetching	8	The object is retrieving rows.