Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Mar 2002
    Location
    Edinburgh, Scotland
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Event on exit (Access 97)

    Hi folks - desperate for help with this - I'm sure this is easy if you know how! I wish my application to run a "snapshot" query taking a copy of a table which returns the Now() date and time function, at the point when a user exits the application by any means - i.e by a control exit or by the application "X" in the top right hand corner of the application itself.

    The snapshot table will thereafter be used as previous data for comparison purposes the next time the data is updated - and I need to return the date and time of its creation (i.e. when the application closed). Can anyone help?? Regards.

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Event on exit (Access 97)

    I'm not sure I understand what you are trying to accomplish. Are you trying to timestamp each record in the table you create, or would the creation date for the table copy work for you?
    In any event, the major trick is to figure out when the application closed. Unfortunately there isn't an application close event that you can trap on in Access 97 or any other version (AFAIK). About the only way I've seen this done is to use a modal form which means the user can't get to the application "X" box, but even then the user can do CTRL-ALT-DEL and kill the app.

    More questions:
    Do you have multiple users for this database, or is it only used by a single user?
    Would timestamping a record each time it is changed be just as effective?
    Have you considered archiving the record in it's entirety each time it is changed? (That's really lots of work in Access, but if you switch to a SQL Server/MSDE back-end it's pretty easy using triggers.)
    Wendell

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Event on exit (Access 97)

    To have something happen when the user closes the database, you must make sure that the user can only do so by means provided by you (a command button, menu item, ...). There are several threads on this subject; you will find them if you do a search for "prevent close".

    I'm confused by "taking a copy of a table which returns the Now() date and time function". What do you mean by this? Do you really want to make a copy of a complete table? If so, should the copy overwrite the previous copy, or do you want to create a new table each time? In both cases, it could cause serious database bloat.

    If you create a table, its creation date/time is set automatically; you can get at it using the CreationDate property of the DAO TableDef object.

  4. #4
    Star Lounger
    Join Date
    Mar 2002
    Location
    Edinburgh, Scotland
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Event on exit (Access 97)

    Hi folks, the idea is that I have a table called inventory which pulls in information from a master table at each update. I need a snapshot of this table within the database, as I need a report which reports on numerical changes to all fields in the inventory table (say Price or Booked) against a given date since the last time it was updated. I'm not good with code being a relative beginner and self-taught to boot, so the simplest thing for me to do within the limitations of my own knowledge is to create a copy of the table on exit and date/time it and then run comparison queries....... does this make sense? I would go for the modal thing, but there are multiple users, some of whom are guilty of crashing out of their databases... Any ideas??

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Event on exit (Access 97)

    As Wendell noted, there is no application-level Close event in Access, and anyway, if the user crashes the database of just turns off the PC, such an event wouldn't occur.

    If you want to keep track of each change made in the Inventory table, you would need to do this in the AfterUpdate event of the form or its controls, but Access is not really the best tool for this. A SQL Server backend is much more suitable for a database with continuous change tracking.

    If you "just" want to compare the current situation with that for a given date, a simple solution is to create a backup copy of the whole database on a daily basis. You'd have to create links in the production database to tables in a backup.

    Or you might copy the Inventory table within the database itself once a day, for example the first time the database is opened that day. It could be done like this:

    1. Create a table with one date/time field in it. Put the current date in this field. In the following I'll use tblCopyDate and CopyDate as table and field names, respectively.

    2. Write a procedure:

    Sub MakeCopy()
    If DLookup("CopyDate", "tblCopyDate") < Date Then
    DoCmd.CopyObject _
    NewName:="tblInventory" & Format(Date, "yymmdd"), _
    SourceObjectType:=acTable, _
    SourceObjectName:="tblInventory"
    CurrentDb.Execute "UPDATE tblCopyDate SET CopyDate = Date()"
    End If
    End Sub

    Replace tblInventory by the name of the table to be copied.

    3. Call this procedure in the OnLoad event of the startup form of the database, or if you prefer from an AutoExec macro.

  6. #6
    Star Lounger
    Join Date
    Mar 2002
    Location
    Edinburgh, Scotland
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Event on exit (Access 97)

    Many Thanks Hans - will try your last suggestion. Marion

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •