Usage Log

[tkelley via AccessMonster.com]

I have inherited a monolith of a database in Access97 (basically it's a f/e
to SQL Sever). I'm in the middle of converting to 03 and testing. So I have
users in 97 and in 03. I have a function that writes to a table in SQL
server with version info, name, etc., each time a user opens an object so
that I can see what gets used, how often, which version, etc. I'll also use
it in a couple of months to start identifying obsolete objects.

I've been calling the code from the Open event of my forms. But I want to do
it for reports too ... and there must be 6 million of them. Is there a
better way to accomplish my goal of a usage log, that allows me to pass
certain values I want in the log? Is there a global function that can be
called without having to actually go into every object's code to enter the
calling code in the open event?

thanks

 

[mscertified]

I fail to see the point in doing this. I dont see how it will isolate
obsolete objects.
I doubt whether an Access db can accomodate anywhere near 6 million reports.
SQL Server should be able to log writes and reads to its tables, however, for
Access reports you are on your own. The best you could do is to open all
reports via a common function, then you would only need the code in one place
e.g.

public function openreport(reportname as string) as long
call loggingfunction(reportname)
docmd.openreport reportname
end function

you could use find/replace to see where all reports are opened.

-Dorian

 

[tkelley via AccessMonster.com]

Thanks for the reply. I guess my exaggeration wasn't obvious enough. Of
course there aren't 6 million reports. ;-)

1) If you don't understand how logging usage will identify objects that are
no longer used, then how would you suggest knowing what objects (forms and
reports) are no longer used? Or did I misuse the word obsolete? Or maybe
there are tools of which I'm unaware. I'd like to hear your suggestions.

2) Your idea of launching from one place is good, and being done in some
cases. However it doesn't address sub-reports.

Keep in mind that this database is very poorly designed, even more poorly
documented, way too complex, and eight years old. I've been here for six
months. I'm just trying to get some information on what gets used, and by
whom.

I was skeptical that there would be a good solution. But I had to ask.

I fail to see the point in doing this. I dont see how it will isolate
obsolete objects.
I doubt whether an Access db can accomodate anywhere near 6 million reports.
SQL Server should be able to log writes and reads to its tables, however, for
Access reports you are on your own. The best you could do is to open all
reports via a common function, then you would only need the code in one place
e.g.

public function openreport(reportname as string) as long
call loggingfunction(reportname)
docmd.openreport reportname
end function

you could use find/replace to see where all reports are opened.

-Dorian

I have inherited a monolith of a database in Access97 (basically it's a f/e
to SQL Sever). I'm in the middle of converting to 03 and testing. So I have

[quoted text clipped - 11 lines]

thanks

 

[dch3]

If you haven't done this...

I would create a Global Sub that takes a couple of parameters (which I think
you're already doing) and just insert a record into the SQLServer table that
you're using. In the FORM & REPORT open event, I would call the SUB and pass
in the variables. Don't worry about any subforms/subreports as its the
parents that you're concerned with - get that information and identifying
unused subfrm/subreports will be easy enough. You can use Me.Name to avoid
having to retype the object's name everytime so that you can just copy &
paste. MZTools does have a handy function for commonly used code.

Thanks for the reply. I guess my exaggeration wasn't obvious enough. Of
course there aren't 6 million reports. ;-)

1) If you don't understand how logging usage will identify objects that are
no longer used, then how would you suggest knowing what objects (forms and
reports) are no longer used? Or did I misuse the word obsolete? Or maybe
there are tools of which I'm unaware. I'd like to hear your suggestions.

2) Your idea of launching from one place is good, and being done in some
cases. However it doesn't address sub-reports.

Keep in mind that this database is very poorly designed, even more poorly
documented, way too complex, and eight years old. I've been here for six
months. I'm just trying to get some information on what gets used, and by
whom.

I was skeptical that there would be a good solution. But I had to ask.

I fail to see the point in doing this. I dont see how it will isolate
obsolete objects.
I doubt whether an Access db can accomodate anywhere near 6 million reports.
SQL Server should be able to log writes and reads to its tables, however, for
Access reports you are on your own. The best you could do is to open all
reports via a common function, then you would only need the code in one place
e.g.

public function openreport(reportname as string) as long
call loggingfunction(reportname)
docmd.openreport reportname
end function

you could use find/replace to see where all reports are opened.

-Dorian

I have inherited a monolith of a database in Access97 (basically it's a f/e
to SQL Sever). I'm in the middle of converting to 03 and testing. So I have

[quoted text clipped - 11 lines]

thanks