How to switch between two databases?

[KPickell]

Here is my problem:

First off, I am new to Access and VB but have been programming assembly language and C for about 20 years now.

I would like to backup the complete database every 24 hours ( single user, single machine, single mdb file). I would like to use the compact and repair function to save the duplicate but it requires that the database be closed. Therefore my plan was to have the VB code in the current database shut down at midnite, open a backup "database" that is simply VB code ( no data ) that makes the duplicate of my original database and then closes and reopenes my original database.

I've looked at the Application.closecurrentdatabase function but it seems to terminate VB code in the current database as soon as it is executed so I can't open a new one right after.

Help
Kevin

 

[psemianonymous]

Yeah, if you close the database, it closes/stops any code you're running. There is a workaround, though. Michael Kaplan (of the Access newsgroup) made something to help you do exactly what you need: TSISOON

http://www.trigeminal.com/lang/1033/utility.asp?ItemID=8#8

quote: Have you ever wanted to close a database, then maybe compact it, and maybe open it back up, open up another database entirely, and maybe even run a procedure inside the database when it is opened? Well, if you are using Access 2000 or Access 97 you needn't look any further than build 3429 of our TSI SOON (Shut One, Open New) database add-in. Simply follow the instructions in the enclosed text file to register it and to get sample usage information. The latest feature that has been added is the ability to make the current database into an MDE.


The other option is to just have the server machine run a Task Scheduler process at midnight--you can assume no one will be in the database at that time.


I just re-read your thing above--is the Access application going to be open 24/7? If so, then #1 (my first suggestion) is feasible. If not, then #2 is by far easier.


Pete



--
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

Corrupt MDBs FAQ

http://www.granite.ab.ca/access/corruptmdbs.htm

 

[bubba100]

This is what we use to close the current database and open another.

Dim stAppName As String
stAppName = "MSAccess.exe ""C:\CompactDB.mdb"" "
Call Shell(stAppName, 1)
Docmd.Quit

What you might find is that it takes longer to close the current db than it does to open the other. Each open Access db has an ldb file in the same folder. As long as this ldb file exists you won't be able to compact the database. We had to put a timer with a loop in our code of the Compactdb to see if the ldb was still there. Once the first db finshes closing the ldb will disappear, then the code to compact can proceed.

Good Luck.