Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to check if the database is already open

Posted on 2009-04-07
12
Medium Priority
?
1,053 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 200 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
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.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

722 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