Is it possible to monitor and log actual queries made against an Access MDB?
The answer depend on the technology used from the client which use MDB. There are different tracing settings which you can configure in HKEY_LOCAL_MACHINE\Software\Microsoft\Jet\4.0\Engines\ODBC http://office.microsoft.com/en-us/access/HP010321641033.aspx. If you use OLEDB to access MDB from SQL Server you can use DBCC TRACEON (see http://msdn.microsoft.com/en-us/library/ms187329.aspx). I can continue, but before all you should exactly define which interface you use to access MDB.
MDB is a file without any active components, so the tracing can makes not MDB itself, but the DB interface only.
UPDATED: Because use use DAO (Jet Engine) and OLE DB from VB I recommend you create JETSHOWPLAN regisry key with the "ON" value under HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\JET\4.0\Engines\Debug (Debug subkey you have to create). This key described for example in https://web.archive.org/web/1/http://articles.techrepublic%2ecom%2ecom/5100-10878_11-5064388.html, http://msdn.microsoft.com/en-us/library/aa188211%28office.10%29.aspx and corresponds to http://support.microsoft.com/kb/252883/en allow trace OLE DB queries. If this output will be not enough for you you can additionally use TraceSQLMode and TraceODBCAPI from HKEY_LOCAL_MACHINE\Software\Microsoft\Jet\4.0\Engines\ODBC. In my practice JETSHOWPLAN gives perfect information for me. See also SHOWPLAN commend.
UPDATED 2: For more recent version of Access (like Access 2007) use key like HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\12.0\Access Connectivity Engine\Engines
. The tool ShowplanCapturer (see http://www.mosstools.de/index.php?option=com_content&view=article&id=54&Item%20%20id=57, to download http://www.mosstools.de/download/showplan_v9.zip also in english) can be also helpful for you.
Monitor queries in an access database
Have you tried turning on the ODBC trace function? Use the ODBC Data Sources control panel applet. Switch to the 'Trace' tab and review the options available.
Microsoft JET SQL Query Logging or How do I debug my customer's program?
To get your grubby hands on exactly what Access is doing query-wise behind the scenes there's an undocumented feature called JETSHOWPLAN - when switched on in the registry it creates a showplan.out
text file. The details are in
this TechRepublic article alternate, summarized here:
The ShowPlan option was added to Jet 3.0, and produces a text file
that contains the query's plan. (ShowPlan doesn't support subqueries.)
You must enable it by adding a Debug key to the registry like so:\\HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\JET\4.0\Engines\Debug
Under the new Debug key, add a string data type named
JETSHOWPLAN
(you must use all uppercase letters). Then, add the key valueON
to
enable the feature. If Access has been running in the background, you
must close it and relaunch it for the function to work.When ShowPlan is enabled, Jet creates a text file named
SHOWPLAN.OUT
(which might end up in yourMy Documents
folder or the current
default folder, depending on the version of Jet you're using) every
time Jet compiles a query. You can then view this text file for clues
to how Jet is running your queries.We recommend that you disable this feature by changing the key's value
toOFF
unless you're specifically using it. Jet appends the plan to
an existing file and eventually, the process actually slows things
down. Turn on the feature only when you need to review a specific
query plan. Open the database, run the query, and then disable the
feature.
For tracking down nightmare problems it's unbeatable - it's the sort of thing you get on your big expensive industrial databases - this feature is cool - it's lovely and fluffy - it's my friend… ;-)
how to get sql query in MS Access 2007
I was not able to find any such tool (i did lots of google few days back, when i was having similar requirements) and finally endup doing import of ms-access db in sql server and change connection, did my work and then changed the connection back to ms-access.. :)
Storing queries on backend access database
You could either save the queries in a separate file on the network share for the users to import from as needed.
A more polished method is to maintain a single frontend, then copy this to a network share when a new version is ready, and let the users download this whenever they launch the application from a shortcut. This way the distribution is fully automatic, and it even works in a Citrix environment.
The full description and a script (which you probably can reduce a little) can be found here:
Deploy and update a Microsoft Access application in a Citrix environment
The crucial line where the local copy is opened is here:
' Run PPT.
If objFSO.FileExists(strAppLocalPath) Then
Call RunApp(strAppLocalPath, False)
Else
Call ErrorHandler("The local application file:" & vbCrLf & strAppLocalPath & vbCrLF & "could not be found.")
End If
So to be sure get strAppLocalPath
right.
Related Topics
How to Grant All Privileges on Views to Arbitrary User
Why Do I Need to Explicitly Specify All Columns in a SQL "Group By" Clause - Why Not "Group by *"
Teradata Equivalent for Lead and Lag Function of Oracle
Query Combinations with Nested Array of Records in JSON Datatype
Split String by Comma in SQL Server 2008
Bigquery Group_Concat and Order By
Combine Two Tables into a New One So That Select Rows from the Other One Are Ignored
Select Multiple Columns from a Table, But Group by One
Is There an Automatic Modification Time Stamp Type for Oracle Columns
SQL Server: How to Get a Database Name as a Parameter in a Stored Procedure
Efficient Way to String Split Using Cte