Results 1 to 12 of 12

Thread: Snapshot (2003)

  1. #1
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Snapshot (2003)

    In a previous post <post#=581,020>post 581,020</post#> , I wanted to compare statistics from twe weeks. This works fine, but we have come accross a snag. We had a case where some actions were transferred from one user to another. This meant that when the stats where compared, the figures for the previous week had changed, as expected. Is there a way of taking some sort of 'snapshot' of a table, so if the data was changed, we could compare with the original data.

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Snapshot (2003)

    Do you want to do this by code, or by manual intervention. You can always copy and paste a table, and choose structure and data.

    Otherwise you could run a run a make table query to make a new table out of the table as it was at the time.
    Regards
    John



  3. #3
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Snapshot (2003)

    The reports are always run by another user, so code would probably be best.

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Snapshot (2003)

    Do you need assistance with this?

    First create the Make Table query. The query will have a table name in it, so whenever you run it you would need to rename the table.

    Then write code to run the query, then rename the new table

    DoCmd.openquery "qrymaketable"
    DoCmd.Rename "newTable", acTable, "newTable" & format(Date(), "ddmmyyyy")
    Regards
    John



  5. #5
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Snapshot (2003)

    Thats excellent John, thanks. Only just got round to looking at it again. Would I be better leaving the date off, as I have to put the table in a query to compare two sets of data?

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Snapshot (2003)

    Yes you can leave off the date. I thought you wanted to build up a collection of snapshots.

    If you run a make table query when the table already exists, you will get a confirmation message confirming the deletion of the existing table.

    To avoid this you could delete the exsting table before running the query. Then you will get an error if the table does not exist.

    So you could try

    Dim strtableName as string
    Dim strQueryName as string
    strTableName = NameofTable
    strQueryName = Nameofquery
    On Error Resume Next
    DoCmd.DeleteObject acTable, strtablename
    DoCmd.OpenQuery strQueryName


    the On Error Resume Next means that if there is an error at the DeleteObject line, it ignores the error and jsut keeps going.
    Regards
    John



  7. #7
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Snapshot (2003)

    Typed the code on the OnClick event of a button, and although it's creating the table, it's still asking for confirmation.

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

    Re: Snapshot (2003)

    Does it help if you insert

    DoCmd.SetWarnings False

    at the beginning of the code, and

    DoCmd.SetWarnings True

    at the end?

  9. #9
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Snapshot (2003)

    Or does it help if you go to Tools..Options Edit/Find and remove the tick from "Action queries" .

    This will remove the confirmation about running the action query, but would not get rid of the one about deleting the table.
    Regards
    John



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

    Re: Snapshot (2003)

    This is a user-level setting, so it will affect all databases for the person who changes this setting, but it has no effect for others using this database.
    It is possible to change this setting using code, but I am not in favor of doing so - in general, programmers shouldn't mess with user preferences.

  11. #11
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Snapshot (2003)

    In theory I agree with you, but I have never actually met a user where confirming action queries represented their preferences.

    Rather it represents a default setting they did not know about.
    Regards
    John



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

    Re: Snapshot (2003)

    I, for one, prefer to have confirmation of action queries turned on, so that I know how many records will be affected by an action query. This is very useful information during testing. In a production database, I usually use DAO or ADO to execute SQL statements; these don't display a warning at all.

Posting Permissions

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