Trapping Application-level or Database-level events

  • Thread starter Thread starter R Avery
  • Start date Start date
R

R Avery

Is the above possible? For example, is it possible to trap the changing
(or saving) of the design of a query or table? Alternatively, is it
possible to decode the information stored in LvProp, LvExtra, etc of the
MSysObjects table?

Any help would be most appreciated.
 
Is the above possible? For example, is it possible to trap the changing
(or saving) of the design of a query or table?
Click to expand...

No. These events are not exposed in any object model, AFAIK. (Happy to be
corrected, but!)

Alternatively, is it
possible to decode the information stored in LvProp, LvExtra, etc of the
MSysObjects table?
Click to expand...

Sure - until they change in the next version of Access!

What are you actually trying to achieve?

HTH,
TC
 
I want to be able to do a few things. Those Lv fields might contain
very useful information. For example, if they store the dimensions of
the query designer window for the query, I could manipulate that. If
they store the format properties for particular fields in a query, that
could be altered programmatically AND can be dumped to XML for easy
importation elsewhere, which is FAR superior to manually entering this
data again at the destination (copy and paste of the query objects is
not an option).

If there were events to trap the saving, creation, alteration of queries
and tabledefs, then I could create a custom table LIKE MSysObjects that
would contain additional fields for commonly-used custom properties of
querydefs. Iterating over 2000+ query defs every time just to determine
custom properties takes forever, at least on my computer (I can't rely
on Modified or Creation date to make this easier by saving results and
only updating the results for those whose Mod date > AsOfDate because
these Modified and Creation dates are often wrong - for a reason I can't
go in to). However, decoding the LvXXXXXXX fields may circumvent this.

How can i decode the information in the LvXXXXXXX fields? Has anyone
ever wrapped this functionality into a class object?
 
But all the properties of a query can be determined using DAO. I don't see
why iterating the properties of 2000+ querydefs should take any real time,
even on a slow PC. Can you show us some code that is slow?

Of course it is possible in principle to parse the msysobjects fields. But -
as I said - they are liable to change in any version of Access. So I can't
see that it makes much sense, to put much effort into parsing those fields.

HTH,
TC
 
Further to this: I took a copy of one of my applications containing
about 100 complex queries. I wrote some code to duplicate them with
different names, for a total of ~3000 queries. I then compacted the
database to ensure that all the queries were uncompiled: a worst case
scenario.

I then wrote code to iterate every property of every one of those
queries (but do nothing else with them). This code took about 28
seconds to run first time, then 15 seconds thereafter, on a low-end
1.6Mhz PC. The improvement would have been due to disk buffering
and/or query compilation - I didn't bother to find out which.

So - assuming that 15 seconds to process ~3000 saved queries is not
"slow" in your opinion - what is the code that is slow?

HTH,
TC
 
This is the reason: we have a huge Access 97 database with many GB's of
data (spread over like 20 .mdb files), and at least 15 users are
connected to it at any point in time. It is on a fairly fast computer,
but it took 3mins, 18 secs to run the following code on just 1889 queries.

On my own computer, I did a test, and I got results like yours.
However, even 15 seconds is a little slow... What if i want to redesign
the Access explorer (or whatever it is called), from which we may select
queries - and i want to include custom columns for custom properties of
query defs? And sorting? 15 seconds a very long time, and 3 minutes is
an unimaginably slow.








Public Sub FindQueries3(Optional ByVal Sql As String = "", Optional
PrintSql As Boolean = False, _
Optional Description As String = "", Optional CreatedAfter As Date =
#1/1/1900#, _
Optional CreatedBefore As Date = #1/1/1900#, Optional LastUpdatedAfter
As Date = #1/1/1900#)
Dim qry As QueryDef
Dim bln As Boolean

On Error Resume Next

Debug.Print
Debug.Print
Debug.Print Now

For Each qry In CurrentDb.QueryDefs
bln = False
If Sql <> "" Then bln = bln Or (InStr(qry.Sql, Sql) <> 0)
If Description <> "" Then bln = bln Or (InStr(1,
qry.Properties("Description"), Description, vbTextCompare) <> 0)
If CreatedAfter <> #1/1/1900# Then bln = bln Or (CreatedAfter <
qry.Properties("DateCreated"))
If CreatedBefore said:
qry.Properties("DateCreated"))
Click to expand...
If LastUpdatedAfter <> #1/1/1900# Then bln = bln Or
(LastUpdatedAfter < qry.Properties("LastUpdated"))


If bln Then
'Debug.Print "Query Name:"
'Debug.Print qry.Name
If PrintSql Then
Debug.Print
Debug.Print "SQL:"
Debug.Print qry.Sql
'Debug.Print
Debug.Print "Query Description:"
Debug.Print qry.Properties("Description")
Debug.Print
Debug.Print
End If
End If
Next

Debug.Print Now
Debug.Print "Done"
End Sub
 
Gak! Sounds big. I see what you are trying to do. 3 minutes sure sounds way
too slow. 15 seconds sounds ok, though?

There are definitely some ways to optimize the code you showed. They're easy
to do. Try them out & see if the difference is much.

1. Each call to the currentdb() function refreshes all collections. So that
is an expensive function! I doubt that it is being re-evaluated on each pass
through your "For Each" loop. None-the-less, best practice is to cache the
value in a variable, to avoid repeated function calls:

dim db as database
set db = currentdb()
' do things here using db instead of currentdb().
set db = nothing

2. To optimize the repeated references to qry.Properties within the loop,
change:

qry.properties("this") ...
qry.properties("that") ...
qry.properties("t'other") ...

to:

with qry.properties
![this] ...
![that] ...
![t'other] ...
end with

3. Maybe minimize the # of property references by changing:

bln = bln or (property = ...)
bln = bln or (property = ...)
etc.

to:

if (property = ...) then
bln = true
elseif (property = ...) then
bln = true
...
endif

HTH,
TC
 
Back
Top