copy MDB to new name, while in use.

[Guest]

I have No idea where to begin, this may be a VB script or an ACCESS VBA
project, I would really appreciate any help I can get on this. I would like
to build a Script that can be run from a command button in ACCESS2003. The
Script needs to check to see if the TESTDB in a subfolder of the primary
database is in use;

If the TESTDB is in use then display a message that the adhoc DB is in use
would you like to create a temp adhocDB,
if YES then copy the current MDB to the subfolder and name it
adhoctemp%currentdate-time%.mdb, remove all the Start restrictions that were
in place by way of options from Tools/Startup on the Master DB and place the
use in the adhocDB. When the user closes this db they should be returned to
the master and the adhoc should be deleted.
If NO then do nothing
If the TESTDB is NOT in use then delete all data from the tables and
repopulate with data from the Master DB, remove all restrictions that were in
place by way of options from Tools/Startup on the Master DB and place the use
in the TESTDB. When the user closes this they should just be returned to the
master.

Just thinking about this scares me. Would someone out there please help me.
Thank you!!

 

[Douglas J. Steele]

I would advise very strongly against trying to copy the currently open
database. There's no guarantee that it'll end up in a consistent state.

What you could do is use the TransferDatabase method to copy the tables from
the current database to the other one.

To check whether a given database is in use, see whether the locking file
exists in the folder. In other words, if you're trying to determine whether
C:\Folder1\Folder2\TestDB.mdb is in use, look to see whether
C:\Folder1\Folder2\TestDB.ldb exists. You can check whether a file exists
using the Dir function:

If Len(Dir("C:\Folder1\Folder2\TestDB.ldb")) > 0 Then
' The file exists
Else
' The file does not exist
End If

 

[Guest]

WOW, Thank you so much for that rapid reply Doug!!! U pref Doug or Gouglas?
I had thought of the ldb file exsistance but I also know that file can be
left behind on occasion when the db has been exited in the wrong way. I was
just revising my question to this>>>

OK as I thought this over a bit more I have simplified it down to this. AND
again I would really appreciate anyone’s help in writing the code for
something like this. I also think it would all be VBA. I had thought VB
script might be needed if I were copying the MDB file, because I can copy and
paste that file all day from DOS or windows explorer, but I could not find
any VBA that would copy an active MDB.
Anyway here is the breakdown of what I am looking to do.
Thank you!!

If TESTDB.ldb then (or is there a better way to know if TESTDB is in use)
place the use in the TESTDB.
ELSE
(If the TESTDB is NOT in use) then delete all data from the tables and
repopulate with data from the Master DB, and then place the use in the TESTDB.
(Should the CompactDatabase be run on TESTDB after a purge and reload of
data?)
=====================================

 

[Douglas J. Steele]

While you're correct that the ldb file can be left behind in certain
conditions, I'm not aware of any other check you can make.

I'm not quite sure what your "place the use in the TESTDB" option is
supposed to do. The ELSE side, though, sounds correct. You definitely want
to run CompactDatabase on the TESTDB. In fact, if the tables are big, you
might want to compact after the purge, and then again after the load.

 

[David W. Fenton]

While you're correct that the ldb file can be left behind in
certain conditions, I'm not aware of any other check you can make.

Attempting to delete the LDB file would be a pretty good indication
that you shouldn't copy the MDB file. Even if there were no users in
the MDB file, if the LDB file is still locked by the file system
(because it's open) it might be an indication that something is
still marked as locked or suspect in the MDB file.

 

[WANNABE]

That would be a typing error,which should say USER not USE (open the database
and make it the current DB). So with that said can you help me to write the
code needed to accomplish this?
=====================================
While you're correct that the ldb file can be left behind in certain
conditions, I'm not aware of any other check you can make.

I'm not quite sure what your "place the use in the TESTDB" option is
supposed to do. The ELSE side, though, sounds correct. You definitely want
to run CompactDatabase on the TESTDB. In fact, if the tables are big, you
might want to compact after the purge, and then again after the load.