Logging ADO operations

[duluter]

Hi.

I've been thinking about creating a log of all transactions in my database as a safeguard against data corruption. If I had a log that consisted of ordered SQL statements, the database could be rebuilt up to the last successful update via those logged SQL statements, right?

In my database, all updates to the data are done through unbound forms via ADO. Is was thinking that each update done through ADO could somehow get logged in a separate file, like a running text file that basically contained sql statements that represented each of the transactions that have happened in the database.

Has anyone done anything like this? I'm purely in Access (my database is split into a front and back end), so no SQL Server stuff is allowed.

Whenever data tables are updated via ADO, behind the scenes an SQL statement is generated, right? Is there any way to get at these generated SQL statements so I can save them to a text file (or as individual records in a separate database)?

Or is there another solution (other than backing the database up regularly, which I also do)? I'm paranoid about losing data and want to do everything I can to prevent it.


Duluter

 

[HiTechCoach]

Hi.

I've been thinking about creating a log of all transactions in my database as a safeguard against data corruption. If I had a log that consisted of ordered SQL statements, the database could be rebuilt up to the last successful update via those logged SQL statements, right?

In my database, all updates to the data are done through unbound forms via ADO. Is was thinking that each update done through ADO could somehow get logged in a separate file, like a running text file that basically contained sql statements that represented each of the transactions that have happened in the database.

Has anyone done anything like this? I'm purely in Access (my database is split into a front and back end), so no SQL Server stuff is allowed.

Whenever data tables are updated via ADO, behind the scenes an SQL statement is generated, right? Is there any way to get at these generated SQL statements so I can save them to a text file (or as individual records in a separate database)?

Or is there another solution (other than backing the database up regularly, which I also do)? I'm paranoid about losing data and want to do everything I can to prevent it.


Duluter

Duluter,

How will you keep the current log file from getting corrupted?

You may want to look into building some type of Audit Trail.

See this previous post:

Audit Trail Idea

One of the advantages of Access is using bound forms. Audit trails can be used with bound forms. I have been working with Access since version 2.0 first came out. I have not really found any advantage to unbound forms in all these yers of wrking with Access. Of the 1000's of forms I have created, I have made less than five forms unbound for data entry. I have found that unbound forms tend to have a lot more disadvantages then advantages form most data entry.


Hope this helps ...

 

[Vonnie]

No matter how small your database is, you must back it up. The larger it is, the more users, the more frequently you should be backing it up. If any of your data contains money data, or legal data, then I would have more than one copy of a backup. all hard drives fail eventually, all databases are vulnerable.