Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2009
    Posts
    3

    Question Unanswered: Access code to log which queries are run

    I have to analyse and optimise several Access DB and was wondering if it would be possible to automate some of the process.

    Is it possible to write VBA code in Access that 'hooks' into the system and is called each time a query (any query) is run. This could then be used to write to a log file (with timestamps) to help analyse queries and performance.

    I'm not holding out much hope, but wondered if anyone had ever done anything similar.

    Thanks

    Simon

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Nope.

    If your queries are run via buttons then you can add the logging code there. This is obviously a major ball ache, however you can also write code to add code to your modules. That was, in fact, the subject of my first question here and it was actually to do exactly what you want to do!
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Mar 2009
    Posts
    3
    Thanks for the speedy response.

    The thing is, there's no one way that these DBs work - it could be on a button calling a macro or calling VB code, on a timer...
    These DBs are all of various ages, had various bits of on-going development and built by various people, so there's absolutely no standardisation.

    Hence the request for a more generic solution that would somehow hook into the internals in Access to trigger whenever a query is run.

    If I was doing this in SQL server, I'd modify each of the stored procs to write to a log file when called and when completed (probably including the params as well, but this wouldn't work with Access as you can run only one command per query.

    So I was wondering if anything else that would be possible in Access?

    Again, not holding out much hope - looks like I might actually have to do some work ;-(

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    No - there's no way to "hook" in to access.

    Note that changing the sprocs in SQL Server is not very different to what you'd do here. You would have to take some account of macros of course.

    If people are actually running reports rather than queries then you could log on the Open event of the report. Queries have no event handling so can't be logged in this way.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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