Reduced overhead when working with disconnected Recordsets

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("", "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        rs.Close    End If        ' (re) open the file    rs.Open Filename, , , , adCmdFile    Set GetEmptyRecordset = rsEnd Function' Reconnect a recordset to a database and perform' a batch updateSub 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    rs.UpdateBatch    ' disconnect the recordset and close the connection    Set rs.ActiveConnection = Nothing    cn.CloseEnd 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"Const SOURCE_STRING = "SELECT * FROM Authors WHERE 1=0"Dim rs As New ADODB.Recordset' Get the structure of the RecordsetSet rs = GetEmptyRecordset("", CONN_STRING, SOURCE_STRING)' Add one record    rs.AddNewrs("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") = 1rs.Update' Add here other records, if you want' .....' Update the database    ReconnectRecordset rs, CONN_STRINGrs.Close

Share the Post:
XDR solutions

The Benefits of Using XDR Solutions

Cybercriminals constantly adapt their strategies, developing newer, more powerful, and intelligent ways to attack your network. Since security professionals must innovate as well, more conventional endpoint detection solutions have evolved

AI is revolutionizing fraud detection

How AI is Revolutionizing Fraud Detection

Artificial intelligence – commonly known as AI – means a form of technology with multiple uses. As a result, it has become extremely valuable to a number of businesses across

AI innovation

Companies Leading AI Innovation in 2023

Artificial intelligence (AI) has been transforming industries and revolutionizing business operations. AI’s potential to enhance efficiency and productivity has become crucial to many businesses. As we move into 2023, several

data fivetran pricing

Fivetran Pricing Explained

One of the biggest trends of the 21st century is the massive surge in analytics. Analytics is the process of utilizing data to drive future decision-making. With so much of

kubernetes logging

Kubernetes Logging: What You Need to Know

Kubernetes from Google is one of the most popular open-source and free container management solutions made to make managing and deploying applications easier. It has a solid architecture that makes

ransomware cyber attack

Why Is Ransomware Such a Major Threat?

One of the most significant cyber threats faced by modern organizations is a ransomware attack. Ransomware attacks have grown in both sophistication and frequency over the past few years, forcing

data dictionary

Tools You Need to Make a Data Dictionary

Data dictionaries are crucial for organizations of all sizes that deal with large amounts of data. they are centralized repositories of all the data in organizations, including metadata such as