Browse DevX
Sign up for e-mail newsletters from DevX

Tip of the Day
Language: VB6
Expertise: Intermediate
Jul 24, 1999



Building the Right Environment to Support AI, Machine Learning and Deep Learning

Reduced overhead when working with disconnected Recordsets

ADO includes the capability to work with disconnected Recordsets, which is a great way to save database connections. The typical sequence when working with such Recordsets is as follows: (1) you connect to the database, retrieving an empty Recordset: this step is necessary in order to correctly retrieve the table structure; (2) you disconnect the Recordset by setting its ActiveConnection property to Nothing; (3) you add one or more records containing the data entered by the end user; (4) you reconnect to the database and assign a correct Connection reference to the Recordset's ActiveConnection property; (5) finally, you can issue an UpdateBatch method to move the data from the local Recordset to the actual database.

While the above schema works, it has one shortcoming: it forces you to initially connect to the database in order to retrieve the table structure. If you omit this step the UpdateBatch method will fail with an error "Insufficient Base Table Information". Thus you are going to spend some seconds at the beginning of each session only to retrieve this empty Recordset.

Fortunately, thanks to the capability to persist a Recordset on a file, it is possible to avoid this overhead. The trick is really simple: rather than connecting to the database you open a persisted Recordset that you've created previously. Thus you only need to create a local file that contains the image of the empty Recordset, and refresh it only when the structure of the base table changes.

To streamline the whole process I've create two reusable procedures that automatically create the file if needed, and that perform the batch update.

' Open an empty recordset from a persisted file. If the file
' doesn't exists it uses the last two arguments to create it
' ConnString should be a valid connecton string
' Source should be a SELECT clause that returns zero records
' Example:  Dim rs As ADODB.Recordset
'           Set rs = GetEmptyRecordset("c:\empty.rs", "DSN=Pubs",
'  '               "SELECT * FROM Authors WHERE 0=1"

Function GetEmptyRecordset(ByVal Filename As String, ByVal ConnString As String, _
    ByVal Source As String) As ADODB.Recordset

    Dim rs As New ADODB.Recordset
    ' check whether the file already exists
    If Len(Dir$(Filename)) = 0 Then
        ' file not found, connect to the database
        rs.Open Source, ConnString, adOpenStatic, adLockBatchOptimistic
        ' then save it for future sessions
        rs.Save Filename
    End If
    ' (re) open the file
    rs.Open Filename, , , , adCmdFile
    Set GetEmptyRecordset = rs
End Function

' Reconnect a recordset to a database and perform
' a batch update

Sub ReconnectRecordset(rs As ADODB.Recordset, ByVal ConnString As String)
    Dim cn As New ADODB.Connection
    ' open the connection
    cn.Open ConnString
    ' perform the batch update
    Set rs.ActiveConnection = cn
    ' disconnect the recordset and close the connection
    Set rs.ActiveConnection = Nothing
End Sub
Using these routines is straightforward:

' Add new records to the Authors table in the Pubs database
' using a disconnected Recordset and without an initial connection
' (more precisely, this code will perform an extra, initial 
' connection only the first time it is executed.)

Const CONN_STRING = "Provider=SQLOLEDB.1;User ID=sa;" & _
    "Initial Catalog=pubs;Data Source=P2"

Dim rs As New ADODB.Recordset

' Get the structure of the Recordset
Set rs = GetEmptyRecordset("c:\empty.rs", CONN_STRING, SOURCE_STRING)

' Add one record    
rs("au_id") = "978-43-6543"
rs("au_fname") = "Francesco"
rs("au_lname") = "Balena"
rs("city") = "Menlo Park"
rs("State") = "CA"
rs("Zip") = "94025"
rs("Contract") = 1

' Add here other records, if you want
' .....

' Update the database    
ReconnectRecordset rs, CONN_STRING

Francesco Balena
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date