Danny's VBA Scripts

Disconnected Recordsets

• Get Everify To Uncover The Truth About Someone

Ever wanted to use a recordset to store temporary data, but been forced to use a multidimensional array because DAO recordsets are always bound to the database? A disconnected recordset is one that is not bound to a database, file, or other data source. It is completely independent. You can add and delete columns, rows, indexes; all without affecting the data in your database.

To create a disconnected recordset, just open it without a connection.

Dim rs As ADODB.Recordset

'Instantiate the recordset Set rs = New ADODB.Recordset

'Append some fields rs.Fields.Append "CustomerID", adInteger rs.Fields.Append "CustName", adVarChar, 20 rs.Fields.Append "Phone", adVarChar, 15 rs.Fields.Refresh

'Add some data With rs .Open .AddNew

!CustomerID = 1 !CustName = "Ollivander" !Phone = "555-5555" .Update End With

'Now do whatever you want with this 'temporary, disconnected recordset

'Clean up rs.Close

Set rs = Nothing

You can also create a disconnected recordset by removing the connection from a bound recordset. For example:

Dim rs As ADODB.Recordset

'Instantiate the recordset Set rs = New ADODB.Recordset

'Give it a client-side cursor, and set its attributes rs.CursorLocation = adUseClient rs.LockType = adLockBatchOptimistic rs.CursorType = adOpenKeyset

'Open the recordset, getting its data from the database rs.Open "Customers", CurrentProject.Connection

'Now disconnect the recordset Set rs.ActiveConnection = Nothing

'Print out the data to prove we still have it Debug.Print rs!CustomerID, rs!CompanyName

'Clean up rs.Close

Set rs = Nothing

Because the default cursor in ADO is server side, you must use a client-side cursor for this to work, because once you disconnect, there is no server. Any changes you make to the data while the recordset is disconnected will not be reflected in the database until you reconnect it and issue the Update or UpdateBatch methods (depending on how many records you changed).

If you intend to use UpdateBatch, the recordset's LockType must be set to adLockBatchOptimistic, as shown above.

'Change the data rs!CompanyName = "who cares"

'Reconnect to the data source rs.ActiveConnection = CurrentProject.Connection

'Update the data rs.UpdateBatch

'Prove it worked

Debug.Print rs!CustomerID, rs!CompanyName

0 -1

Average user rating: 1 stars out of 1 votes