I have an Access 2000 database that is used by 80 users. I am having serious corruption issues on a daily basis. It usually always turns out being one particular table that contains a number of memo fields. I need to use memo fields because text fields are not big enough. There is a lot of mass updating querries going on during the day and I am assuming people have certain records open in the table when these mass querries are run by other people and the result is a corrupted record. My problem is that I can't track down which querries are being run and by who or at what time. Does anyone know of a way or have some code that I could use that would write out to some sort of a log file telling me as much details as possible when a table is updated. If I could get the name of the querries being run then I might be able to resolve the problem. Any help or ideas would be greatly appreciated. Thanks
Tracking Corruption
- Thread starter loki
- Start date
loki,
You can create a table with username, time and event.
If users are "captive" then right before your code runs a
form or report you can make an entry to the table.
If users have access to the database window then the best
you can do is use the OnLoad events to make your entries.
If they were just running queries I think that would go
undetected.
By the way, how do you recognize the corruption and pinpoint
the time it occurred?
Wayne
You can create a table with username, time and event.
If users are "captive" then right before your code runs a
form or report you can make an entry to the table.
If users have access to the database window then the best
you can do is use the OnLoad events to make your entries.
If they were just running queries I think that would go
undetected.
By the way, how do you recognize the corruption and pinpoint
the time it occurred?
Wayne
- Local time
- Yesterday, 18:32
- Joined
- Feb 28, 2001
- Messages
- 31,055
One approach is to make it "easier" for your users to use your common queries. Build a switchboard form that presents them with dozens of little buttons that they can click to run their favorite queries. Tell them you made a "shortcut" for them because these queries are used so often. Make sure you use the command-button wizard to do this because it will automatically build some code for you behind each button.
Behind the scenes of every button, add code that updates a separate log table every time they run that query. If you make it easier for them to run their favorite queries, you can also make it easier for yourself to TRACK the running of those queries. You will look like a hero!
This of course depends on your ability to sell this to your 80 users. And unfortunately, there is currently no way with which I am familiar to track who runs a query from the DB window. Access is not a "complete" database system in the level of audits that it performs. Unless you can restrict access to this window, you are not going to get very far.
Behind the scenes of every button, add code that updates a separate log table every time they run that query. If you make it easier for them to run their favorite queries, you can also make it easier for yourself to TRACK the running of those queries. You will look like a hero!
This of course depends on your ability to sell this to your 80 users. And unfortunately, there is currently no way with which I am familiar to track who runs a query from the DB window. Access is not a "complete" database system in the level of audits that it performs. Unless you can restrict access to this window, you are not going to get very far.
