Solved

How to check if the database is already open

Posted on 2009-04-07
12
1,008 Views
Last Modified: 2013-11-27
Hi All.

It seems like I am becoming reliant on you for answers but this one is eluding me.  I want to be able to set up windows task sheduler to run my database at 0645L every morning so that at 0700 it can open a report showing the days scheduled maintenance tasks and outstanding faults.  This I can do but the database is actually designed to run 24/7 so if the task scheduler opens a second instance I want the second to shut itself down

Any ideas?
0
Comment
Question by:Clay Willing
  • 6
  • 6
12 Comments
 
LVL 2

Expert Comment

by:gandalf97
ID: 24091012
If the database runs 24/7, why not stick a timer in the form and have it trigger the report?  That way you wouldn't have to mess with multiple instances of the database being open.

Just a thought.

Gandalf
0
 
LVL 2

Author Comment

by:Clay Willing
ID: 24091391
Hi Gandalf.

Yep the timer is going to trigger the event but I wanted to add the task schedule so that if anyone closed the database it would open automatically
0
 
LVL 2

Expert Comment

by:gandalf97
ID: 24091640
I'll check my notes.  I'm not sure if it matters but what version of Access are you running?

Gandalf
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 2

Expert Comment

by:gandalf97
ID: 24091905
One solution would be to have your scheduler attempt to open the database in "Exclusive" mode.  If it fails, then it is already open.  If this is a single user database, this will work.  If not, then other users will be locked out once started by the scheduler.  So is this a single user database or will other people have it open at the same time?

I'm trying to give you alternative ways to solve the problem than what you asked for because the solution I saw was from Access 2.0 days and it involved tricky code to look at existing process info and if it found another instance it killed itself.  It wasn't pretty and could lead to corruption of the database.  So, in the absence of a neater solution, I'd try "Exclusive Mode".

Let me know whether this will work for you.

Regards,
Gandalf
0
 
LVL 2

Author Comment

by:Clay Willing
ID: 24092484
Hi Gandalf,  
Sorry went off to get some dinner mid night shift.  Little background then.  Designing in Access2007 with the database default/compatibility set to Acess2003, which is causing difficulties of its own but that' another story but theres no turning back now and I dont have an earlier copy of office.  This will be a multi user database though with each user having a copy of the front end and the back end on either a server or a locked down workgroup PC.

0
 
LVL 2

Expert Comment

by:gandalf97
ID: 24096979
No problem.  I just wanted to know your version because how Access handles the lock file (ldb) changed over time.  Since you split the code and data and each user has their own copy of the front end, this should not be an issue.

Have you tried having the scheduler open the front-end database on your machine in exclusive mode?  Try scheduling a test in 5 minutes while leaving the front end open.  It should fail.  Try it again with your copy closed.  Let me know what happens.

Regards,
Gandalf
0
 
LVL 2

Author Comment

by:Clay Willing
ID: 24097543
going to need a pointer on how to open in exclusive mode.  wont that restrict others or does it only apply to the front end thats running on the users pc?
0
 
LVL 2

Expert Comment

by:gandalf97
ID: 24097873
If you open your copy of the front end it should only affect you.  I don't normally open my databases this way but you can add the /excl switch to the command line to open in exclusive mode.  I am assuming this is how you are invoking the database in the scheduler.  If not, please let me know.

Regards,
Gandalf
0
 
LVL 2

Accepted Solution

by:
gandalf97 earned 50 total points
ID: 24098129
I just tried this myself.  I created a database (db1.mdb) in my MyDocuments folder and left it open.  I went to the scheduler and scheduled a task to open the database and modified the string: "C:\Documents and Settings\mylogin\My Documents\db1.mdb" to "C:\Documents and Settings\mylogin\My Documents\db1.mdb \excl".  I set the task to execute in 2 minutes and watched the scheduled task window.  Sure enough, right on time the status changed to "could not start".  I tried closing the database and rescheduling but it wouldn't open.  I'm thinking this is because of settings I have on that test database that you might not.  Try my test and see if it works for you.  If it does, great!  If not, let me know and I'll try to figure out what is going wrong.

Regards,
Gandalf

0
 
LVL 2

Author Comment

by:Clay Willing
ID: 24098392
Thankis gandalf that  /excl switch makes sense.   Cant see why your db didnt open I will give it a try later.  Thanks for the ongoing help.  will get back to you.
0
 
LVL 2

Author Comment

by:Clay Willing
ID: 24107273
Hey Gandalf,

It worked no probs!  Thats one step closer to a worry free deployment.  Couldn't try it out last night as the girly was at my place.  Somethings have to take priority.

Once again thanks for the help.  I try really hard not to ask questions on EE as I don't to become reliant but sometimes it's necessary.  I'll keep an eye out to help others now I have battled through my first corporate database.
Clay
0
 
LVL 2

Author Closing Comment

by:Clay Willing
ID: 31567702
Cheers Gandalf
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question