Solved

How to check if the database is already open

Posted on 2009-04-07
12
991 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:clay258
  • 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:clay258
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
 
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:clay258
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 2

Author Comment

by:clay258
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:clay258
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:clay258
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:clay258
ID: 31567702
Cheers Gandalf
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

758 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now