Test if other database is open

[OssieMac]

Can someone please give me an example of code to use in one database to test
if another database is open and if it is open then close it.

Access 2002 and 2003 if it makes a difference.

All help greatly appreciated.

 

[Alex Dybenko]

Hi,
to test it database open - you can try to open it exclusively using
dbengine.opnedatabase, or just try to rename it, if fail - then database
opened. Not sure that you can close it, as it can be opened via network on
another PC

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[John Spencer]

I've got this old code that you pass the full path to a database and it will
determine if it is running or not.

I'm not aware of a good method of shutting down one database from another
database.


Public Function TestDDELink(ByVal strAppName$) As Integer
'*******************************************
'Name: TestDDELink (Function)
'Purpose: IsDBAlreadyRunning
'Author: http://ourworld.compuserve.com/homepages/attac-cg/acgsoft.htm
'Date: April 11, 2000, 11:25:49 AM
'Output: True or False on whether Application is running
'*******************************************
' Sample Call: TestDDELink("T:\Newsgroup Answers_2K.mdb")
Dim varDDEChannel

On Error Resume Next

Application.SetOption ("Ignore DDE Requests"), True
varDDEChannel = DDEInitiate("MSAccess", strAppName)

' When the app isn't already running this will error
If Err Then
TestDDELink = False
Else
TestDDELink = True
DDETerminate varDDEChannel
DDETerminateAll
End If

Application.SetOption ("Ignore DDE Requests"), False

End Function

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[JP]

The way I do it is use the Dir function to see if there is a .ldb file
in the same folder as the Access database.

For example, if you have an Access db file "C:\MyDatabase.mdb", then
if someone else has it open, there will be a file called
"MyDatabase.ldb" in the same folder. So just use

If Dir("C:\MyDatabase.ldb") = "" Then
' your code to work with the database goes here
Else
Msgbox "Someone else has locked the file for editing", vbCritical
End If


HTH,
JP

 

[Tony Toews [MVP]]

Can someone please give me an example of code to use in one database to test
if another database is open and if it is open then close it.

It's one thing to see if someone else has it open as the other posters
have already given you some solutions. But closing Access from
another system programmatically is just about impossible.

Unless you put a flag in a table in the BE which the Access front end
MDB/MDE checks on a regular basis. If this suits then you should be
using a hidden startup form with a timer event set which checks this
every, say, 30 seconds. Also be aware that while working in VBA code
you want all such timers to be off as this causes interesting
weirdness. I use a bit of code on startup that checks to see if the
MDB/MDE running from either an MDB or MDE. If MDE then I activate the
timer.

BTW why are you asking? What is your goal?

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[OssieMac]

Thanks to everyone for the answers. All were helpfull in my learning curve
with Access. Knowing for sure what I can and can't do allows me to achieve my
goal. I'll simply abort the copy if open with a msgbox to the user to close
it.

Re Tony's question 'BTW why are you asking? What is your goal?'

Access 2002 does not have a backup feature and 2003, if used from the front
end, does not backup the back end, and that is the more important one because
I can restore front end from my copies. The user is currently using 2002 but
I understand she will be moving to another computer with 2003.

I am writing a separate Access application that is run from an icon on the
desktop and it simply copies the files to a backup folder and appends the
date and time to the filename. It also has a restore feature so that the date
and time to restore is selected from a combo. Under test the files appear to
copy quite satisfactorily while open but I am uncertain if there are any
circumstances under which the backup could become corrupted if database is
open. As for restoring back over a database that is open, I have not even
attempted. I guess that I just want to feel comfortable there there is a high
probability of always having good backups and good restores.

--
Regards,

OssieMac

 

[David W. Fenton]

I am writing a separate Access application that is run from an
icon on the desktop and it simply copies the files to a backup
folder and appends the date and time to the filename. It also has
a restore feature so that the date and time to restore is selected
from a combo. Under test the files appear to copy quite
satisfactorily while open but I am uncertain if there are any
circumstances under which the backup could become corrupted if
database is open. As for restoring back over a database that is
open, I have not even attempted. I guess that I just want to feel
comfortable there there is a high probability of always having
good backups and good restores.

You are treading on dangerous ground. A hot backup copy made via the
file system is never going to be 100% reliable. You need to use Jet
functionality to do the backup, since Jet will respect locks on the
data and create a backup file that has consistent and valid data in
it (though it may not have a few uncommitted data changes -- at most
it could be a few minutes out of date). There are two methods to
accomplish this:

1. copy all the tables to a new MDB via TransferDatabase. This
always seems too fussy to me.

2. open the back end and use:

Application.SaveAsText 6, vbNullString, [path/filename of new MDB]

I have never found a method for running this command except on the
currently opened MDB.

To restore the back end, you have to kick everybody out of the
database. The usual method for doing this is to have a timer form in
the front end that monitors a table in the back end. When a record
is added to that table indicating the users have to shut down, the
timer detects it and notifies the user to log out. If the user
doesn't log out in a suitable time frame, you walk through the forms
collection and save any dirty records and then close the app.

But I'd hesitate to do this, because what if new data has been added
in the current sessions? Are you just going to overwrite it with the
backup file?

I think you have major conceptual problems with the task you seem to
be wanting to accomplish.

 

[OssieMac]

Thanks for the reply David. I am not sure if I fully understand why "treading
on dangerous ground". I assume you mean by "Hot copy" to copy while in use.
If so, it is not my intention to do so even though I realize that it appears
to work. Possible corruption under these circumstances is my main cause for
concern and the reason why I want to know if the database is open.

The database is a single user system on a stand alone PC. I just don't want
the user to inadvertantly try to copy it while they have it open (maybe
minimised and forgot about it). If I can identify that it is open then I can
just put up a msgbox accordingly and exit and let them close the database and
try again.

Also the data entry is transcription from paper copies. There is a process
in place whereby all entries are filed in chronological order of entry to the
system. Date and time of completion of each batch is recorded on the top one
and this is the time when the backup should be done. This way if the data
becomes corrupted and unrecoverable then it should be easy to establish
exactly what has been lost. (The paper copies are not kept indefinitely but
long enough if some data had to be re-entered.)

I will be interested if you have reason to believe there could be valid
concerns for the security of the backup copy under these circumstances?

Thanks again for taking the time to reply.


--
Regards,

OssieMac

I am writing a separate Access application that is run from an
icon on the desktop and it simply copies the files to a backup
folder and appends the date and time to the filename. It also has
a restore feature so that the date and time to restore is selected
from a combo. Under test the files appear to copy quite
satisfactorily while open but I am uncertain if there are any
circumstances under which the backup could become corrupted if
database is open. As for restoring back over a database that is
open, I have not even attempted. I guess that I just want to feel
comfortable there there is a high probability of always having
good backups and good restores.

You are treading on dangerous ground. A hot backup copy made via the
file system is never going to be 100% reliable. You need to use Jet
functionality to do the backup, since Jet will respect locks on the
data and create a backup file that has consistent and valid data in
it (though it may not have a few uncommitted data changes -- at most
it could be a few minutes out of date). There are two methods to
accomplish this:

1. copy all the tables to a new MDB via TransferDatabase. This
always seems too fussy to me.

2. open the back end and use:

Application.SaveAsText 6, vbNullString, [path/filename of new MDB]

I have never found a method for running this command except on the
currently opened MDB.

To restore the back end, you have to kick everybody out of the
database. The usual method for doing this is to have a timer form in
the front end that monitors a table in the back end. When a record
is added to that table indicating the users have to shut down, the
timer detects it and notifies the user to log out. If the user
doesn't log out in a suitable time frame, you walk through the forms
collection and save any dirty records and then close the app.

But I'd hesitate to do this, because what if new data has been added
in the current sessions? Are you just going to overwrite it with the
backup file?

I think you have major conceptual problems with the task you seem to
be wanting to accomplish.

 

[David W. Fenton]

I will be interested if you have reason to believe there could be
valid concerns for the security of the backup copy under these
circumstances?

I don't understand how you think you're going to get Windows
Explorer to put up a message box.

 

[OssieMac]

Hi David,

It is not being done in Windows Explorer. My original question was "Can
someone please give me an example of code to use in one database to test if
another database is open?"

I simply wrote another Access application that is launched from an icon on
the desktop. It has an initialize button that allows the user to use Browse
to select the file to be copied and the folder for the backup. It then saves
that info in a table with one record with 2 fields and uses it as the
defaults so that after initial setup the user simply clicks the icon and the
backup is done. (The code tests that the file to be backed up exists and the
backup folder exists before attempting the copy.)

Anyway it is working to my satisfaction now. I just hope that it keeps doing
so and I am interested if anyone sees any dangers in the way I am achieving
it.

I appreciate your response and thanks again for your time.

 

[Tony Toews [MVP]]

I am writing a separate Access application that is run from an icon on the
desktop and it simply copies the files to a backup folder and appends the
date and time to the filename.

I've done something virtually the same. However I run it from the FE.
I did not create a separate MDB/MDE.

First thing I do is close all open forms and reports. I do not have
any recordsets in global variables so I don't have to worry about
those as I always use a hidden form bound to my Global Options table.

Then I rename the BE MDB and move it to the backup folder using the
VBA Rename function. Then I compact it back and start up the app
again.

If the rename fails then I know someone else is in the MDB, I display
a message accordingly and start up the app again.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/