change log (1 Viewer)

[benh76135]

After looking at how a few change logs have been made. I'm thinking of a different way to do it. Effectiveness aside, I can test it to see how it runs. My current idea is to have all the tables on my backend "worth checking" to have a user and date/time field added. Then run a query to find the "X number" of most recent changes. The x number could be a number or everything in past week ect. Where I'm stuck is making that query populate a table on the backend to store the data say once every 10 minutes.

My setup I have a server which i work from the has the backend for everyone and my front end which is massive. I have no performance issues, hell i run my cad program, test other front end programs, browse the web for information, while running my large front end program that shows me up to the minute information. The other 10 users have desktop pc's that i need to improve hardware on but they cost $50 each so cant complain, and they work.

Only 3 users in total need to see this change log so the info being sorted by my server and stored / removed from backend would help data flow over the network.

My assumption right now is to create a new front end to run a simple main page with an on timer event that will read said change log query and post it to table while removing old information from table to limit bloating of data.

I don't bother posting unless I believe I cant find what i need and think someone else may have a use for what I'm doing as well.

 

[Ranman256]

Are you storing a record of the person,and what query they ran?
Or
Are you storing ALL the data results the user ran?

If the latter,why?
If the former,then it's 1 record,and the backend can hold millions of records.

 

[The_Doc_Man]

At my last job before I retired, we had a change log set up, but we got into the discussion of how much we needed to keep. I don't claim this to be uniform nomenclature in the industry as a whole, but at our site we differentiated between reconstructive logs and forensic logs.

With our main personnel database, we had reconstructive logs that could return a database to its actual state at a given time of day. I.e. a step-by-step rollback log showing the before and after of each change. This log was so big that it accounted for megabytes (I don't recall how many) per day.

For my other database, we decided that all we really wanted was to know who was touching what tables and whether they were doing inserts, updates, deletes, or selects. That was our forensic log. It left a trail to see who was doing something at a given time, but we didn't try to make that detailed enough to reconstruct the DB. We took regular backups so it would have been possible to step back a day, restore the file, and then ask everyone who had done any updates to reapply them - but it only happened once that we had to take it that far.

When doing your logging, decide what you need to do with your log before you build the logger mechanism. It will make the difference between megabytes per day and kilobytes per day.

 

[Ranman256]

Then I suggest a backup db. A separate db that can hold the old records, but attached to the current db. This way you can keep the size smaller,and still access the log.

 

[benh76135]

For an example to answer the first question.....

Owner1 enters a purchase order for an item that does not exist

Owner2 is supposed to create that item, and update.

Guywhodoeseverything<me> comes in and edits that purchase order

Log would show that I was the last person to edit that record. Just the simple fact of knowing an "old" record was modified is all I'm doing.

So if the Sawguy for some reason changes a purchase order... something is wrong he should not touch them... ect

Said query to make this would only be on my machine to populate a backend table that could then be accessed from 3 places to see recent changes.

I've been doing a backup db just for the event of emergency..

For reconstruction / forensic logs. im not going that far. Our information is not very complex. Tracking who did what where and how many. 6 employees right now, max we had is 14.

just been stuck on making a VBA timer event to take said query of items , clear out change log table of no more that 30 records <no clue on this number> then repopulate the table with the most current 30 events.

 

[The_Doc_Man]

No, maybe you are approaching this wrong. Just as a confirmation of approach:
If your users actually can see & interact with the raw tables, you are asking for a headache anyway because you can't track ANYTHING. Nobody should see anything except functional forms launched by a default opening form, like a switchboard or dispatcher form.

just been stuck on making a VBA timer event to take said query of items

If all your users see are forms with action buttons to save or cancel whatever that form does for you, then you make your log entry when your users click the action button that actually changes something (and don't bother with a log if they clicked the CANCEL option). No timer needed; this is a totally asynchronous method. Take the data from the form where the dirty deed was done and at the time of doing it. You've got the events you need right there, including either the control_Click event or the Form_AfterUpdate event. Certainly that is no worse than a timer event, and you don't have to worry about wasted timer-event cycles or testing.

 

[benh76135]

Honestly I hate posting on forums because it seams like an argument, I try to stick to just reading them.

Yes everyone uses a front end switchboard that i call a startup form, which leads to forms to enter data. The back end tables are not viewed directly by anyone.

If it helps, the back end is 70 meg after 4 years. To me it does not sound like a lot, and I could remove two years of data to another location to improve performance. Just have not needed to do that yet.

The second half of your reply i get what your saying, and I agree on the operation sounds much better using an after update event instead of on timer event. Wouldn't this method lead to a much larger amount of data?

 

[The_Doc_Man]

Depends on the timer event's complexity. If the timer code is smart enough to only write changes, you probably would see no difference. If the timer event takes snapshots of things that haven't changed, perhaps it would write more on the timer than on the AfterUpdate event. However, the system load on the back end would drastically decrease overall since the timer has to look for things that have changed (implying a SELECT or two) whereas the AfterUpdate KNOWS that something has changed - and WHAT has changed. A lot less overhead involved to discover changes in that case.

Please don't take my other comment about switchboards as an attempt to be argumentative. It is just that often we discover people who DON'T use switchboards or otherwise hide the inner table structure and then wonder why their data so often gets hosed. I had to verify that you understood that factor.

 

[benh76135]

I get what you're saying there. After update knows there is a change and on timer has to look for a change. Makes perfect sense.

I'm kind of stuck right now I haven't had time to edit into my backend all the extra fields on main tables. Will have to do it after hours and im planning on buying a new server this week may try to do the update on it when and if i can pick it up. Current sever works great but this thing is a good deal and will be overkill plus cant hurt to have a backup system.

Appreciate your insight~

 

[jdraw]

Honestly I hate posting on forums because it seams like an argument, I try to stick to just reading them.

Hmmm??

I have found that you can learn something from just about anyone if you listen long enough.

Don't confuse a substantive question with an argument. Better to disregard a comment from a point of knowledge than to treat it like a challenge.

Good luck with your project.