create MDB snapshot of SQLserver database via code

P

pteranodon

For testing purposes, I sometime create a snapshot version of my SQL
server tables in a MDB file. This lets me work away from the server
with the data as it stands at the time of MDB creation. I currently
achieve this with:
- creating an .ADP project connected to the SQL server database
- create an empty .MDB file
- open the .MDB and File->Get External Data->Import...
- point to the .ADP
- Select all tables
- OK

This leaves me with an MDB with stand-alone versions of the SQL
server's tables (minus their indexes) that I can query and play around
with.

How could I go through these steps in code? I see that I could use
DoCmd.TransferDatabase, but I would need to repeat it for each table
in the database and I don't know how to get a list of tables from an
ADP or a SQL server database.

For bonus points, how do I import (or recreate) the indexes that exist
on the source tables?

Thanks for any pointers!

Kevin
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Transfer data from any mdb file to sql server? 1
copy SQLserver tables into .mdb (not linked) 0
sqlserver 2000 database access is slow 4
Working outlook via vba code with xlsx - error 0
Unrecognized database format 'S:\xxx.accdb' (Microsoft Jet Database Engine) 0
adp form save\update problems 3
Server error 262 create data base permission denied in database ma 0
Using MDE file to connect to SQLServer via ODBC 1

Top