how can I copy an ADO database to a new database?

I want to connect to a database with the ADO data control, remove or edit a bunch of records and then save it to a different place. Is this possible? I haven't found anything in the help file or in the books I've been reading.
LVL 1
chaos_59Asked:
Who is Participating?
 
sharmonConnect With a Mentor Commented:
Is this a file based database like Access?  If so, why don't you just copy the database file via code to this different place first, then connect to the copied database via ADO and do your updates on the copied database directly.
0
 
TravisHallCommented:
What he said. (Which is to say, Sharmon deserves the points, not me.)

But also, generally you don't save a database. The database provider writes any changes you make automatically. Once you change the database, for all intents and purposes, it is changed. There is no original that you can revert to later after saving to a different file.

So with databases, copy then change, or extract then transform and import. Never change then save.
0
 
gbarenCommented:
chaos,

You can use a disconnected recordset.

1. Connect to source
   rs.ActiveConnection.Open "DSN1"
2. Get a Recordset
3. Disconnect
   rs.ActiveConnection.Close
4. Update the Records
5. Connect to a different source
   rs.ActiveConnection.Open "DSN2"
6. Update
   rs.UpdateBatch

I don't know whether this would work. It does sound reasonable, though.

gbaren
0
 
chaos_59Author Commented:
This was the conclusion I was trying to avoid, but it seems like the only/best answer.
Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.