Saving a Recordset to a File

To do all this, you use the Recordset object's Save method. The following examples demonstrate how to save, reopen, modify, and then resave a recordset. Not all providers allow you to save a recordset to a file. You're safe with the Jet OLE DB provider, but to be certain with other providers, open the recordset using a client-side cursor.

Dim rs As ADODB.Recordset Dim strADTGFile As String Dim strXMLFile As String

Set rs = New ADODB.Recordset

'Open the recordset rs.CursorLocation = adUseClient rs.Open "Customers", CurrentProject.Connection, _ adOpenStatic, adLockOptimistic, adCmdTable

'Specify the output files strADTGFile = "c:\Temp\Customers.adtg" strXMLFile = "c:\Temp\Customers.xml"

You'll get a runtime error if you try to save a recordset to a file that already exists, so we have to delete any existing file first. But if you try to delete a file that doesn't exist, you'll still get a runtime error.

On Error Resume Next Kill strADTGFile Kill strXMLFile Err.Clear On Error GoTo 0

Now use the Save method to save the recordset to disk. You have two options with regard to file formats: Advanced Data Tablegram (ADTG), which is a proprietary Microsoft format, or the Extensible Markup Language (XML) format.

Saving the recordset in the XML format is great if you intend to exchange data with another application that supports XML, but the ADTG format will produce a smaller file size.

'Save the recordset to disk as an ADT file rs.Save strADTGFile, adPersistADTG

'Just to show that it can be done, save 'the recordset to disk as an XML file rs.Save strXMLFile, adPersistXML

'Clean up rs.Close

Set rs = Nothing Set cn = Nothing

We'll leave both files on the disk for now, because we haven't finished with them yet.

If we were to continue working with the recordset, adding and deleting rows, or modifying data, the changes would be reflected in the database, not in the file. Any changes you want reflected in the file must be explicitly saved to the file—remember, this recordset is bound to the database by a connection.

0 0

Post a comment