I am working on a MS Access database. Can I script the data, and the database objects, into a SQL script?
-
Why not just backup the MCD/ACCDB/ADP file?Tony Toews– Tony Toews2009-12-24 21:07:01 +00:00Commented Dec 24, 2009 at 21:07
-
What environment do you want to run the script in? A web server? A scheduled task in Windows? From within Access itself?David-W-Fenton– David-W-Fenton2009-12-24 21:08:30 +00:00Commented Dec 24, 2009 at 21:08
-
And when you say "database objects" do you mean Access objects, i.e., forms/reports/macros/modules?David-W-Fenton– David-W-Fenton2009-12-26 21:00:59 +00:00Commented Dec 26, 2009 at 21:00
Add a comment
|
2 Answers
I guess you want to backup your data programmatically from within your database Application?
For this scenario, split frontend and backend (data) into two separate MDB files. Then your frontend part can copy the data file to your backup folder programmatically, for example, by using the FileSystemObject. If you don't know how to split frontend and backend, look here.
1 Comment
David-W-Fenton
Why use the FSO when you have file copy operations available directly in VBA without adding a reference or initializing the FSO with late binding?
Not sure if there are any tools out there, I'm sure there has got to be in this day and age, but you can do it if you want to do some vba coding.
So, if your tool search has come up blank you could use the objects to generate a sql script for you. For example you could use the Table objects to get the column properties and infer what you would need into a sql script. I know it can be done, I did it in Access 97 a long long time ago. The queries already have a sql script behind them so that should be easy to get.
As for the data it's much the same but easier. You could even write a query that could do it for you. Just have it output to a file your insert statements.
You may need to work around id issues if you are reimporting into an access database but been so long could not tell you what to do.
