Livio/ May 2, 2018/ Excel, Excel VBA/ 0 comments

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 With

dbConn.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!

Me

Leave a Comment

Your email address will not be published. Required fields are marked *

*
*