• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1127
  • Last Modified:

How to check if the database is already open

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
Clay Willing
Asked:
Clay Willing
  • 6
  • 6
1 Solution
 
gandalf97Commented:
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
 
Clay WillingEng Ops MgrAuthor Commented:
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
 
gandalf97Commented:
I'll check my notes.  I'm not sure if it matters but what version of Access are you running?

Gandalf
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
gandalf97Commented:
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
 
Clay WillingEng Ops MgrAuthor Commented:
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
 
gandalf97Commented:
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
 
Clay WillingEng Ops MgrAuthor Commented:
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
 
gandalf97Commented:
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
 
gandalf97Commented:
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
 
Clay WillingEng Ops MgrAuthor Commented:
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
 
Clay WillingEng Ops MgrAuthor Commented:
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
 
Clay WillingEng Ops MgrAuthor Commented:
Cheers Gandalf
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 6
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now