Disconnected Recordsets
Disconnected Recordsets are great when you want to retrieve and work with data from your database without the risk of creating conflicts with other users, in fact they are a fully functional Recordsets but they do not hold any lock in the database. You can keep it open as long as wish and also re-synchronize it to your database.
Creating a Disconnected Recordset
Creating a disconnected Recordset using ADO is actually very easy. After creating your Recordset object, you just need to modify some properties:
Set objRS = New ADODB.Recordset
With objRS ‘ create a disconnected RecordSet
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockBatchOptimistic
.Open Source:=strSQL, ActiveConnection:=dbConn, Options:=adCmdText
Set .ActiveConnection = Nothing
End WithdbConn.Close ‘ close your connection to the database
The above code snippet assumes that you have already connected to your database using the Connection object dbConn, and that your SQL is contained within your string strSQL.
Now you can work with your data without worrying of creating conflicts with other users.
Re-Synchronize your disconnected Recordset
Resynchronizing your disconnected Recordset to the data in your database is even easier:
dbConn.Open ‘ reopen the connection to the database
Set objRS.ActiveConnection = dbConn ‘ set the activeconnection
objRS.Requery Options:=adCmdText ‘ requery the database
Set .ActiveConnection = Nothing ‘ make it a disconnected recordset once again
dbConn.Close
Have you ever used a disconnected Recordset? I find them really great, especially when I load up the data in a Userform and allow the user to filter the data using userform controls!