Take the 2-minute tour ×

I have an Access application with a SQL server back-end, mixed with quite a few DB objects local to the Access app. I've tried running SQL Profiler, but I got very little except a cryptic sp_execute 2,4288,4289,4290,4291,4292,4293,4294,4295,4296,4297.

I would like a trace tool that is local to the Access DB, so I also pick up any activity that doesn't go back to the SQL server.

share|improve this question
add comment

2 Answers

up vote 2 down vote accepted

As far as I know there is no such facility within Access but, depending on your case, you could try these few things:

  • Write a wrapper against SQL executables: that would mean replacing all calls to Execute, OpenRecordset etc within your VBA to an alternative version that would log the query.
    This isn't going to catch everything obviously but it could help.

  • Move your local tables to another database and use ODBC to relink them to your original Access application. You can then use ODBC's logging facilities.
    This could be the best altenative as it's fairly easy to setup for debugging.
    It's not the best solution for a production environment though as all your calls to local tables will in fact go through ODBC, but again, it's a temporary solution for debugging.

  • Use ShowPlan and ISAMStats to view how Jet/ACE interprets your queries and get other database activity stats.
    It's easy to setup by writing a key to the registry and you'll end-up with a log describing how your queries are analysed.
    It's more useful for optimisation than logging but again, it could help.

share|improve this answer
I like your second option most. I'll give it a try tonight, thanks. –  ProfK Mar 12 '09 at 12:02
Oh yes, regarding your first option: I don't have the VBA. –  ProfK Mar 12 '09 at 13:06
Option 2 seems ill-worded. The original question already said SQL Server was the data store, and I would assume that would mean using ODBC linked tables, as that's by far the easiest way to work with SQL Server data in an Access application. So all that was really needed was turning on ODBC logging. –  David-W-Fenton Dec 8 '09 at 22:23
@ProfK: if you're using Access you have VBA, though you may not be using it. –  David-W-Fenton Dec 8 '09 at 22:23
add comment

Use Flextracer, a shareware, free for 30 days or so. My colleague here has just found this for us as we were going through a similar situation. Problem solved.


[]s, Pedro Carneiro Jr. pedrokarneiro@hotmail.com

share|improve this answer
What does that provide that ODBC logging lacks? –  David-W-Fenton Dec 8 '09 at 22:24
add comment

Your Answer


