crash detection in database

[JohnE]

Have been given a task of what the boss called crash detection. This is an
A2007 front end and mssql2005 back end. Connection is dsn/odbc. What is
requested is to detect whether the user shut down the front end or an error
occured that caused the app (or user) to reopen the app after is shut down.

My first thought is that since a "crash" would break the connection between
the front and back, have a txt file write to the user's profile file on their
machine. Now, when the user reopen's the app, it checks to see if the txt
file exits. If it does, it now records info into a table on the backend,
deletes the file, then recreates it. If the user closes/shutsdown the app,
it will delete the file so there is no file there on the opening so the check
finds nothing, writes nothing to db, creates new txt file, and opens the app.

Has anyone done something like this before? If so, how? Any examples,
samples, links to sites will be appreciated.

Thanks....John

 

[Jack Leach]

Any reason you can't do this in a table rather than writing a text file?
I've done the same... only in reverse. With a custom login system I would
write the user's computer name (see mvps.org/access/api) to a table of active
users, and remove them on logout. When there was a crash, next startup on
that comp would detect that the user was 'already logged in' and a request
would be made to remove the record.

I would think the concept would be easily modified into what you are looking
for.

hth
--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

 

[JohnE]

Any reason you can't do this in a table rather than writing a text file?
I've done the same... only in reverse. With a custom login system I would
write the user's computer name (see mvps.org/access/api) to a table of active
users, and remove them on logout. When there was a crash, next startup on
that comp would detect that the user was 'already logged in' and a request
would be made to remove the record.

I would think the concept would be easily modified into what you are looking
for.

hth
--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

Mr Leach, thanks for the idea. I actually like that idea better then the
writing a file to the person's machine. I think it can persuade the boss to
go for it instead of the file writing. I will look into the site you
provided. I take it you use the same? If the user name is detected, what
does the user do or is the whole process transparent to the user?
Thanks again.
John

 

[JohnE]

Mr Leach, thanks for the idea. I actually like that idea better then the
writing a file to the person's machine. I think it can persuade the boss to
go for it instead of the file writing. I will look into the site you
provided. I take it you use the same? If the user name is detected, what
does the user do or is the whole process transparent to the user?
Thanks again.
John

I was just out there to the site. I don't think I have every come across a
site like this. Even saw another routine I can use elsewhere. Say, another
question or two I have is where do you place the function for detecting the
opening of the app? Also, would you be willing to share what you have or
know of an example site that has a demo to look at?

Thanks again.

 

[Jack Leach]

I don't think I have every come across a

site like this. Even saw another routine I can use elsewhere.

Yes, mvps.org is the holy grail of developer reference websites. Probably
75% of questions answered here via links direct to that website.

As for the setup, I don't think you want to go with the User name. Assuming
that a user can log in from different computers, I would go with the Computer
name itself rather than the user (or a combination of both). This entire
process can be completely transparent to the user.

The basic idea lies in having a form that is opened in hidden mode as the
application starts, and remains open until the application closes. On
startup, run a startup function from an Autoexec macro (a macro named
Autoexec will execute on startup unless bypassed by holding down the Shift
key (this behavior can be modified as well if you choose... its in mvps)).

So run this startup function and open the form in hidden mode, and there it
will stay throughout the instance of the app.

After you open your form, somewhere in your startup procedure (like after
you've verified table connections if you happen to do this), write the
current Computer and User name to a table in the backend. Include
timestamps, etc. if you like, and now you have a record of the app opening.

To capture the closing of the app, you run some code from the Close event of
your hidden form. As this form sits around and does nothing the whole time
the app is running, the only time it is going to close is when the app is
shut down... there should be no other way this form is closed, and you need
to rely on that (e.g. - if you have any procedures to "close all forms" you
need to make sure you put an exception in for this one!).

In this close event, run a delete query with the criteria being the Computer
and User name. You can also write a log here if you wish. If the app closes
unexpectedly, this procedure will never run, and the information will remain
in the backend table.

So there you have how to mark the app as opened and closed. To check for a
crash on the last close, it's as simple as querying the activity table on
startup, before you enter the computer and user name. Run a dcount or
dlookup on the table with the comp and user name... if there's a record, you
know the app closed incorrectly. At this point you can do whatever you'd
like to with it - report it to the user, report it to the admin without the
user ever knowing, etc etc. Just remember when you're done doing your
reporting to delete the record before you add it again for standard startup
purposes.

Thats more or less all there is to it. Unfortunately I do not have a demo
of this. I use the same concept but handle it a little differently... I do
not track the app opening/closing, but rather user login/logout (I use a
custom user setup, not windows security, which allows for any person to login
from any computer). So even if I were to pull my app, and remove all the
code except this functionality and turn it into something someone else could
understand (not fun), it wouldn't help your specific cause anyway.

Luckily it's not that difficult of a concept to work with. The above should
be a rather complete description of what you need to accomplish the task.
Let me know if you need a hand with any of the details...

hth


--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

 

[JohnE]

Mr Leach, thank you for the info. This clarified it.
.... John