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 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 SubUsing 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("c:empty.rs", 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
