• Home
 · 
• Databases/Data Access/DAO/ADO
 · 
• Persisted Recordset Sample.

Persisted Recordset Sample.

Persisted Recordset Sample.

Rate Persisted Recordset Sample.

• 1
• 2
• 3
• 4
• 5

(1(1 Vote))

' 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