Copy ADO to MDB

[Greg Franke]

I want to copy an ADO recordset to a seperat MDB file & Table. Any way to do
this?

Thanks

 

[Alex Dybenko]

you can open 2 recordsets and copy from one to another:

Dim fld As Field

rstDest.Open ...
rstSource.Open ....

rstSource.MoveFirst

Do Until rstSource.EOF
rstDest.AddNew
For Each fld In rstSource.Fields
rstDest.Fields(fld.Name).Value =
rstSource.Fields(fld.Name).Value
Next fld
rstDest.Update
rstSource.MoveNext
Loop

 

[Greg Franke]

Yes to copy one ADO RST to another.

But I want to copy my ADO RST to an access MDB file.

 

[Alex Dybenko]

so, you can open 2 recordsets and copy data between them.
What you can also do - open rst on one DB, disconnect it and the connect to
other db. this could also work.
not 100% clear what do you mean with "copy my ADO RST to an access MDB
file". MDB file can not store ado rst, it can store table(s). If you would
describe the whole thing you want to do - we could give a better advice )

 

[Greg Franke]

OK.. I'll Try. I need to be able to take data from an ADO RST and export
that data to a table in a separate MDB file. I know how to export or save
the ADO data in XML format but not in and MDB.

 

[Graham R Seach]

Greg,

To do all 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 you 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

If you 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!

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[Greg Franke]

Can I link to an external MDB file & Table using ADO?

 

[Graham R Seach]

Greg,

<<Can I link to an external MDB file & Table using ADO?>>
Yes, either by creating an ADO connection to it and retrieving its data as
usual, or by retreiving its data via SQL:
rs.Open "SELECT * FROM [c:\MyProjectDir\MyProject.mdb].[tblMyTable]", cn

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------