Monitoring use of reports

[Leslie Isaacs]

Hello All

I am using A97.

Is it possible with code to:

a) create a table with a record for each report on the db, with the (key)
field being set according to the report name;
b) record in a 2nd field in the above table the date that the corresponding
report was last opened.

In fact I would like to do the above for all the db's tables, queries,
forms, macros and modules. The reason is that I know that many of these
items are completely redundant and could be deleted - so code that would
delete the item (after prompting) after a specified period of inactivity
(say 6 months) would be the icing on the cake!

Is this asking too much?

Many thanks
Leslie Isaacs

 

[Douglas J. Steele]

The following query will give you a list of all of your reports:

SELECT Name
FROM MSysObjects
WHERE Type = -32764
ORDER BY Name

You could use that to populate your table.

In each report's Report_Open event, add logic to update the date in your
table.

You can do this with Forms and, I think, Macros as well, but not with
tables, queries or modules. Forms are Type = -32768 and Macros are Type
= -32766

 

[Jeff Conrad]

Hi Leslie,

Here is a recent past post of mine that deals with this subject.
Perhaps it will help in your situation.
There is nothing built into Access that will do this, but it is quite easy to
create a recording method. I remember a post from MVP Albert Kallal
a couple of years ago where he detailed that he logs which forms, reports,
etc are opened as well as what toolbar/menu bar options are used. He uses
this information to see what options are used the most (or over used), and
which options are used the least. He can then make improvements to the
program. I can provide a link to that post if you would like.

Anyway, here is a little something I just put together. You can increase or
decrease the complexity as much as you desire. This will record who opens
what form or report. Follow these steps.

1. Create a new table called tblActivityLog with the following fields:
fldObjectLogID - AutoNumber
fldObjectName - Text
fldTimeOpened - Date/Time
fldUser - Text

2. Create a new standard module and copy/paste this code into it:

Public Sub subLogActivity(strObjectName As String)
On Error GoTo ErrorPoint

' Jeff Conrad - Access Junkie
' 1/28/05
' You are free to use this code in any projects
' so long as you admit you are an Access Junkie

Dim dbs As DAO.Database
Dim rst As DAO.Recordset

Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("tblActivityLog", , dbAppendOnly)

With rst
.AddNew
!fldObjectName = strObjectName
!fldTimeOpened = Now()
!fldUser = CurrentUser()
.Update
End With

ExitPoint:
On Error Resume Next
' Cleanup Code
rst.Close
Set rst = Nothing
Set dbs = Nothing
Exit Sub

ErrorPoint:
' Unexpected Error
MsgBox "The following error has occurred:" _
& vbNewLine & "Error Number: " & Err.Number _
& vbNewLine & "Error Description: " & Err.Description _
, vbExclamation, "Unexpected Error"
Resume ExitPoint

End Sub

3. Make sure you have a reference set to the DAO object library.

4. Compile the code, and save the module as basLogActivity.

5. Now put the following one line of code in EVERY form and report's
Open event that you want to track:

Call subLogActivity(Me.Name)

You will now see that whenever the form or report opens it will make a
record in the table and show what was opened, by whom, and when.
You can then make some nice queries and reports off this table information.
For example, you could do a query by a user and see everything they
have done. You could also query a specific database object and see
who opened it.


Also, here is some info by MVP Tony Toews:

http://www.granite.ab.ca/access/usagestatistics.htm

Hope that gets you going,
I used CurrentUser() because this question was asked in the Security
newsgroup. You could also capture the Network User name if you
would like or omit the field entirely if not needed.

Does that help?
--
Jeff Conrad
Access Junkie
Bend, Oregon


in message

 

[Leslie Isaacs]

Many thanks Douglas

Great that it's that simple - at least for the forms, reports and macros.
Any ideas for the queries?

Also, having used your query as a make-table query and then added the date
field [last used] to the resulting table (which I called [my reports]),
regarding the logic to update the date in the table I have set a report's
OnOpen event as:

DLookup([myreports]![last used], "[tables]![myreports]", "[name]= me.name")
= Date()

but (probably not surprisingly to you!) this didn't work. Am I close?

Thanks for your continued help.
Les

 

[Douglas J. Steele]

Because there's no Open event (or the equivalent) for queries, there's
really nothing you can do there.

DLookup is read-only, so that's not going to work. What you need is a Update
query:

CurrentDb.Execute "UPDATE MyReports SET LastUsed = " & _
Format(Now(), "\#mm\/dd\/yyyy hh\:nn\:ss\#") & _
" WHERE Name = '" & Me.Name & "'", dbFailOnError

For what it's worth, your DLookup was wrong anyhow. It should be:

DLookup("[last used]", [myreports]", "[name]= '" & me.name "'")


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Many thanks Douglas

Great that it's that simple - at least for the forms, reports and macros.
Any ideas for the queries?

Also, having used your query as a make-table query and then added the date
field [last used] to the resulting table (which I called [my reports]),
regarding the logic to update the date in the table I have set a report's
OnOpen event as:

DLookup([myreports]![last used], "[tables]![myreports]", "[name]=
me.name")
= Date()

but (probably not surprisingly to you!) this didn't work. Am I close?

Thanks for your continued help.
Les

The following query will give you a list of all of your reports:

SELECT Name
FROM MSysObjects
WHERE Type = -32764
ORDER BY Name

You could use that to populate your table.

In each report's Report_Open event, add logic to update the date in your
table.

You can do this with Forms and, I think, Macros as well, but not with
tables, queries or modules. Forms are Type = -32768 and Macros are Type
= -32766

 

[Leslie Isaacs]

Hello Douglas

Thanks for your further reply.

Pity about not being able to monitor query use: also surprising?

Regarding the update query you suggested, I pasted the code into the OnOpen
event of a report, but when I opened that report I got
"Runtime error 3061 - Too few parameters, expected 1"

The code I have is:

Private Sub Report_Open(Cancel As Integer)
CurrentDb.Execute "UPDATE MyReports SET LastUsed = " & Format(Now(),
"\#mm\/dd\/yyyy hh\:nn\:ss\#") & " WHERE Name = '" & Me.name & "'",
dbFailOnError
End Sub

Thanks also for the correction to my DLookup statement - I have never got
the hang of which-and-how-many delimeters!

Hope you can help with the above OnOpen error.

Many thanks
Les

BTW: don't suppose you can shed any light on my other problem in my posting
from yesterday titled Sharing Violation?

Because there's no Open event (or the equivalent) for queries, there's
really nothing you can do there.

DLookup is read-only, so that's not going to work. What you need is a Update
query:

CurrentDb.Execute "UPDATE MyReports SET LastUsed = " & _
Format(Now(), "\#mm\/dd\/yyyy hh\:nn\:ss\#") & _
" WHERE Name = '" & Me.Name & "'", dbFailOnError

For what it's worth, your DLookup was wrong anyhow. It should be:

DLookup("[last used]", [myreports]", "[name]= '" & me.name "'")


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Many thanks Douglas

Great that it's that simple - at least for the forms, reports and macros.
Any ideas for the queries?

Also, having used your query as a make-table query and then added the date
field [last used] to the resulting table (which I called [my reports]),
regarding the logic to update the date in the table I have set a report's
OnOpen event as:

DLookup([myreports]![last used], "[tables]![myreports]", "[name]=
me.name")
= Date()

but (probably not surprisingly to you!) this didn't work. Am I close?

Thanks for your continued help.
Les

The following query will give you a list of all of your reports:

SELECT Name
FROM MSysObjects
WHERE Type = -32764
ORDER BY Name

You could use that to populate your table.

In each report's Report_Open event, add logic to update the date in your
table.

You can do this with Forms and, I think, Macros as well, but not with
tables, queries or modules. Forms are Type = -32768 and Macros are Type
= -32766

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Hello All

I am using A97.

Is it possible with code to:

a) create a table with a record for each report on the db, with the (key)
field being set according to the report name;
b) record in a 2nd field in the above table the date that the
corresponding
report was last opened.

In fact I would like to do the above for all the db's tables, queries,
forms, macros and modules. The reason is that I know that many of these
items are completely redundant and could be deleted - so code that
would
delete the item (after prompting) after a specified period of
inactivity
(say 6 months) would be the icing on the cake!

Is this asking too much?

Many thanks
Leslie Isaacs

 

[Leslie Isaacs]

Hello Jeff

Many thanks for your reply. Your code worked a treat, and will give me
exactly what I need.

Just a thought: I don't suppose it is possible to use code to add:
Call subLogActivity(Me.Name)
to the OnOpen event of all my reports and forms, in one go?

I only ask because the db has millions of reports and forms (well not quite
but a helluvalot) - hence the need to monitor and delete the redundent ones!
Otherwise I'll just have to add the OnOpen event one by one.

Thanks again
Les

 

[Jeff Conrad]

in message:

Hello Jeff

Hi Leslie,

Many thanks for your reply.

Many welcomes.

Your code worked a treat, and will give me exactly what I need.

Good to hear.

Just a thought: I don't suppose it is possible to use code to add:
Call subLogActivity(Me.Name)
to the OnOpen event of all my reports and forms, in one go?

I only ask because the db has millions of reports and forms (well not quite
but a helluvalot) - hence the need to monitor and delete the redundant ones!
Otherwise I'll just have to add the OnOpen event one by one.

Not easily (if at all possible) because there could already be code in the
object's Open event and/or something on the Properties line for the
Open event (like a macro name). You could wipe out existing code
in the process by trying to automate this. Or it would be extremely
difficult to try and "stuff" this code inside the code module without
affecting any existing code.

So the short answer is no, you'll probably have to do this manually.
I'm more than happy to be proven wrong though.

 

[Douglas J. Steele]

Looks as though your field name has an embedded blank in it.

Try:

Private Sub Report_Open(Cancel As Integer)
CurrentDb.Execute "UPDATE MyReports SET [Last Used] = " & Format(Now(),
"\#mm\/dd\/yyyy hh\:nn\:ss\#") & " WHERE [Name] = '" & Me.name & "'",
dbFailOnError
End Sub

BTW, Name isn't a good choice for a field name: it's actually a reserved
word in Access.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hello Douglas

Thanks for your further reply.

Pity about not being able to monitor query use: also surprising?

Regarding the update query you suggested, I pasted the code into the
OnOpen
event of a report, but when I opened that report I got
"Runtime error 3061 - Too few parameters, expected 1"

The code I have is:

Private Sub Report_Open(Cancel As Integer)
CurrentDb.Execute "UPDATE MyReports SET LastUsed = " & Format(Now(),
"\#mm\/dd\/yyyy hh\:nn\:ss\#") & " WHERE Name = '" & Me.name & "'",
dbFailOnError
End Sub

Thanks also for the correction to my DLookup statement - I have never got
the hang of which-and-how-many delimeters!

Hope you can help with the above OnOpen error.

Many thanks
Les

BTW: don't suppose you can shed any light on my other problem in my
posting
from yesterday titled Sharing Violation?

Because there's no Open event (or the equivalent) for queries, there's
really nothing you can do there.

DLookup is read-only, so that's not going to work. What you need is a Update
query:

CurrentDb.Execute "UPDATE MyReports SET LastUsed = " & _
Format(Now(), "\#mm\/dd\/yyyy hh\:nn\:ss\#") & _
" WHERE Name = '" & Me.Name & "'", dbFailOnError

For what it's worth, your DLookup was wrong anyhow. It should be:

DLookup("[last used]", [myreports]", "[name]= '" & me.name "'")


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Many thanks Douglas

Great that it's that simple - at least for the forms, reports and macros.
Any ideas for the queries?

Also, having used your query as a make-table query and then added the date
field [last used] to the resulting table (which I called [my reports]),
regarding the logic to update the date in the table I have set a report's
OnOpen event as:

DLookup([myreports]![last used], "[tables]![myreports]", "[name]=
me.name")
= Date()

but (probably not surprisingly to you!) this didn't work. Am I close?

Thanks for your continued help.
Les



message
The following query will give you a list of all of your reports:

SELECT Name
FROM MSysObjects
WHERE Type = -32764
ORDER BY Name

You could use that to populate your table.

In each report's Report_Open event, add logic to update the date in your
table.

You can do this with Forms and, I think, Macros as well, but not with
tables, queries or modules. Forms are Type = -32768 and Macros are
Type
= -32766

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Hello All

I am using A97.

Is it possible with code to:

a) create a table with a record for each report on the db, with the
(key)
field being set according to the report name;
b) record in a 2nd field in the above table the date that the
corresponding
report was last opened.

In fact I would like to do the above for all the db's tables, queries,
forms, macros and modules. The reason is that I know that many of these
items are completely redundant and could be deleted - so code that
would
delete the item (after prompting) after a specified period of
inactivity
(say 6 months) would be the icing on the cake!

Is this asking too much?

Many thanks
Leslie Isaacs