HandsOn Saving Records to a Disk File

^^ Note: Because this hands-on retrieves data from the Northwind database, adjust the path found in the procedure code to point to the correct location of this file on your computer.

1. In the Visual Basic Editor window, choose Insert | Module.

2. In the module's Code window, type the SaveRecordsToDisk procedure shown below.

Sub SaveRecordsToDisk()

Dim conn As ADODB.Connection Dim rst As ADODB.Recordset Dim strFileName As String Dim strNorthPath As String strFileName = CurrentProject.Path & "\" & "Companies.rst" strNorthPath = "C:\Program Files\Microsoft Office\" & _ "Office11\Samples\Northwind.mdb"

On Error GoTo ErrorHandle

Set conn = New ADODB.Connection

With conn

.Provider = "Microsoft.Jet.OLEDB.4.0" .ConnectionString = "Data Source = " & strNorthPath .Mode = adModeReadWrite

Creating and Manipulating Databases with ADO

.Open End With

Set rst = New ADODB.Recordset With rst

.CursorLocation = adUseClient

' Retrieve the data

adOpenKeyset, adLockBatchOptimistic, adCmdTable

' Disconnect the recordset .ActiveConnection = Nothing

' Save the recordset to disk .Save strFileName, adPersistADTG .Close End With

MsgBox "Records were saved in " & strFileName & "."

ExitHere: ' Cleanup Set rst = Nothing Exit Sub

ErrorHandle:

If Not IsEmpty(Dir(strFileName)) Then Kill strFileName Resume Else

MsgBox Err.Number & ": " & Err.Description Resume ExitHere End If End Sub

3. Run the above procedure to save the recordset to a file.

The procedure shown in this example saves all the data located in the Customers table to a file with an .rst extension. We named this file Com-panies.rst, but you are free to choose any filename and extension while saving your recordset.

Persisted recordsets are very useful for populating combo boxes or list boxes, especially when the data is located on a server and does not change too often. You can update your data as needed by running a procedure that creates a new dump of the required records and deletes the old disk file. This way, your Access application can display the most recent data in its combo or list boxes without having to connect to a database. Let's look at how you can fill a combo box with a saved recordset by working with Custom Project 16-1.

Part II

0 0

Post a comment