The "Retrieving Data from Microsoft Access Using a Plain Text Query" section mentioned that getting in and out of the database as quickly as possible was an important goal. However, the Recordset object is a powerful tool that you would often like to hold onto and use without locking other users out of the database. The solution to this problem is the ADO disconnected recordset feature.
A disconnected recordset is a Recordset object whose connection to its data source has been severed, but that can still remain open. The result is a fully functional Recordset object that does not hold any locks in the database from which it was queried. Disconnected recordsets can remain open as long as you need them, they can be reconnected to and resynchronized with the data source, and they can even be persisted to disk for later retrieval. A few of these capabilities are examined in the following example.
Imagine you wanted to implement a feature that would allow users to view any group of customers they chose. Running a query against the database each time the user specified a different criterion would be an inefficient way to accomplish this. A much better alternative would be to query the complete set of customers from the database and hold them in a disconnected recordset. You could then use the Filter property of the Recordset object to quickly extract the set of customers that your user requested.
The following example shows all of the elements required to create a disconnected recordset. Again, assume the availability of the public gobjConn Connection object:
Public grsData As ADODB.Recordset
Public Sub CreateDisconnectedRecordset ()
Dim szSQL As String
' Create the SQL Statement.
szSQL = "SELECT CustomerlD, CompanyName, ContactName, Country " & _ "FROM Customers"
' Steps to creating a disconnected recordset: ' 1) Create the Recordset object. Set grsData = New ADODB.Recordset ' 2) Set the cursor location to client side. grsData.CursorLocation = adUseClient ' 3) Set the cursor type to static. grsData.CursorType = adOpenStatic ' 4) Set the lock type to batch optimistic. grsData.LockType = adLockBatchOptimistic ' 5) Open the recordset.
grsData.Open szSQL, gobjConn, , , adCmdText ' 6) Set the Recordset's Connection object to Nothing. Set grsData.ActiveConnection = Nothing
' grsData is now a disconnected recordset. Sheet1.Range("A1").CopyFromRecordset grsData
Note that the Recordset object variable in the preceding example is declared with public scope. If you were to declare the Recordset object variable at the procedure level, VBA would automatically destroy it when the procedure ended and it would no longer be available for use.
Six crucial steps are required to successfully create a disconnected recordset. It's possible to combine several of them into one step during the Recordset.Open method, and it's more efficient to do so, but they are separated here for the sake of clarity:
□ You must create a new, empty Recordset object to start with.
□ You must set the cursor location to client-side. Because the recordset will be disconnected from the server, the cursor cannot be managed there. Note that this setting must be made before you open the recordset. It is not possible to change the cursor location once the recordset is open.
□ The ADO client-side cursor engine supports only one type of cursor, the static cursor, so this is what the CursorType property must be set to.
□ ADO has a lock type specifically designed for disconnected recordsets called Batch Optimistic. The Batch Optimistic lock type makes it possible to reconnect the disconnected recordset to the database and update the database with records that have been modified while the recordset was disconnected. This operation is beyond the scope of this chapter, so note that the Batch Optimistic lock type is required in order to create a disconnected recordset.
□ Opening the recordset is the next step. This example used a plain text SQL query. This is not a requirement. You can create a disconnected recordset from almost any source that can be used to create a standard recordset. The client-side cursor engine lacks a few capabilities, however; multiple recordsets are one example.
□ The final step is disconnecting the recordset from the data source. This is accomplished by setting the recordset's Connection object to Nothing. If you recall from the "Recordset Object Properties" section, the Connection object associated with a Recordset object is accessed through the Recordset.ActiveConnection property. Setting this property to Nothing severs the connection between the recordset and the data source.
Now that you have a disconnected recordset to work with, what kinds of things can you do with it? Just about any operation the Recordset object allows. Say that the user wanted to see a list of customers located in Germany, sorted by alphabetical order. This is how you'd accomplish that task:
' Set the Recordset filter to display only records
' whose Country field is Germany.
grsData.Filter = "Country = 'Germany'"
' Sort the records by CompanyName.
grsData.Sort = "CompanyName"
' Load the processed data onto Sheetl
If you are working in a busy multi-user environment, the data in your disconnected recordset may become out-of-date during the course of your application due to other users inserting, updating, and deleting records. You can solve this problem by requerying the recordset. As demonstrated by the following example, this is a simple matter of reconnecting to the data source, executing the Recordset.Requery method, then disconnecting from the data source:
' Reconnect to the data source.
Set grsData.ActiveConnection = gobjConn
' Rerun the Recordset object's underlying query, grsData.Requery Options:=adCmdText
' Disconnect from the data source.
Set grsData.ActiveConnection = Nothing
Was this article helpful?
Get Inspired By The Most Popular Bloggers Online! If You Want To Skyrocket Your Success With Business And Improve Your Overall Life You Need To Have A Look At The Accidental Blogging Millionaires! Business can be a fight, particularly when you’re trying to establish one online and like all fights, to succeed you must find the winning techniques and apply them.