Procedure run (Access open / Close)

[Niklas Östergren]

Hi!

I´m about to create a procedure which needs to be run after a specific date,
which the user select (ex. 1 of september each year).

I have been thinking a little bit of how to accomplishe this and where
thinking of running the procedure each time when Access opens with help of a
macro called "Autoexec". But this whill not trigger the procedure to run if
the user never closes the application.

Anyone have any idéa of how I solve this?

TIA!
// Niklas

 

[Tom Wickerath]

Hi Niklas,

If the user never closes the application, then close it for them. You shouldn't allow users to
keep shared Access databases open for extended periods of time. You might want to check out the
following KB article:

HOW TO: Detect User Idle Time or Inactivity in Access 2000
http://support.microsoft.com/?id=210297

I recommend setting the Const IDLEMINUTES constant to something more reasonable than 1 minute. I
use a setting of 15 minutes in my applications. Thus, if there is no user activity for 15
minutes, the database just gracefully closes on its own.

You can insert code in the Form_Timer() event procedure to check for a certain date, and run a
procedure if the date condition is met. You'd probably also want to store this result in a local
table, using code to see if the procedure had been run by querying the table, so that your
procedure would only be run once after 1 Sep., instead of being run every time the From_Timer
code runs. What kind of procedure is this, anyways?

Okay, off-topic question for you again....
You wrote back yesterday:

****************************
"Hi Tom!
Yes finaly I could monitor the third post. Why I could´nt see it I don´t
know. I tryed from three different pc:s with two different internet
connections. And after trying to post it from my pc at home with a simple
56kb modem it worked. Strange!

Thank´s for helping out though!
****************************

but you didn't indicate if the answer I provided to the earlier message worked out for you.


Tom
__________________________________


Hi!

I´m about to create a procedure which needs to be run after a specific date,
which the user select (ex. 1 of september each year).

I have been thinking a little bit of how to accomplishe this and where
thinking of running the procedure each time when Access opens with help of a
macro called "Autoexec". But this whill not trigger the procedure to run if
the user never closes the application.

Anyone have any idéa of how I solve this?

TIA!
// Niklas

 

[Niklas Östrergren]

Sorry again Tom!

Yes! your help was what I needed. I´m sorry about that but there are so much
things going on in my head at the moment! I think that I´m not alone (I´m
thinking of the disaster and at the same time trying to develop my
application). At the moment I´m trying to raise some money from our
assosiation to help the Swedish children who´s parent´s are lost. There are
a Swedish man, in Pukhet, who volantaraly, have been taken care of some of
those victims and I´m trying to get in contact with him to see if money can
help him out with this!

Talke about "Off topic"!

Back to your answere and my Q:

I have been thinking of closing my application automaticly but didn´t know
how to do that. So I´ll read through the article and will get back later on
when I have tryed it out (which may take some while since I havn´t developed
the procedure yet).

I will have several variables stored in the backend of my db, to be used for
this procedure, since the procedure are going to create new records for
people with memberships free of charge and I don´t want this procedure to be
run more than once no matter from which frontend it´s initiated.

I will have a StartDate from which the procedure will start to ask the user
if this procedure shall be run. I will have a Boolean variable holding the
result IF the procedure have been run or not. And I´ll need a Integer
variable holding the value of how many times the user are alowed to answere
"No" before I automaticly runs the procedure. And I´ll need a Boolean
variable so the user can select if they want this question to be presented
to them or not. If NOT the procedure will run automaticly without the user
knowing it. BUT I´ll display a MsgBox informing the user that it allready
HAVE been running. And finaly I need a Boolean variable so the user can turn
this option ON/OFF.

That´s what I have been comming up with so far. Maby it´s to many variables
for this procedure but I have been asking the user of the old application
that I wrote 1999 (which is verry bad if you ask me). And they don´t know
how they´d like this to work so I have to build in a dynamic function
letting them to chose later on whoch this shall work. or am I totally out in
the blue!

Thank´s for helping out Tom I realy appreciate it!

// Niklas

 

[Tom Wickerath]

Hi Niklas,

I'm glad to learn that the help I provided on the subject of validating input data was useful. It
took me quite an effort to type up that answer, so it's nice to know that it worked out for you.
If you are referring to the recent tsunami disaster in Asia, you have many people throughout the
world, including me, who are praying for the victims.

Maybe it´s too many variables for this procedure

It's starting to sound that way to me....

and I don´t want this procedure to be run more than
once no matter from which frontend it´s initiated.

How about storing the critical date in a one-record table. In your startup form, or function
called by an autoexec macro, you can check this date. If it is past that date, then simply
present a message box informing the user that the database cannot be used until some maintenance
work is performed by the database administrator, and to please call Niklas at {insert phone
number} for further details. Follow this message box statement up with a DoCmd.Quit command. It
won't take too many phone calls before you, or whoever's name and phone number that you include
in the message box, takes the requisite action.

Let the DBA decide when to initiate this one-time procedure. They can then update this date field
in the back-end database as a part of this maintenance. It will also provide them an opportunity
to make a clean back-up copy of the back-end database, and do a compact and repair on it as well
(although they should probably do this on a much more frequent basis anyways).

....but I have been asking the user of the old application
that I wrote 1999 (which is verry bad if you ask me).

Tell you what...I will volunteer to do an analysis of your database, using Total Access Analyzer,
version 10.5 (http://www.fmsinc.com/products/analyzer/index.html) if you are willing to send me a
copy by private e-mail. I do not need any of the data. You can delete all records. Please
compact and zip the file if you want to take me up on this offer. If this is a secured database,
then I will need the admin. password and a copy of the .mdw file as well.


Tom
__________________________________


Sorry again Tom!

Yes! your help was what I needed. I´m sorry about that but there are so much
things going on in my head at the moment! I think that I´m not alone (I´m
thinking of the disaster and at the same time trying to develop my
application). At the moment I´m trying to raise some money from our
assosiation to help the Swedish children who´s parent´s are lost. There are
a Swedish man, in Pukhet, who volantaraly, have been taken care of some of
those victims and I´m trying to get in contact with him to see if money can
help him out with this!

Talke about "Off topic"!

Back to your answere and my Q:

I have been thinking of closing my application automaticly but didn´t know
how to do that. So I´ll read through the article and will get back later on
when I have tryed it out (which may take some while since I havn´t developed
the procedure yet).

I will have several variables stored in the backend of my db, to be used for
this procedure, since the procedure are going to create new records for
people with memberships free of charge and I don´t want this procedure to be
run more than once no matter from which frontend it´s initiated.

I will have a StartDate from which the procedure will start to ask the user
if this procedure shall be run. I will have a Boolean variable holding the
result IF the procedure have been run or not. And I´ll need a Integer
variable holding the value of how many times the user are alowed to answere
"No" before I automaticly runs the procedure. And I´ll need a Boolean
variable so the user can select if they want this question to be presented
to them or not. If NOT the procedure will run automaticly without the user
knowing it. BUT I´ll display a MsgBox informing the user that it allready
HAVE been running. And finaly I need a Boolean variable so the user can turn
this option ON/OFF.

That´s what I have been comming up with so far. Maby it´s to many variables
for this procedure but I have been asking the user of the old application
that I wrote 1999 (which is verry bad if you ask me). And they don´t know
how they´d like this to work so I have to build in a dynamic function
letting them to chose later on whoch this shall work. or am I totally out in
the blue!

Thank´s for helping out Tom I realy appreciate it!

// Niklas

 

[Niklas Östrergren]

Hi Tom!

Reguarding the help you are offering me, with my old application, I´m happy
to hear that you apply some help to me. And maby I will send you a copy. I
have to think about it first and to look through the old application first.

I did take a closer look at it last spring and found out that I did think it
was easyer to creat a completly new db than to modifye the old one. There
are some major misstakes made in the table design and relationships which I
now have fixed in the new application. I have also learned more about VBA
and developing and today know sevel way´s which makes an application much
more user friendly.

What I´ll do when I´m finished with this application, containing, more than
50 tables (some of them apr. 10 pcs. are LookUpTables). Is that I´ll
transfere the old data, stored since 1999, into the new application. So our
assosiation will not loose any data.

"and I don´t want this procedure to be run more than once no matter from
which frontend it´s initiated."
Maby I was a little bit uncleare about what I meant. The procedure shall be
run once every year but not more.

And one of the things that the user´s have compalined about, in the old
application, is that they need me to do things that they should be able to
do. Which is true. And that´s what I´m trying to avoid in the new
application.

So I have created a maintenance form for the assosiation in which the user
can change some variables used in my code to do some tasks that the user
want to be done more or less frequently. One of these are when a user add a
new member apply and the memberfee is payed after a breakdate (which can be
changed by the user) then shall an extra membership for the next membership
period (next year) be created automaticly free of charge. This is one of
these demands that needs to be possible to change by the user since the date
may be changed in the future (today it´s 1:st of september each year).

So I thought that I´ll do the same thing with this procedure, witch creates
new memberships records for people with a membership tyme which is free of
charge. This needs to be created before the assosiation sends out
paymentcards (bills) so we don´t accidently sned bills to those people which
shouldn´t pay.

About you offer I´ll think about it Tom and will get back to you later if
I´ll go for it! Meanwhile Tanks a lot for helping out even this time.

I did try the DetectIdleTime_Form and it works just fine!

And yes! I was thinking of the Tsunami disaster!

// Niklas

 

[Tom Wickerath]

Hi Niklas,

I'd be happy to perform the analysis of your current version if you'd prefer. Just send me a copy
before you load any of the data into it.

I did take a closer look at it last spring and found out that I did think it
was easy to create a completely new db than to modify the old one.

Probably a wise decision. You've followed advice that database design expert and author, Michael
Hernandez, has written about and included in his lectures many times:
http://www.datatexcg.com/Downloads/DatabaseDesignTips1997.pdf


"and I don´t want this procedure to be run more than once no matter from
which frontend it´s initiated."
Maybe I was a little bit unclear about what I meant. The procedure shall be
run once every year but not more.

This is exactly how I understood the issue. My recommendation is that the DBA be the one who runs
this procedure, since it only needs to be run one time per year. The idea to implement code that
limits the ability to open the database simply serves as a safety valve, in case the DBA forgets
to run this procedure at the required time.

I did try the DetectIdleTime_Form and it works just fine!

Great.

Unnecessary database corruption can be caused by users who leave the database open longer than
necessary. If they are in the middle of editing a record (ie. record is marked dirty and pencil
symbol is displayed if form record selector is visible) and they decide to go to lunch, they will
leave this record locked for other users. They'll likely leave a whole page of records locked as
well, unless you go the extra step of implementing code to ensure that true record-level locking
is obtained:

PRB: Jet 4.0 Row-Level Locking Is Not Available with DAO 3.60
http://support.microsoft.com/?id=306435

If the power goes out while they are in the middle of editing such a record, or their PC locks up
and they have to Ctrl-Alt-Del, etc., this can cause the back-end to become corrupted. Much less
chance of that happening if you gracefully kick them out if they're not actively using the
database. Access is not very tolerant about minor glitches, so it's best to not let people
maintain open connections to a back-end database for extended periods of time.


Tom
_________________________________


Hi Tom!

Reguarding the help you are offering me, with my old application, I´m happy
to hear that you apply some help to me. And maby I will send you a copy. I
have to think about it first and to look through the old application first.

I did take a closer look at it last spring and found out that I did think it
was easyer to creat a completly new db than to modifye the old one. There
are some major misstakes made in the table design and relationships which I
now have fixed in the new application. I have also learned more about VBA
and developing and today know sevel way´s which makes an application much
more user friendly.

What I´ll do when I´m finished with this application, containing, more than
50 tables (some of them apr. 10 pcs. are LookUpTables). Is that I´ll
transfere the old data, stored since 1999, into the new application. So our
assosiation will not loose any data.

"and I don´t want this procedure to be run more than once no matter from
which frontend it´s initiated."
Maby I was a little bit uncleare about what I meant. The procedure shall be
run once every year but not more.

And one of the things that the user´s have compalined about, in the old
application, is that they need me to do things that they should be able to
do. Which is true. And that´s what I´m trying to avoid in the new
application.

So I have created a maintenance form for the assosiation in which the user
can change some variables used in my code to do some tasks that the user
want to be done more or less frequently. One of these are when a user add a
new member apply and the memberfee is payed after a breakdate (which can be
changed by the user) then shall an extra membership for the next membership
period (next year) be created automaticly free of charge. This is one of
these demands that needs to be possible to change by the user since the date
may be changed in the future (today it´s 1:st of september each year).

So I thought that I´ll do the same thing with this procedure, witch creates
new memberships records for people with a membership tyme which is free of
charge. This needs to be created before the assosiation sends out
paymentcards (bills) so we don´t accidently sned bills to those people which
shouldn´t pay.

About you offer I´ll think about it Tom and will get back to you later if
I´ll go for it! Meanwhile Tanks a lot for helping out even this time.

I did try the DetectIdleTime_Form and it works just fine!

And yes! I was thinking of the Tsunami disaster!

// Niklas

 

[Niklas Östrergren]

Hi Tom!

I´m not sure I follow you, reguarding the Row/Record locking thing!

Do you sugest that I try to implement code to ensure that true record-level
locking is obtained instead of safely close the application?

Or do I understand this correct if I say that when I close the application I
also save records beeing edited when I use (Application.Quit acSaveYes) and
that this should be enough?

Best Reguards!
// Niklas
P.S I have tryed to send you the answere by mail but the e-mail address was
invalid!

 

[Tom Wickerath]

Hi Niklas,

I´m not sure I follow you, regarding the Row/Record locking thing!

If you open up Tools > Options... and click on the Advanced tab, you will see an option that
reads "Open databases using record-level locking". This was one of the new features advertised
by Microsoft with the release of Access 2000. It would be great if it actually worked, but it
doesn't. If you simply check off the option, you might think that you have achieved true
record-level locking. The KB article that I pointed you to indicates otherwise. You need to jump
through some ADO hoops first, to actually get to the promised land of record-level locking.

Do you suggest that I try to implement code to ensure that true
record-level locking is obtained ...

Yes. I recommend this for any database with a shared JET (.mdb) back-end file.

....instead of safely close the application?

In addition to safely closing the application (not instead of).

Or do I understand this correct if I say that when I close the
application I also save records being edited when I use
(Application.Quit acSaveYes) and that this should be enough?

Yes, the record will be saved irregardless of whether or not you implement the code in KB 306435.
The two ideas are not really related. The code in KB 306435 simply helps you achieve true record
level locking. Without it, a user is likely to lock more than just the record they are currently
editing, if they are using a bound form. Basically, they will lock a page of records. The number
of records actually locked depends on the number of bytes that each record requires. In JET 4, a
page of records is 4096 bytes (I'm pretty sure, without spending the time to look it up). All I
was trying to say was that a user can lock more than just the one record that they are editing,
when they are using a bound form, unless the extra step is taken to ensure true record level
locking. In a well-behaved multi-user database, you really don't want records that are not
currently being edited to get locked.

P.S I have tried to send you the answer by mail but the e-mail
address was invalid!

My temporary e-mail address is pretty easy to figure out when you look at it:
[email protected]

You need to remove the part that reads "RemoveThisSpamBlock". You should end up with an 18
character e-mail address. Sorry, I don't want to post the real address, even if it is a
disposable account, because this would make it too easy for the scum sucking spammers to harvest
the address.

Tom
_________________________________


Hi Tom!

I´m not sure I follow you, reguarding the Row/Record locking thing!

Do you sugest that I try to implement code to ensure that true record-level
locking is obtained instead of safely close the application?

Or do I understand this correct if I say that when I close the application I
also save records beeing edited when I use (Application.Quit acSaveYes) and
that this should be enough?

Best Reguards!
// Niklas
P.S I have tryed to send you the answere by mail but the e-mail address was
invalid!

_________________________________

 

[Tom Wickerath]

Yes, the record will be saved irregardless of whether or not you implement the code in KB

306435.

Of course, the record will only be saved as long as data is available for all required fields,
and any validation rules are satisfied.

Tom
_____________________________________


Hi Niklas,

I´m not sure I follow you, regarding the Row/Record locking thing!

If you open up Tools > Options... and click on the Advanced tab, you will see an option that
reads "Open databases using record-level locking". This was one of the new features advertised
by Microsoft with the release of Access 2000. It would be great if it actually worked, but it
doesn't. If you simply check off the option, you might think that you have achieved true
record-level locking. The KB article that I pointed you to indicates otherwise. You need to jump
through some ADO hoops first, to actually get to the promised land of record-level locking.

Do you suggest that I try to implement code to ensure that true
record-level locking is obtained ...

Yes. I recommend this for any database with a shared JET (.mdb) back-end file.

....instead of safely close the application?

In addition to safely closing the application (not instead of).

Or do I understand this correct if I say that when I close the
application I also save records being edited when I use
(Application.Quit acSaveYes) and that this should be enough?

Yes, the record will be saved irregardless of whether or not you implement the code in KB 306435.
The two ideas are not really related. The code in KB 306435 simply helps you achieve true record
level locking. Without it, a user is likely to lock more than just the record they are currently
editing, if they are using a bound form. Basically, they will lock a page of records. The number
of records actually locked depends on the number of bytes that each record requires. In JET 4, a
page of records is 4096 bytes (I'm pretty sure, without spending the time to look it up). All I
was trying to say was that a user can lock more than just the one record that they are editing,
when they are using a bound form, unless the extra step is taken to ensure true record level
locking. In a well-behaved multi-user database, you really don't want records that are not
currently being edited to get locked.

P.S I have tried to send you the answer by mail but the e-mail
address was invalid!

My temporary e-mail address is pretty easy to figure out when you look at it:
[email protected]

You need to remove the part that reads "RemoveThisSpamBlock". You should end up with an 18
character e-mail address. Sorry, I don't want to post the real address, even if it is a
disposable account, because this would make it too easy for the scum sucking spammers to harvest
the address.

Tom
_________________________________


Hi Tom!

I´m not sure I follow you, reguarding the Row/Record locking thing!

Do you sugest that I try to implement code to ensure that true record-level
locking is obtained instead of safely close the application?

Or do I understand this correct if I say that when I close the application I
also save records beeing edited when I use (Application.Quit acSaveYes) and
that this should be enough?

Best Reguards!
// Niklas
P.S I have tryed to send you the answere by mail but the e-mail address was
invalid!

_________________________________

 

[Niklas Östrergren]

Thank´s a lot for all help Tom!

I´ll read through the KB 306435 later on.

Thank´s a lot for your help!

// Niklas