Permood,
To do this, you use the ADO Recordset object's Save method. The following
examples demonstrate how to save, re-open, modify, then re-save such 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 XML format. Saving the
'recordset in 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
'Leave both files on the disk for the present, 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 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!
'
'The next example shows you how to re-open the recordset we saved to
'disk in the preceding section, make a change to it, then re-save it.
Dim rs As ADODB.Recordset
Dim strADTGFile As String
Set rs = New ADODB.Recordset
'Specify the output file
strADTGFile = "c:\Temp\Customers.adtg"
'When you want to open a recordset using a file as its source, you must do
'so without specifying a connection. This creates what is called, a
disconnected
'recordset. Once the recordset is open, you can work with it just like any
other
'recordset, but the recordset will be bound to the file - not the database!
If you
'want to bind the recordset to the database, you must then set the recordset
's
'ActiveConnection property.
'
'Our example reconnects to the database, but also re-saves the recordset to
the
'file. Open the recordset with a client-side cursor, but NO connection!
rs.CursorLocation = adUseClient
rs.Open strADTGFile, , adOpenStatic, adLockOptimistic
'Now set the recordset's connection
rs.ActiveConnection = CurrentProject.Connection
'Make a change and save it again
rs!Fax = "555-1234"
rs.Update
Kill strADTGFile
rs.Save strADTGFile, adPersistADTG
'Clean up
rs.Close
Set rs = Nothing
'The final example opens the file again to demonstrate that we have indeed
'accomplished our goal of saving a modified recordset, after which, the two
'output files are deleted, since we don't need them any more.
Dim rs As ADODB.Recordset
Dim strADTGFile As String
Dim strXMLFile As String
Set rs = New ADODB.Recordset
'Specify the output file
strADTGFile = "c:\Temp\Customers.adtg"
'Open the recordset with a client-side cursor,
'but NO connection!
rs.CursorLocation = adUseClient
rs.Open strADTGFile, , adOpenStatic, adLockOptimistic
'Now prove that the data had changed since the last operation
Debug.Print rs!Fax
'Clean up
rs.Close
Set rs = Nothing
Kill strADTGFile
Kill strXMLFile
I hope this helps.
Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html