ADO
Disconnected
Recordsets


by Legrev3

and Rick Meyer

Home

To see the actual code for the ADO disconnected recordset Mailing List Demo, click here to download the zip file (36K) containing the entire demo project.
Here is the scenario:
  • You are working with a database on a server over the internet.
  • You have a rather large amount of data to enter or edit.
  • You don't want to tie up the server or your connection resources for extended periods of time.
    • This is the perfect situation for a disconnected recordset.
    Here is what happens:
  • 1. Connect to the database.
  • 2. Retrieve your recordset data.
  • 3. Close the connection.
  • 4. Work with the disconnected recordset.
  • 5. Reconnect to the database.
  • 6. Batch update the database.
  • 7. Close the connection.
  • 8. Repeat steps 4 through 7 until done.
    • Specifically the code for 1 through 3 above is: 
        Dim strFilespec As String
    Dim strConn As String
    Dim cnContacts As ADODB.Connection
    Dim rsContacts As ADODB.Recordset

    Set cnContacts = New ADODB.Connection
    Set rsContacts = New ADODB.Recordset

    strFilespec = App.Path & "\" & "Contacts.mdb"
    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                "Data Source=" & strFilespec & ";"
    
    cnContacts.CursorLocation = adUseClient
    cnContacts.Open strConn

    rsContacts.Open "SELECT * FROM MailList ORDER BY ContactName", _
        cnContacts, adOpenStatic, adLockBatchOptimistic, adCmdText

    Set rsContacts.ActiveConnection = Nothing
    cnContacts.Close
    Then as you are working with your disconnected recordset, you just update any edits or addnews as usual with: 
        rsContacts.Update
    And when you are ready for steps 5 through 7: 
        cnContacts.Open
    Set rsContacts.ActiveConnection = cnContacts

    rsContacts.UpdateBatch
    rsContacts.Requery

    Set rsContacts.ActiveConnection = Nothing
    cnContacts.Close