如何获取在Access数据库中已更改的记录详细信息 [英] How to get the record details which has been changed in Access database

问题描述

我有一个Access数据库，其中有许多表和成千上万的记录，如果任何人更改其中的任何数据，任何行，甚至只有一个单元格，有什么方法来了解什么特定的行或单元格在Access数据库中更改，任何属性或应该使用任何触发器？

解决方案

回到MSSQL数据库，我似乎记得ACCESS（MDB）和SQL（MDF）数据库不是继承支持这一点。如果我没有错误，你必须采用这些原则：

1）事务数据不应该被添加，只引用包含更改值的新记录的历史记录。我从三个角度来看：

• 我有一个名为PrecursorRecId的列，它保存了
  最新记录的记录


• 另一个列和Context_ID，其中所有相关记录
  由公共值链接，该公共值是
  AuditTxnContext表的键ID列。 li>
  
• 记录的TxnDate（很符合逻辑）

2）主数据记录也没有更改，具有EffectiveFromDate和EffectiveToDate，其中具有NULL EffectiveToDate的记录被认为是当前的。根据表的敏感性，MaterData记录的创建伴随着审核条目记录。再次，有一个RecordCreatedDateTime，它不会自动匹配EffectiveFromDate戳。

这可能会帮助您前进，但没有太多的帮助您现有的数据。我也不太了解你是什么工作，所以我的输入是相当通用的。我希望它帮助任何方式。如果我错误，我会欢迎任何人的意见，这只是我们如何迎接我们的挑战。

如果你还在处理它，告诉我一点。

Mac

如果没有，请关闭您的问题。

i am having an Access database with many tables and thousands of records in it, if anybody changes any data in it , any row, or even only a cell, is there any way to get to know what specific row or cell has been changed in the Access database, any property or should I use any trigger ?

解决方案

I had a similar problem a couple of years back with an MSSQL DB and I seem to recall that both ACCESS (MDB) and SQL (MDF) Databases DON't inherantly support this. You, if I am not mistaken, will have to adopt the principles:

1) Transactional Data should NEVER be adited, only reference historical records with new records containing the changed values. I took it from three angles :

• I had a column called PrecursorRecId which held the record_id of the most recent record the update related to.
• Another column held and Context_ID under which all related records were linked by a common value which was the key Id column of AuditTxnContext table.
• Record's TxnDate (pretty logical)

2) Master Data records Also were not altered but each record had an EffectiveFromDate and and EffectiveToDate where the records with a NULL EffectiveToDate were considered current. Depending on the sensiitivity of the table, creation of MaterData records was accompanied by Audit Entry records. Again, there is a RecordCreatedDateTime which does not automatically match the EffectiveFromDate stamp.

This may help you going forward but there's not much to assist with your existing data. I also don't have much idea of what it is you are working on so my input is fairly generic. I hope it helps any way. I would welcome anyone's input if I am in error, this is just how we approached our challenge.

Tell me a bit more if you are still dealing with it. If not, maybe close your question.

Cheers

Mac

这篇关于如何获取在Access数据库中已更改的记录详细信息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！