How do you determine if anyone is logged onto a BE database?

[Jeff]

I have a system on a network and want to determine if anyone is currently
connected to the back-end files.

An interesting twist is that I have noticed that some users can be connected
(have the front end open at the first form) and even though this links to
the back-end files, there are no ldb files created.

This is so I know when it is safe to compact the back-end files without
going round to make sure everyone is logged off. User level security is in
place.

Thanks
Jeff

[Mark]

Jeff,
I'm no expert but my understanding is that you can have the frontend op
and it will not affect the backend if the forms that are open are not bound
to a table from the backend. I'm pretty sure that if there is no lbd file
present for the backend, no-one will be connected to it.

Mark
"Jeff" <je************@asken.com.au> wrote in message
news:43**********************@per-qv1-newsreader-01.iinet.net.au...

I have a system on a network and want to determine if anyone is currently
connected to the back-end files.

An interesting twist is that I have noticed that some users can be
connected (have the front end open at the first form) and even though this
links to the back-end files, there are no ldb files created.

This is so I know when it is safe to compact the back-end files without
going round to make sure everyone is logged off. User level security is in
place.

Thanks
Jeff

[TC]

Why not just try to compact it & handle the error? If it works, it
worked. If it didn't, you can print a tailored message & then try again
later.

HTH,
TC

[Lyle Fairfield]

This used to work in the olden (access 97 days).

Private Function CanBeOpenedExclusively(ByVal FullPath As String) As
Boolean
Dim d As Database
Dim p As PrivDBEngine
Set p = New PrivDBEngine
On Error Resume Next
Set d = p(0).OpenDatabase(FullPath, True)
CanBeOpenedExclusively = Not (d Is Nothing)
p(0).Close
Set d = Nothing
Set p = Nothing
End Function

[BillCo]

if there's no ldb on the back end then there are no table locks and the
databse can be compacted. Even if a user is logged into the front end,
they may not have any record locks as they have no tables, queries,
recordsets etc open to the backend. If the user tries to do something
while you are compacting they might get an error message and pick up
the phone and bark at you a little... but thats a fact of life as an
access administrator :)

a good trick to use is make a copy of the backend, compact the copy and
then quickly swap it for the old be... theres less of a time window
that way

always always always make a copy of the backend before compacting it on
the fly...

always!

[David W. Fenton]

"Jeff" <je************@asken.com.au> wrote in
news:43**********************@per-qv1-newsreader-01.iinet.net.au:

I have a system on a network and want to determine if anyone is
currently connected to the back-end files.

An interesting twist is that I have noticed that some users can be
connected (have the front end open at the first form) and even
though this links to the back-end files, there are no ldb files
created.

This is so I know when it is safe to compact the back-end files
without going round to make sure everyone is logged off. User
level security is in place.

There are two methods:

1. use the LDBUSR.DLL. You should search the Microsoft Knowledge
Base for that file name.

2. use the ADO UserRoster function.

BTW, I would definitely want to check before attempting to compact,
rather than just raising the error and handling it, as TC suggests.

There are lots of things you could do with the user information,
such as message the users who are logged in and ask them to log out.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Ted]

Jeff:
I have an application in which I just added a table to the Back End,
and code in the Front End which updates the BE Table to show who is
logged in and who is logged out of the application.
That of course isn't accurate if someone has just rebooted their
computer (in which case they would not have been marked as logged
out). So I am going to enter an On Timer event of my Main Menu form
(which only has buttons to open up other forms). The On Timer event,
which I think I will set for 5 minute intervals, will update the BE
table to show the last time it "checked in" with the BE to tell it they
are still logged in. That way if someone has not updated a field in the
table within a five minute time period, it can be assumed they are not
logged in.
I have noticed that when a user tries to update information on a form
that has a timer even on it, they are usually sent back to the first
field in the Tab Order on the form. This can be quite irritating. Since
I only use the Main Menu to show command buttons to open other forms,
and I HIDE the Main Menu rather than close it, the On Timer event will
fire for the Main Menu form and should not effect whatever form I am
working in. In fact I shall write that and check it out now, while I am
thinking about it.
In the BE table, I identify the computer updating its logon information
by using the computer's name, not the CurrentUser() which could be
Admin on the computer or the network logon name. My BE table serves to
control how many computers can use the application at any one time (for
licensing reinforcement).
Here is the BE table:
tblActivity
User txt 50 and Primary Key to avoid duplicate entries
LogDate Short Date
LogTime Short Time
Status txt 3 (either IN or OUT)
LastCheckIn Short Time

This code section Logs the user in when they open the application. Call
it from your Main Menu OnOpen event or when your Splash Screen opens.:
Dim intMaxUsers As Integer
Dim CRS1 As ADODB.Recordset
Dim strCmd As String
Dim intUsers As Integer
Dim intUserCount As Integer
Dim intFoundUser As Integer
intMaxUsers = CheckMaxLicUsers()
Set CRS1 = New ADODB.Recordset
CRS1.Open "tblActivity", CurrentProject.Connection, adOpenKeyset,
adLockReadOnly
intUserCount = CRS1.RecordCount
If CRS1.RecordCount > 0 Then
CRS1.MoveLast
intUsers = CRS1.RecordCount
CRS1.MoveFirst
End If
While Not CRS1.EOF
If CRS1.Fields("User") = strUser Then
intFoundUser = 1
'MsgBox "Found User"
End If
CRS1.MoveNext
Wend
CRS1.Close
If intUserCount < intMaxUsers And intFoundUser = 0 Then
LogActivity = 1
CRS1.Open "tblActivity", CurrentProject.Connection,
adOpenKeyset, adLockOptimistic
CRS1.AddNew
CRS1.Fields("User") = strUser
CRS1.Fields("LogDate") = Format(Now(), "mm/dd/yyyy")
CRS1.Fields("LogTime") = Format(Now(), "hh:mm:ss")
CRS1.Fields("Status") = "IN"
CRS1.Update
CRS1.Close
Else
If intUserCount > intMaxUser And intFoundUser = 0 Then
LogActivity = 0
Else
LogActivity = 3
strCmd = "SELECT * FROM tblActivity WHERE User = '" &
strUser & "'"
CRS1.Open strCmd, CurrentProject.Connection, adOpenKeyset,
adLockOptimistic
CRS1.Fields("LogDate") = Format(Now(), "mm/dd/yyyy")
CRS1.Fields("LogTime") = Format(Now(), "hh:mm:ss")
CRS1.Fields("Status") = "IN"
CRS1.Update
CRS1.Close
End If
End If
Set CRS1 = Nothing

The following code is in the OnTimer event of the Main Menu form:
OnTimer Event: Call UpdateUserActivity(fOSMachineName())
Function UpdateUserActivity(strUser As String) As Integer
Dim CRS1 As ADODB.Recordset
Dim strCmd As String

Set CRS1 = New ADODB.Recordset
strCmd = "SELECT * FROM tblActivity WHERE User = '" & strUser & "'"
CRS1.Open strCmd, CurrentProject.Connection, adOpenKeyset,
adLockOptimistic
CRS1.Fields("LastCheckIn") = Format(Now(), "hh:mm:ss")
CRS1.Update
CRS1.Close
Set CRS1 = Nothing
End Function

I've also created a form bound to the tblActivity which show me all of
the computers that have logged in to the application (and therefore are
connected to the BE), what date and time they logged IN or OUT, and the
last time their computer checked in with the activity log.

Jeff wrote:

I have a system on a network and want to determine if anyone is currently
connected to the back-end files.

An interesting twist is that I have noticed that some users can be connected
(have the front end open at the first form) and even though this links to
the back-end files, there are no ldb files created.

This is so I know when it is safe to compact the back-end files without
going round to make sure everyone is logged off. User level security is in
place.

Thanks
Jeff

[Ted]

By the way, using this same Check IN and Check OUT method, you could
add a field to the tblActivity and modify the code that checks in to
look for a certain value in that field.
If a certain value is in the field, cause each user's application to
open up and form that will display a message telling the user to log
off immediately. If they are not at their computer during that time,
have the form time out and with the OnTimer event Quit the application.
That would automatically close each user's application within whatever
time periods you have set up for each function (Example: The update
activity function runs every five minutes and the time out form times
out in five minutes. At the most you will have to wait only ten minutes
from the time you issue to log out command until all of the computers
have shut down the application.)
Does that make any sense? It does to me, but sometimes it is difficult
to put ideas into words.

[TC]

David W. Fenton wrote:

There are two methods:

1. use the LDBUSR.DLL. You should search the Microsoft Knowledge
Base for that file name.

2. use the ADO UserRoster function.

There are three methods. The third is to attempt to compact it & trap
the error if one occurs.

TC

[David W. Fenton]

"TC" <aa**********@yahoo.com> wrote in
news:11**********************@o13g2000cwo.googlegr oups.com:

David W. Fenton wrote:

There are two methods:

1. use the LDBUSR.DLL. You should search the Microsoft Knowledge
Base for that file name.

2. use the ADO UserRoster function.

There are three methods. The third is to attempt to compact it &
trap the error if one occurs.

That won't tell you who is using it, which was the original
question, and has utility in itself.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[TC]

David W. Fenton wrote:

That won't tell you who is using it, which was the original question, and has utility in itself.

I agree that it is useful to know who has the database open. But the
original question was:

"I have a system on a network and want to determine if anyone is
currently connected to the back-end files ... This is so I know when it
is safe to compact the back-end files without going round to make sure
everyone is logged off."

One way "to determine if anyone is currently connected to the back-end
files", and "to know when it is safe to compact the back-end files", is
to just try and compact the file, and trap the error if one occurs.

TC

[Jeff]

Thanks guys.

You gave me plenty to consider.

I think I will go with Lyles suggestion for now. This seems to work great in
2000 and provides enough protection.

However, I am also going to delve into determining users currently logged in
and sending a message to them to log out, but also have a timer that logs
them out after a period. This just worries me at this time - they may be in
the middle of changing data and have gone to lunch, as happens. You can tell
them never to leave a form open during an edit, but who listens.

Jeff

"TC" <aa**********@yahoo.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

David W. Fenton wrote:

That won't tell you who is using it, which was the original question, and
has utility in itself.

I agree that it is useful to know who has the database open. But the
original question was:

"I have a system on a network and want to determine if anyone is
currently connected to the back-end files ... This is so I know when it
is safe to compact the back-end files without going round to make sure
everyone is logged off."

One way "to determine if anyone is currently connected to the back-end
files", and "to know when it is safe to compact the back-end files", is
to just try and compact the file, and trap the error if one occurs.

TC

[Jeff]

Thanks Lyle

Still works great in 2000 and I will be using your suggestion.

Jeff

"Lyle Fairfield" <ly***********@aim.com> wrote in message
news:11*********************@g47g2000cwa.googlegro ups.com...

This used to work in the olden (access 97 days).

Private Function CanBeOpenedExclusively(ByVal FullPath As String) As
Boolean
Dim d As Database
Dim p As PrivDBEngine
Set p = New PrivDBEngine
On Error Resume Next
Set d = p(0).OpenDatabase(FullPath, True)
CanBeOpenedExclusively = Not (d Is Nothing)
p(0).Close
Set d = Nothing
Set p = Nothing
End Function

[TC]

Just remember with Lyle's method - it might return "ok" at instant #1,
but a database open might succeed at instant #2, before you actually
start the compact at instant #3.

(I'm not saying that someone could double-click the FE between instant
#1 and instant #3. I'm saying that they could have done that a few
moments before, and the Access startup process did not get around to
opening the back-end file until instant #2 - by coincidence, as it
were.)

There's no locking or single-threading process that would stop this
happening. So in theory, it /could/ happen. So the compact could fail,
even if you used Lyle's check. So you'd have to put some error handling
around the compact, to manage that. So the preliminary check actually
does not achieve anything, and you might as well rely 100% on the error
handler around the compact!

I'm not trying to criticise Lyle's code, I'm sure that it would work
perfectly, 99.9% of the time. But personally, I always code for the
remaining 0.1% of cases. Those are the ones that cause support
headaches that can be really hard to track down. I make sure to handle
those cases, right from the start, so I'm confidend they will /never/
occur.

HTH,
TC

[Lyle Fairfield]

I think you have this confused with the second coming:

Behold, I show you a mystery; we shall not all sleep, but we shall all
be changed, in a moment, in the twinkling of an eye, at the last trump:
for the trumpet shall sound, and the dead shall be raised
incorruptible, and we shall be changed. For this corruptible must put
on incorruption, and this mortal must put on immortality. So when this
corruptible shall have put on incorruption, and this mortal shall have
put on immortality, then shall be brought to pass the saying that is
written, Death is swallowed up in victory. O death, where is thy sting?
O grave, where is thy victory?
- First Corinthians 15:51-55

In preparation for this I always orient my hard drives so that they
open to the east. So far this has prevented that 0.1 % chance from ever
happening in the 8 or 9 years that the function has been in use by
about 70 users, two or three hundred times a year. And if it does
happen on that day of judgement, I suppose the mdb will be swallowed up
too, and I suppose I won't care a lot then.

HTH

Lyle

[TC]

What can I say? If I have a choice between writing code that works
100% of the time, and code that works 99.9% of the time, I'll choose
the former - not the latter.

Lyle, I'm really not trying to have a go at you or you code. But I've
done loads of system programming over the years, where tiny timing
problems can have disasterous consequences, that are very hard to track
down. So I'm always very conscious of timing issues in all the code I
read, or write.

Your code has a timing issue. I suggested a simple fix. Whether the
problem happens once a day, once a week, or once in three millenia, is
irrelevant, IMHO, if the problem is easy to fix.

Cheers,
TC

[Jeff]

IMHO, 99.9% is more than enough in this case. The situation is one where the
person doing the compacting will do this at a suitable time when there
should be no one connected and active. Basically, after hours. However,
someone may be working back or left their system on and the database open.

In other situations where the possibility of users being actively connected
is higher I would consider doing the error thingy.

Jeff

"TC" <aa**********@yahoo.com> wrote in message
news:11**********************@g49g2000cwa.googlegr oups.com...

What can I say? If I have a choice between writing code that works
100% of the time, and code that works 99.9% of the time, I'll choose
the former - not the latter.

Lyle, I'm really not trying to have a go at you or you code. But I've
done loads of system programming over the years, where tiny timing
problems can have disasterous consequences, that are very hard to track
down. So I'm always very conscious of timing issues in all the code I
read, or write.

Your code has a timing issue. I suggested a simple fix. Whether the
problem happens once a day, once a week, or once in three millenia, is
irrelevant, IMHO, if the problem is easy to fix.

Cheers,
TC

[TC]

Sorry, the reasoning expressed on this issue, does not make sense to
me!

Say you had a function that divided a number N1 by another number N2
and then multipled the result by a third number N3.

Say you coded that function as follows: result = (N1 / N2) * N3

I would say: "That code will fail when N2 = 0. You should fix that
problem, by adding a test for that case."

Would you reply: "No, I needn't do that, because N2 will almost never
be zero. It hasn't happened so far, so I'm happy to take the chance
that it won't happen in future" ?

My response would be: "Sorry, that doesn't make sense. Why take /ANY/
chance, when the problem is so easy to fix? Just add a test for N2=0.
It's a single line of code. Why would you want to /NOT/ fix the
problem, when it is so easy to fix?"

IMHO that is an accurate analog of what we've been discussing here. I
just can't understand why anyone would NOT choose to fix that problem.
I come from a commercial software development environment, where issues
like this ARE important, they are found & discussed on a regular basis,
and very few people would ever say: "oh, it's easy to fix, but it's
very unlikely, so let's just ignore it".

Your milage will vary!

Cheers,
TC

[Lyle Fairfield]

TC wrote:

What can I say? If I have a choice between writing code that works
100% of the time, and code that works 99.9% of the time, I'll choose
the former - not the latter.

Lyle, I'm really not trying to have a go at you or you code. But I've
done loads of system programming over the years, where tiny timing
problems can have disasterous consequences, that are very hard to track
down. So I'm always very conscious of timing issues in all the code I
read, or write.

Your code has a timing issue. I suggested a simple fix. Whether the
problem happens once a day, once a week, or once in three millenia, is
irrelevant, IMHO, if the problem is easy to fix.

Cheers,
TC

Being serious now, I am thinking carefully about what you say, and the
style you propose. It seems wise.
On the other hand, programming is full of tests; we see them recommended
everywhere; we see samples which include them everywhere. I recall
seeing here in CDMA, years ago, a suggestion that since testing is
faster than setting, one should do something like, if boolean then
boolean = false, rather than just, boolean = false.
Probably some of these tests are tests of conditions that can be changed
by some external program and probably most or all of these have the time
window vulnerability you describe. Yet much exemplary or previously
exemplary code ignores the time window issue.
So I am left wondering, Why?

--
Lyle Fairfield

[Lyle Fairfield]

TC wrote:

Sorry, the reasoning expressed on this issue, does not make sense to
me!

Say you had a function that divided a number N1 by another number N2
and then multipled the result by a third number N3.

Say you coded that function as follows: result = (N1 / N2) * N3

I would say: "That code will fail when N2 = 0. You should fix that
problem, by adding a test for that case."

Would you reply: "No, I needn't do that, because N2 will almost never
be zero. It hasn't happened so far, so I'm happy to take the chance
that it won't happen in future" ?

My response would be: "Sorry, that doesn't make sense. Why take /ANY/
chance, when the problem is so easy to fix? Just add a test for N2=0.
It's a single line of code. Why would you want to /NOT/ fix the
problem, when it is so easy to fix?"

IMHO that is an accurate analog of what we've been discussing here. I
just can't understand why anyone would NOT choose to fix that problem.
I come from a commercial software development environment, where issues
like this ARE important, they are found & discussed on a regular basis,
and very few people would ever say: "oh, it's easy to fix, but it's
very unlikely, so let's just ignore it".

Your milage will vary!

Cheers,
TC

I don't understand this. It seems to me that you have previously
recommended dealing with your concern by catching any error during the
(N1 / N2) * N3 process. If you want to be sure over the time and the
universe then surely you must allow for the possibility that some
malfunction of the computer, some magnetic storm, some absolutely new
phenomenon results in N2 being set to zero between the test and the
calculation?

--
Lyle Fairfield

[rkc]

TC wrote:

Sorry, the reasoning expressed on this issue, does not make sense to
me!

You can lead a horse to water...

[Lyle Fairfield]

rkc wrote:

TC wrote:

Sorry, the reasoning expressed on this issue, does not make sense to
me!

You can lead a horse to water...

The horse may have thought and decided he/she doesn't like the water or
isn't thirsty.

The whole notion of testing for "Can We Open the DB Exclusively" before
we compact is ancient (last century) and authoritative (advanced by
Microsoft). Perhaps, I have not been paying attention but this is the
first occasion where I have seen the [time window for change of the db's
status] advanced as a reason for not testing.
If the [time window] concern is valid, then I think it's likely that
more code than just compacting code is vulnerable.
For this reason I think it would be worthwhile to consider the whole
question rationally over a period of time.
There are two questions here for me (and perhaps other questions for
others).

1. Should we allow for time windows in our code as a rule? TC seems to
being saying, "Yes". Personally, I have run (nothing to do with this
thread) several tests to see if I could get something interspersed with
code execution, calling DoEvents and having other procedures running
from within the access application and from without. I have never
succeeded. But this is different; I have never tested opening a file.
And, of course, I try to have error handling code even after I test for
a condition. But I think we are talking about fundamental changes in
coding style here and I think this deserves some debate.

2. Should we allow for our error handling procedures to handle all
eventualities and thus never test for any condition? My own code does
this a lot. Is it good practice? Are there drawbacks to this approach?

--
Lyle Fairfield

[TC]

Hi Lyle

I can see that my calculation example was poorly designed. /I/ know
what I meant, but I can see why other people won't. It wasn't a good
example. I've tried to reword it, but din't get far.

So let me return to the original issue! Here is all that I'm trying to
say.

The code in question is this:

1: See if the db can be opened exclusively;
2: If NO, display an error message;
3: If YES, it is safe to compact the db - go do that.

My argument goes as follows.

(1) There's a timing error in that code. The fact that it is openable
exclusively at line 1, does not prove that it will still be openable
exclusively at line 3. So it is theoretically possible for line 3 to
fail with an unexpected runtime error, for that reason.

(2) There's a simple way to fix that problem. That way is, to delete
lines 1 and 2, and give line 3 an error handler to trap the error that
occurs if the database is still (or now) in use. This eliminates the
timing error. Now, there is /no risk/ of an unexpected runtime error
due to timing issues. You have /expected/ the error & written code for
it ccordingly.

If (2) was hard, I can understand anyone saying: "No, it's very rare,
and hard to fix, so I won't bother. The cost to benefit ratio just
ain't there!".

But (2) is /easy/ - it's the work of 2 minutes. So I just don't see why
anyone would choose NOT to do it.

In summary, I can fully understand not fixing a roblem that is hard to
fix, and rare to occur. But I can't understand not fixing problem that
is /easy/ to fix - whether it is rare or not! That's really all that
I'm getting at here.

Cheers,
TC

[TC]

Here's another example of the same issue that we've discussed in this
thread. This is a much better example, I wish I'd thought of it before.

Say you write the following code.

1. Calcuate the size (on disk) of the data that you plan to save;
2. Check the disk to see if there is that much space available;
3. If NO, display an error message;
4. If YES, go ahead and save that data.

That code has a timing error. The problem is, that the amount of free
disk space can change, between the time you check it at step 2, and the
time you do the save at step 4. So, /regardless of what that check
says/, you really have to try it anyway, and see if any error occurs.

So now you have this.

1. Calcuate the size (on disk) of the data that you plan to save;
2. Check the disk to see if there is that much space available;
3. If NO, we really have to try & save it anyway:
- Try to save the data;
- See if that got a NO SPACE error;
- If YES, display the error that you would have done
originally.
- If NO: "Oops, I thought there wan't enough disk
space to
save that data - but there was!"
4. If YES, go ahead and try to save it:
- See if that got a NO SPACE error;
- If NO, that's great;
- If YES: "Oops, I though there was enough disk space
to
save that data - but there wasn't!"

.... which is obviously ridiculous. So this is what you would do next:

1. Try to save the data;
2. See if a NO SPACE error occurred;
3. If YES, display an appropriate message.

In other words, the original code but /with the pointless check
removed/ !

As a matter of general principle, there's no point acting on a value,
if that value could have changed "behind the scenes" before you can act
on it. You must either (a) interlock the process, with a Windows mutex
or whatever, to stop that change occurring at all; or (b) make /no
assumptions/ about that value, just try the next action & see if it
succeeeds or not.

Similar issues occur in commercial database systems, when a transaction
'X' updates the dabase, on the basis of some value 'Y', which, an
instant later, is rolled-back due to a failed transaction 'Z'. If 'X'
now commits, you've updated the database on the basis of a value
('Y')which no longer exists! Oops.

Those are very tricky issues. They rely entirely on timing issues.
Precisely who does what, and when!

Cheers,
TC

[Lyle Fairfield]

TC wrote:

Hi Lyle

I can see that my calculation example was poorly designed. /I/ know
what I meant, but I can see why other people won't. It wasn't a good
example. I've tried to reword it, but din't get far.

So let me return to the original issue! Here is all that I'm trying to
say.

The code in question is this:

1: See if the db can be opened exclusively;
2: If NO, display an error message;
3: If YES, it is safe to compact the db - go do that.

My argument goes as follows.

(1) There's a timing error in that code. The fact that it is openable
exclusively at line 1, does not prove that it will still be openable
exclusively at line 3. So it is theoretically possible for line 3 to
fail with an unexpected runtime error, for that reason.

(2) There's a simple way to fix that problem. That way is, to delete
lines 1 and 2, and give line 3 an error handler to trap the error that
occurs if the database is still (or now) in use. This eliminates the
timing error. Now, there is /no risk/ of an unexpected runtime error
due to timing issues. You have /expected/ the error & written code for
it ccordingly.

If (2) was hard, I can understand anyone saying: "No, it's very rare,
and hard to fix, so I won't bother. The cost to benefit ratio just
ain't there!".

But (2) is /easy/ - it's the work of 2 minutes. So I just don't see why
anyone would choose NOT to do it.

In summary, I can fully understand not fixing a roblem that is hard to
fix, and rare to occur. But I can't understand not fixing problem that
is /easy/ to fix - whether it is rare or not! That's really all that
I'm getting at here.

Cheers,
TC

I think I am becoming convinced; it's a very good point and I'll file it
into the "follow these guidelines when coding" folder in my brain.

--
Lyle Fairfield

[TC]

Ok, cheers!

TC

[David W. Fenton]

"Jeff" <je************@asken.com.au> wrote in
news:43***********************@per-qv1-newsreader-01.iinet.net.au:

However, I am also going to delve into determining users currently
logged in and sending a message to them to log out, but also have
a timer that logs them out after a period. This just worries me at
this time - they may be in the middle of changing data and have
gone to lunch, as happens. You can tell them never to leave a form
open during an edit, but who listens.

Have your timer pop up a dialog that says they have to log off.

Tell them they have 15 minutes.

They can then close the dialog, finish their work and exit.

Or, if they don't, after 15 minutes, it will pop up, attempt to save
all data in any dirty forms, and close.

If they are sitting at the computer when this happens, it shouldn't
be an issue.

If they aren't, then it should just shut down transparently.

It does occur to me that you'd need to handle certain kinds of
possible data errors in the forms that you're trying to save (such
as not all required fields filled out), and that you'd probably have
to abandon edits in some kinds of forms. One solution to that would
be to log what was abandoned so that, theoretically, the user could
recover it if it turned out to have been important.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Jeff]

I absolutely agree with you that there is a timing issue, even if it is
probably fractions of a second. But the principal I do understand.

So will I take the error handling approach? This is very interesting as I
think back over how I code. To take this approach in all cases would add a
reasonable amount to the cost, I think. This probably requires more thought.

Will my systems work better and give me less problems. That I find hard to
believe when all my systems have worked consistently over many years with
very few problems. I honestly can't think of one problem that could be
attributed to a timing issue of this nature.

But I can see what you are saying and it will certainly be considered in the
future.

Thanks
Jerff

"TC" <aa**********@yahoo.com> wrote in message
news:11*********************@o13g2000cwo.googlegro ups.com...

Here's another example of the same issue that we've discussed in this
thread. This is a much better example, I wish I'd thought of it before.

Say you write the following code.

1. Calcuate the size (on disk) of the data that you plan to save;
2. Check the disk to see if there is that much space available;
3. If NO, display an error message;
4. If YES, go ahead and save that data.

That code has a timing error. The problem is, that the amount of free
disk space can change, between the time you check it at step 2, and the
time you do the save at step 4. So, /regardless of what that check
says/, you really have to try it anyway, and see if any error occurs.

So now you have this.

1. Calcuate the size (on disk) of the data that you plan to save;
2. Check the disk to see if there is that much space available;
3. If NO, we really have to try & save it anyway:
- Try to save the data;
- See if that got a NO SPACE error;
- If YES, display the error that you would have done
originally.
- If NO: "Oops, I thought there wan't enough disk
space to
save that data - but there was!"
4. If YES, go ahead and try to save it:
- See if that got a NO SPACE error;
- If NO, that's great;
- If YES: "Oops, I though there was enough disk space
to
save that data - but there wasn't!"

... which is obviously ridiculous. So this is what you would do next:

1. Try to save the data;
2. See if a NO SPACE error occurred;
3. If YES, display an appropriate message.

In other words, the original code but /with the pointless check
removed/ !

As a matter of general principle, there's no point acting on a value,
if that value could have changed "behind the scenes" before you can act
on it. You must either (a) interlock the process, with a Windows mutex
or whatever, to stop that change occurring at all; or (b) make /no
assumptions/ about that value, just try the next action & see if it
succeeeds or not.

Similar issues occur in commercial database systems, when a transaction
'X' updates the dabase, on the basis of some value 'Y', which, an
instant later, is rolled-back due to a failed transaction 'Z'. If 'X'
now commits, you've updated the database on the basis of a value
('Y')which no longer exists! Oops.

Those are very tricky issues. They rely entirely on timing issues.
Precisely who does what, and when!

Cheers,
TC

[jimfortune]

TC wrote:

In other words, the original code but /with the pointless check
removed/ !

I see your point and your example is a good one, but I respectfully
disagree. A check that catches 99.99% or more of the potential
problems is not pointless IMO even if it involves a little more code.
I don't think that the last resort should be the first option.

James A. Fortune

[Lyle Fairfield]

Well, TC I am still thinking about this.

Why do I want to check to see if I can open the db that is to be
compacted (the backend db) exclusively? Because I don't want to try to
compact it if I can't compact it.

Why don't I want to try to compact it if I can't compact it? Because it
will create an error. The error I get with the code below is quite a
nice error; it's a nice non-threatening message box, created by Access
itself; it's one of the most lovely and innocuous erros I have ever
seen; but it's an error.

Why don't I want the error to occur, after all shouldn't the user be
alerted that the compact did not occur? Because I run this code on open
of the front end. What this results in is that each user who opens the
front end tries to compact the back end, but, assuming the db is in
use, ony the first one who opens the front end actually does so. The
others sweep through the code which does nothing if
CanBeOpenedExclusively returns False without being aware that anything
at all has happened; because nothing has happened. Do I want them
interrupted with an Error message? No, I do not. Because no error
occurred. I don't want to try to compact the db when someone else has
it in a state where it can't be compacted. If an error occurs I want it
to be a real error about something that went wrong; when the db can't
be compacted nothing has gone wrong.

Yes I know we could write extensive error handling, to do nothing if
the file is opened exclusivley. And that is quite legitimate. But so is
not creating an error for something which in my view of the procedure
is not an error.

I think I got confused about the purpose of CanBeOpenedExclusively.
It's not to prevent an error. It's to prevent compacting a db that has,
very likely, been recently compacted, as whoever has it opened
exclusivley will almost certainly have done that.

Do I want to raise an error I don't have to raise. I think you say, Yes
because we can build everything into this; I THINK (at this time) I
say, No, because it's actually better not to have errors, and it's
especially good not to have errors which indicate only that the code is
running exactly the way you want it to run, maybe not from VBA's point
of view, but from my sense of confidence about my code.

And what about that 0.01 % time, that one day in a thousand when
someone does something that prevents the compacting in that little
time window that exists; well we're going to get the beautiful error
message and the db will miss compacting that day, and will be compacted
on only 999 days out of 1000. I think this will put it in the top 0.01
% of all dbs as far as having timely compacts done.

I know I'm rambling here, but I think I do want to make the point. I
still want to handle errors. I don't want to handle errors which aren't
really errors. In this case it's not an error for the db not to be
compacted when it's in use; it's what I want.

This is the very old code (it must be as I'm using bangs); it's run a
long time in many places:

Private Const AttachedTable As Long = 6
Private Const FileNotFoundErrNumber As Long = 53
Private Const Notify As Boolean = False
Public Sub CompactAttachedTableMDBS()
'will fail if no reference to DAO
On Error GoTo CompactAttachedTableMDBSErr
Dim r As DAO.Recordset
Dim s As String
If Forms.Count Or Reports.Count Then
MsgBox "Please, close all forms and reports, and retry.",
vbExclamation, "FFDBA"
Else
s = "SELECT Distinct CStr(DataBase) AS db" _
& " FROM MSysObjects WHERE Type=" & AttachedTable
With DBEngine(0)(0)
.TableDefs.Refresh
Set r = .OpenRecordset(s)
With r
Do While Not .EOF
If DoesFileExist1997(!db) Then
If CanBeOpenedExclusively(!db) Then
Shell SysCmd(acSysCmdAccessDir) &
"MsAccess.Exe " & """" & !db & """" & " /compact"
If Notify Then
MsgBox "Successfully Compacted" _
& vbCrLf _
& !db & "." _
, vbInformation, "FFDBA"
End If
Else
MsgBox "Can't compact" _
& vbCrLf _
& !db & "." _
& vbCrLf _
& "Database seems to be opened by another
user.", vbExclamation, "FFDBA"
End If
Else
MsgBox "Can't compact" _
& vbCrLf _
& !db & "." _
& vbCrLf _
& "Database seems to have been moved or
deleted.", vbExclamation, "FFDBA"
End If
.MoveNext
Loop
.Close
End With
End With
End If
CompactAttachedTableMDBSExit:
Set r = Nothing
Exit Sub
CompactAttachedTableMDBSErr:
With Err
MsgBox .Description, vbCritical, "Error: " & .Number
End With
Resume CompactAttachedTableMDBSExit
End Sub

Private Function CanBeOpenedExclusively(ByVal FullPath As String) As
Boolean
Dim d As Database
Dim p As PrivDBEngine
Set p = New PrivDBEngine
On Error Resume Next
Set d = p(0).OpenDatabase(FullPath, True)
CanBeOpenedExclusively = Not (d Is Nothing)
p(0).Close
Set d = Nothing
Set p = Nothing
End Function

Public Function DoesFileExist1997(ByVal FilePath As String) As Boolean
On Error GoTo DoesFileExist1997Err
GetAttr FilePath
DoesFileExist1997 = True
DoesFileExist1997Exit:
Exit Function
DoesFileExist1997Err:
With Err
If .Number = FileNotFoundErrNumber Then
DoesFileExist1997 = False
Else
MsgBox .Description, vbCritical, "Error Number: " & .Number
End If
End With
Resume DoesFileExist1997Exit
End Function

[TC]

You are saying:

1. You don't want the user to get an error if he tries to compact it
and that fails because it can't be opened exclusively.

2. Therefore you have to check first, to see if it is currently open
exclusively.

But point 2. does not follow from point 1! You just:

A. Ty the compact unconditionally - thereby closing the timing problem
that I've pointed out;

B. If that fails with the error code that means, "I can't do that
because someone else is using it", then - ignore that error!

That fact that you /trap/ an error, doesn't mean that you have to
display an error message!

It is still trivially possible to fix the timing problem in question.
You do that by /deleting/ the canbeopenedexclusivly check, and adding
suitable error handling to the compact call.

Truly, it's 3 or 4 lines of code!

Cheers,
TC

[TC]

It's called "defensive programming". You just don't write code that is
99.9% correct, if you could make a trivial change to make it 100%
correct. It doesn't matter whether the problem in question has actually
ever happened - or not!

But I think we've exhausted this issue!

Cheers all,
TC

[Lyle Fairfield]

TC

In this particular case I am shelling.

Shell SysCmd(acSysCmdAccessDir) _
& "MsAccess.Exe " & """" & !db & """" & " /compact", _
vbHide

I do not want to shell unconditionally. I want to open this compacting
Access instance/thread/program only when there is a 99.9% chance if its
task being completed successfully.
(My own estimate would be 99.99999999999999999999% but that's
irrelevant.)

************************
Now for another point/question.

Let's suppose you (TC, not just any you) have written some code to
compact the backend. While the compact is going on, a user opens
his/her copy of the front end and if successful opens a form for
editing data. One of these will fail, I believe, (probably the first,
although compacting can be so quick it's hard to verify that
empirically). Do you include, or recommend the inclusion of error
handling code for opening a front end, [what if the back end is opened
exclusvely by some compacting procedure, or any procedure for that
matter; what if IT erased the backend last night (this is not so
uncommon)?], and every form and report? Although we generally don't do
this, what about opening a table or query directly in datasheet view?

Lyle

[Lyle Fairfield]

Forgot to ask as well:

How the heck to you write error handling code for an error that may or
may not occur within a shell call?

[StopThisAdvertising]

Thanks guys for a nice discussion!
I enjoyed reading this one!

For me: I 'can live happily ever after' with Lyles' code. it is good enough for me also.

But IMO TC is absolutely right about his timing issue.
TC: I admire your patience in this thread... chapeau!

Arno R

[Arno R]

Excuse me for using the wrong account for the previous message.
Damn, Second time I did this in the past week! Sorry!

Arno R

"StopThisAdvertising" <StopThisAdvertising@DataShit> schreef in bericht news:43**********************@text.nova.planet.nl. ..
Thanks guys for a nice discussion!
I enjoyed reading this one!

For me: I 'can live happily ever after' with Lyles' code. it is good enough for me also.

But IMO TC is absolutely right about his timing issue.
TC: I admire your patience in this thread... chapeau!

Arno R

[TC]

Lyle Fairfield wrote:

In this particular case I am shelling.
Shell SysCmd(acSysCmdAccessDir) _
& "MsAccess.Exe " & """" & !db & """" & " /compact", _
vbHide

Ok, >>> NOW <<< I see where you're coming from!

It never occurred to me, that you'd be shelling-out to do the compact.

If you do it in the way you have shown, you have no way of handling any
errors that occur in that process! (for example, the error that occurs
if the BE is already open) No wonder you want to do some up-front
checks!

The solution (and all-round better approach IMHO), is to do the compact
by VBA code within the current instance of Access. Then you can add
whatever error handling you want. There's no need at all, for another
instance of Access.

Just do this:

- create a temp filename;
- use dbengine.compactdatabase to compact the data db to that temp
name;
- rename the data db out;
- name and/or move the temp file in;
- include appropriate error handling!

If you normally keep a persistent connection oen to the BE, for
performance reasons, you'd need to close that first, then do the above,
& then reopen that connection.

Cheers,
TC

[jimfortune]

TC wrote:

It's called "defensive programming". You just don't write code that is
99.9% correct, if you could make a trivial change to make it 100%
correct. It doesn't matter whether the problem in question has actually
ever happened - or not!

But I think we've exhausted this issue!

Cheers all,
TC

You're accusing the choir of being heathens. You missed my point
entirely. I'm not talking about your earlier point. I'm saying that
your code is better off with the 'additional' check that catches 99.9%
of the errors. We're talking about two things that are both 100%
correct. I am not suggesting that you abandon your safety net although
that temptation is present for this scenario. I believe everyone
already agrees that 100% certainty is light years away from 99.9%.
After making every effort possible to turn the 99.9% into 100%, and
trust me when I say I try, you're sometimes stuck with error handling.

James A. Fortune

[TC]

Lyle Fairfield wrote:

Now for another point/question.

Let's suppose you (TC, not just any you) have written some code to
compact the backend. While the compact is going on, a user opens
his/her copy of the front end and if successful opens a form for
editing data. One of these will fail, I believe, (probably the first,
although compacting can be so quick it's hard to verify that
empirically). Do you include, or recommend the inclusion of error
handling code for opening a front end, [what if the back end is opened
exclusvely by some compacting procedure, or any procedure for that
matter; what if IT erased the backend last night (this is not so
uncommon)?], and every form and report? Although we generally don't do
this, what about opening a table or query directly in datasheet view?

Good question!

For performance reasons, many people open a persistent connection to
the BE file for the duration of the FE run. Here's how I do it:

set global_db_varable = dbengine.opendatabase("path to BE file")

In my apps, that statement is executed from Form_Open in the unbound
main menu form - so nothing else (in that FE) has tried to open the BE,
yet.

So that would be the perfect place to handle BE access errors. I just
checked what my own code would do, if that statement failed wih a
runtime error. Here's what I found:

beep
MsgBox "CAN NOT OPEN DATA DATABASE" & vbCr & vbCr & _
"Error " & Err.Number & ": " & Err.Description & vbCr & vbCr & _
"PLEASE ADVISE YOUR SOFTWARE SUPPLIER", _
vbCritical
Application.Quit

Oops! I probably need to amend that code, to give a better message if
the BE is currently being compacted. Perhaps, ask the user to try again
in a few moments.

Cheers,
TC

[Lyle Fairfield]

I think you have changed the topic here. It is not about how to compact
an external file.

I have explained why I am doing my checking before I am doing my
compact. The reason I am doing my compact this way is irrelevant.

I THINK your position is that we should not test for conditions before
we try as this is redundant; we should instead meet all the conditions
in our error handling code.

I disagree with this.

There are situations where we want to minimize the chance for error;
Shell is one of these.

This is not to denigrate good error handling. I try to include good
error handling when I think it's needed. An example is working with low
level DOS files. When an error occurs, I want to be sure file handles
are closed, and so I am careful about that.

Your position will give me cause for a little more thought when I code.
But I think it's easier to, for instance, test a divisor for a value of
zero, and take whatever action is appropriate before dividing, than to
do the division, check for errors and take whatever action is
appropriate for the division by zero error. I also think it makes my
code easier to follow.

A comparison of ways to compact an external file may be an interesting
topic. I encourage anyone who wants to discuss it to start a thread
about it. I haven't used the dbengine to do it for many years. I could
say why, (especially if I could remember why), but that is not part of
this topic.

And of course, I do use error handling when I see a good reason for so
doing. If you look at this function which I posted, you see that.
Private Const FileNotFoundErrNumber As Long = 53
Public Function DoesFileExist1997(ByVal FilePath As String) As Boolean
On Error GoTo DoesFileExist1997Err
GetAttr FilePath
DoesFileExist1997 = True
DoesFileExist1997Exit:
Exit Function
DoesFileExist1997Err:
With Err
If .Number = FileNotFoundErrNumber Then
DoesFileExist1997 = False
Else
MsgBox .Description, vbCritical, "Error Number: " & .Number
End If
End With
Resume DoesFileExist1997Exit
End Function

Finally TC you've given some excellent pointers in CDMA. One I remember
is that JET (now or maybe always) stores booleans as bits, and so can
store 8 booleans as one byte. I did not know this before you outlined
it.
But here in this thread I have posted code and you have not (except for
a msgbox). I feel this puts me at a decided disadvantage.

And you have recently quarrelled with Terry Kreft. Sometimes I don't'
agree with Terry but I would as likely quarrel with him as a Buddhist
would with the Buddha. IMO almost all the important body of collective
knowledge of CDMA rests on the contributions of Dev Ashsish, Terry
Kreft and Michael Kaplan. (yes I know I've quarrelled with Michael). I
think this is very unfortunate. I would very much have liked their
opinion about the wisdom of allowing for tiny windows of opportunity
for something to go wrong. Terry's the only one who still shows up from
time to time and it's sad that we didn't get his view.

[TC]

Bye all.

TC