by Anthony Loera (7 Submissions)
Category: Databases/Data Access/DAO/ADO
Compatability: VB 6.0
Difficulty: Unknown Difficulty
Originally Published: Mon 20th August 2001
Date Added: Mon 8th February 2021
Rating: 
(1 Votes)
Persisted Recordset Sample.
VBcoders
' 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 = 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
rs.UpdateBatch
' disconnect the recordset and close the connection
Set rs.ActiveConnection = Nothing
cn.Close
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"
Const SOURCE_STRING = "SELECT * FROM Authors WHERE 1=0"
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.AddNew
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
rs.Update
' Add here other records, if you want
' .....
' Update the database
ReconnectRecordset rs, CONN_STRING
rs.Close