Make a copy of the current database automatically (A2000)

Topic

New Reply

I created a database with a form with 9 buttons. The fifth step presents an input box so the user can create a name for another database that will be created and loaded with many of the objects from the current database. Since I want the entire process to be as ‘hands off’ as possible for the user, I was dismayed when I discovered that references on the new database always have to be juggled for the code to work properly.

I finally decided that instead of creating a new database and exporting objects to it I would simply copy the entire database to the new name, thereby avoiding the references problem.

I would still like this process to be automated, avoiding the user having to go to Windows Explorer and manually copying the database. I thought of capturing the path and filename of the source database and prompting for the new copy database name — then create a function or macro from this information and export it to the utility database to be run to make the copy. Am I making this more difficult than necessary to keep it automated — after all, you can’t copy a database if it’s open. Any thought and/or tips on how to accomplish creating a macro or function from the captured info. would be welcomed.

Thanks,

Randy

Reply | Quote

Replies

Your database is the tables. Everything else can be copied (drag the origingal in explorer to create a complete copy and rename )or exported to the new database. Now all that is left is for your user to create a new table and I think that the process of creating you a new table gives you the choice of current or other db.
The create new table can be done with an SQL expression in code which can be done by creating the table via a query, switching to SQL view and copying the code. I’m fairly sure that you could add the new table name to the SQL. My difficulty is that I am unable to remember the detail and would have to spend half an hour fiddling and testing to get the precise syntax. I hope this gives you some pointers if no one else gives you a more precise answer. The target db name and path could be put in a string, but the syntax of including a string within an SQL is a bit hairy.
Hope this helps.
Mike

Reply | Quote

Thanks for your reply. I will elaborate somewhat.

The entire process is broken up into several steps, all performed with it’s own respective button. I want to have the user complete some of the process, then make a copy of the database with a different name, where they will complete the process a bit differently than on the original database.

I currently have some code that creates a new empty database, and copies all of the objects to the new database. The problem with that is that References (the dirty word) need to be set up on the new copy. I don’t want to encumber my users with that task, so I figure that since making a copy of the entire database preserves the References, that would be the way to do.

I hope this clarifies what I am looking for. I thought of creating a utility with VB6.0 that the user could open from the database that would store the current path and mdb file name, and prompt the user for the new copy name. The user could then shut down the database and press the button on the VB form to make the copy, followed by code to automatically re-open the first database to complete the rest of the steps.

But I was wondering if anyone out there had a better idea.

Thanks again,

GHH3RD
Randy

Reply | Quote

Charlotte’s approach is the simplest and most elegant – but it does require that the user have exclusive use of the database at the point where the compact/repair process is going on, and also cannot be done from code within the current database. I seem to recall a thread where a similar process was going on, and it included programatically setting references, but a quick search didn’t find it. By the way, it is generally possible to copy an Access database when it is open by another user – the multiuser capabilites make that possible.

I’m curious about some aspects of this. For one thing, why do you want to copy the database to begin with? Is your database normally multiuser, or single user? And are the users storing the database on their own workstation, or somewhere else on the server?

Reply | Quote

It would probably be simplest to just compact your database to the destination path and filename the user designates. That leaves your current database intact but gives you s compacted and (fairly ) clean copy as a backup.

Reply | Quote