By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,773 Members | 2,583 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,773 IT Pros & Developers. It's quick & easy.

Access 2003 Report log

P: n/a
I have multiple reports in one database, on one form. I need to know
what reports were run when, and by Network User ID. How do I do that?
Nov 13 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
Create a table with fields for the date/time, report name, and network user
id.

Use the Open event of the report to Execute an append query statement to add
the record to the table. You can mock up an append query in the query design
grid (Append on Query menu), and then swith it to SQL View (View menu) to
see the string you need to create.

The network user ID is an API call. Details:
http://www.mvps.org/access/api/api0008.htm

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Jarrod" <ja*****@dayspring.com> wrote in message
news:3c**************************@posting.google.c om...
I have multiple reports in one database, on one form. I need to know
what reports were run when, and by Network User ID. How do I do that?

Nov 13 '05 #2

P: n/a
On Tue, 10 Aug 2004 11:13:44 +0800, "Allen Browne"
<Al*********@SeeSig.Invalid> wrote:

Hi Allen,
I liked your advice until the last part. I think executing in-line SQL
statements is a last resort; you seem to make it a first one. The QBE
grid can be a final disign interface, rather than a mockup one.
Rather execute the append query like this pseudo-code (if using DAO)
dim qd as dao.querydef
set qd=currentdb.openquerydef("my_append_query")
qd!parameter1 = value1
qd!parameter2 = value2
'etc.
qd.execute dbFailOnError
qd.close
set qd=nothing

This way, you get the benefits of pre-compilation, as well as an extra
level of indirection (not always needed)

-Tom.

Create a table with fields for the date/time, report name, and network user
id.

Use the Open event of the report to Execute an append query statement to add
the record to the table. You can mock up an append query in the query design
grid (Append on Query menu), and then swith it to SQL View (View menu) to
see the string you need to create.

The network user ID is an API call. Details:
http://www.mvps.org/access/api/api0008.htm


Nov 13 '05 #3

P: n/a
Hi Tom

Can respect your approach. It may be particularly appropriate in an
environment that uses stored procedures, or where the same query needs to be
executed from multiple places in the code.

My personal preference is to build the SQL string dynamically, mostly for
ease of maintenance, i.e.
- The code is more portable: can copy just the module or procedure.

- The code is self documenting, i.e. you can see the SQL and understand what
is happening.

- Avoids maintaining dozens (hundreds?) of these kinds of queries in most
apps.

- Avoids dependencies (e.g. can I delete this query if I delete this code,
or is the query needed elsewhere also?)

In general, dynamic SQL statements are *much* more powerful than saved
queries, e.g.:
- You can build the WHERE clause dependant on what controls have values at
runtime, instead of declaring a parameter for each ahead of time.

- You can decide on operators at runtime, e.g. where the user may want to
specify a range, and the operators may be <, >, Between, or none at all,
depending on whether they enter the begin value, end value, both, or
neither.

- Join types, whether to include related tables, placement of subqueries,
and so on can all be decided at runtime.

I don't believe that query compilation time was ever an issue, even on a
386. Even it if is significant, an argument could be made that the data
during development and testing is so different from the end user's data that
a precompiled query saves the *wrong* plan. If the data set is of any size,
the time taken to execute the wrong plan would be much greater than the time
it would take to calculate the appropriate query plan at runtime.

So, horses for courses. I'm not trying to convince you to change your
practice; just that each approach has its own benefits.

Have fun.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Tom van Stiphout" <no*************@cox.net> wrote in message
news:40********************************@4ax.com...
On Tue, 10 Aug 2004 11:13:44 +0800, "Allen Browne"
<Al*********@SeeSig.Invalid> wrote:

Hi Allen,
I liked your advice until the last part. I think executing in-line SQL
statements is a last resort; you seem to make it a first one. The QBE
grid can be a final disign interface, rather than a mockup one.
Rather execute the append query like this pseudo-code (if using DAO)
dim qd as dao.querydef
set qd=currentdb.openquerydef("my_append_query")
qd!parameter1 = value1
qd!parameter2 = value2
'etc.
qd.execute dbFailOnError
qd.close
set qd=nothing

This way, you get the benefits of pre-compilation, as well as an extra
level of indirection (not always needed)

-Tom.

Create a table with fields for the date/time, report name, and network
user
id.

Use the Open event of the report to Execute an append query statement to
add
the record to the table. You can mock up an append query in the query
design
grid (Append on Query menu), and then swith it to SQL View (View menu) to
see the string you need to create.

The network user ID is an API call. Details:
http://www.mvps.org/access/api/api0008.htm

Nov 13 '05 #4

P: n/a
Tom van Stiphout <no*************@cox.net> wrote in
news:40********************************@4ax.com:
On Tue, 10 Aug 2004 11:13:44 +0800, "Allen Browne"
<Al*********@SeeSig.Invalid> wrote:

Hi Allen,
I liked your advice until the last part. I think executing in-line SQL
statements is a last resort; you seem to make it a first one. The QBE
grid can be a final disign interface, rather than a mockup one.
Rather execute the append query like this pseudo-code (if using DAO)
dim qd as dao.querydef
set qd=currentdb.openquerydef("my_append_query")
qd!parameter1 = value1
qd!parameter2 = value2
'etc.
qd.execute dbFailOnError
qd.close
set qd=nothing

This way, you get the benefits of pre-compilation, as well as an extra
level of indirection (not always needed)

-Tom.


Tom

If you create such a query, it lives somewhere and the user has permissions
to execute it?
Could the user execute it from another application, or from the same
application in a different context?
If so (and I'm not sure that is is so) would an in-line SQL statement in
and MDE or ADE manifestation of the application better guard against such
an eventuality?

I guess this is my foible of the month, but I am becoming more and more
concerned about the security of backend or server-side database procedures,
such as queries, sprocs or udfs, and even tables. If they can be run from
Application A, then why not from Application B? While we may have created
Application A, and written safe code for it, someone else may create
Application B, and our data may not be secure.

--
Lyle
--
use iso date format: yyyy-mm-dd
http://www.w3.org/QA/Tips/iso-date
--
The e-mail address isn't, but you could use it to find one.
Nov 13 '05 #5

P: n/a
Thanks for the help, guys. One more thing that I haven't been able to
find. I would rather not go into all 50 or so reports to add the code
you've given me. Is there a way to pass the report name from the
form, possibly using the button I click to open that report.

What I am wanting, as a finished product, is a list of each report
(button) clicked, by whom, and when. I would like to just write the
code once, putting it in the form, not the reports, then pass the
report name somehow, and then insert the data into my table. Any
thoughts?

Thanks for your help!
Nov 13 '05 #6

P: n/a
Jarrod, set the On Open property of all your reports to:
=LogTheReport([Report])

In a general module, write the function that does the logging:
Private Function LogTheReport(rpt As Report)
and in the function you can refer to:
rpt.Name

That allows your reports to be lightweight, has only one place to maintain
the code, and lets you get at any property of the report that you wish.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Jarrod" <ja*****@dayspring.com> wrote in message
news:3c*************************@posting.google.co m...
Thanks for the help, guys. One more thing that I haven't been able to
find. I would rather not go into all 50 or so reports to add the code
you've given me. Is there a way to pass the report name from the
form, possibly using the button I click to open that report.

What I am wanting, as a finished product, is a list of each report
(button) clicked, by whom, and when. I would like to just write the
code once, putting it in the form, not the reports, then pass the
report name somehow, and then insert the data into my table. Any
thoughts?

Thanks for your help!

Nov 13 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.