Solved

How to check if the database is already open

Posted on 2009-04-07
12
1,031 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

615 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