If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Difficult one :) - Logging changes in my database made
Hi,
I have a database where different users would be able to change the value of certain fields. I would like to capture the name of that user, the date and time of the change and what was changed in a table so that i can report on this. (For auditing purposes) I have no idea how to go about this. I would imagine that i would have to create the following tables: tblUser - autUserID - txtUserName - txtUserPassword tblChanges - autChangeID - txtUserName (Linked with above table) - dateChangeDate - txtTableNameChanged (this would probably be the table that where the change happened) - txtFieldNameChanged (this would prob be the field in the table above that changed) - txtRecordIDChanged (thsi would probably be the PRIMARY KEY of the record in the table that was changed) My questions a 1 - Am i on the right track in terms of the structure as above? 2 - If so, how would i capture the old vs the new data - e.g. if in table "tblBooking" the value of the field "dateBookingDate" was changed from "1 March 2008" to "8 March 2008" - how would i capture this in my "tblChanges"? Would i have to add two extra fields; "txtOldValue" and "txtNewValue"? 3 - Lastly, how would my database know which user made the change? I imagine that if the user start's their FE they would have to be prompted for their UserName and Password and the database would have to remember this throughout the session? - How would i implement this? Many thanks for this. I know ask a lot of questions and that it is quite in-dept but i knwo this post will help a lot of users. Thanks, Wayne |
#2
|
|||
|
|||
Difficult one :) - Logging changes in my database made
See:
Audit Trail - Log changes at the record level at: http://allenbrowne.com/AppAudit.html -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "ant1983" wrote in message ... I have a database where different users would be able to change the value of certain fields. I would like to capture the name of that user, the date and time of the change and what was changed in a table so that i can report on this. (For auditing purposes) I have no idea how to go about this. I would imagine that i would have to create the following tables: tblUser - autUserID - txtUserName - txtUserPassword tblChanges - autChangeID - txtUserName (Linked with above table) - dateChangeDate - txtTableNameChanged (this would probably be the table that where the change happened) - txtFieldNameChanged (this would prob be the field in the table above that changed) - txtRecordIDChanged (thsi would probably be the PRIMARY KEY of the record in the table that was changed) My questions a 1 - Am i on the right track in terms of the structure as above? 2 - If so, how would i capture the old vs the new data - e.g. if in table "tblBooking" the value of the field "dateBookingDate" was changed from "1 March 2008" to "8 March 2008" - how would i capture this in my "tblChanges"? Would i have to add two extra fields; "txtOldValue" and "txtNewValue"? 3 - Lastly, how would my database know which user made the change? I imagine that if the user start's their FE they would have to be prompted for their UserName and Password and the database would have to remember this throughout the session? - How would i implement this? Many thanks for this. I know ask a lot of questions and that it is quite in-dept but i knwo this post will help a lot of users. Thanks, Wayne |
#3
|
|||
|
|||
Difficult one :) - Logging changes in my database made
As Allen points out, you can build a audit function into your Access
database. Another approach would be to use a more robust backend (e.g., SQL-Server), which provides this kind of auditing trail. Regards Jeff Boyce Microsoft Office/Access MVP "ant1983" wrote in message ... Hi, I have a database where different users would be able to change the value of certain fields. I would like to capture the name of that user, the date and time of the change and what was changed in a table so that i can report on this. (For auditing purposes) I have no idea how to go about this. I would imagine that i would have to create the following tables: tblUser - autUserID - txtUserName - txtUserPassword tblChanges - autChangeID - txtUserName (Linked with above table) - dateChangeDate - txtTableNameChanged (this would probably be the table that where the change happened) - txtFieldNameChanged (this would prob be the field in the table above that changed) - txtRecordIDChanged (thsi would probably be the PRIMARY KEY of the record in the table that was changed) My questions a 1 - Am i on the right track in terms of the structure as above? 2 - If so, how would i capture the old vs the new data - e.g. if in table "tblBooking" the value of the field "dateBookingDate" was changed from "1 March 2008" to "8 March 2008" - how would i capture this in my "tblChanges"? Would i have to add two extra fields; "txtOldValue" and "txtNewValue"? 3 - Lastly, how would my database know which user made the change? I imagine that if the user start's their FE they would have to be prompted for their UserName and Password and the database would have to remember this throughout the session? - How would i implement this? Many thanks for this. I know ask a lot of questions and that it is quite in-dept but i knwo this post will help a lot of users. Thanks, Wayne |
#4
|
|||
|
|||
Difficult one :) - Logging changes in my database made
Hi Allen,
I followed your steps exactly as you pointed out but am getting an error when i close my form (after i have made the change) The error reads: "Ambiguous name detected: AuditEditBegin" and it stops at the BeforeUpdate Line... "Allen Browne" wrote: See: Audit Trail - Log changes at the record level at: http://allenbrowne.com/AppAudit.html -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "ant1983" wrote in message ... I have a database where different users would be able to change the value of certain fields. I would like to capture the name of that user, the date and time of the change and what was changed in a table so that i can report on this. (For auditing purposes) I have no idea how to go about this. I would imagine that i would have to create the following tables: tblUser - autUserID - txtUserName - txtUserPassword tblChanges - autChangeID - txtUserName (Linked with above table) - dateChangeDate - txtTableNameChanged (this would probably be the table that where the change happened) - txtFieldNameChanged (this would prob be the field in the table above that changed) - txtRecordIDChanged (thsi would probably be the PRIMARY KEY of the record in the table that was changed) My questions a 1 - Am i on the right track in terms of the structure as above? 2 - If so, how would i capture the old vs the new data - e.g. if in table "tblBooking" the value of the field "dateBookingDate" was changed from "1 March 2008" to "8 March 2008" - how would i capture this in my "tblChanges"? Would i have to add two extra fields; "txtOldValue" and "txtNewValue"? 3 - Lastly, how would my database know which user made the change? I imagine that if the user start's their FE they would have to be prompted for their UserName and Password and the database would have to remember this throughout the session? - How would i implement this? Many thanks for this. I know ask a lot of questions and that it is quite in-dept but i knwo this post will help a lot of users. Thanks, Wayne |
#5
|
|||
|
|||
Difficult one :) - Logging changes in my database made
On Wed, 19 Mar 2008 08:42:03 -0700, ant1983
wrote: "Ambiguous name detected: AuditEditBegin" and it stops at the BeforeUpdate Line... That suggests that you have two instances of Sub AuditEditBegin or two labels AuditEditBegin: somewhere in your code. Use the binoculars to search for them. Edit the code so that there isn't an ambiguous subroutine name or label and you should be fine. -- John W. Vinson [MVP] |
#6
|
|||
|
|||
Difficult one :) - Logging changes in my database made
Allen,
I tried this fix with my own db and while performing the debug/compile function I get an error "user defined type not defined" and it highlights the section of code that reads: Dim db As DAO.database ' Current database Dim sSQL As String ' Append query. is DAO.database supposed to be where I put the name of my database? If so, I tried that and my database file name has an apostrophe in it ... is that causing the problem and should I rename my db so that isn't an issue? Thank you, Joseph "Allen Browne" wrote: See: Audit Trail - Log changes at the record level at: http://allenbrowne.com/AppAudit.html -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "ant1983" wrote in message ... I have a database where different users would be able to change the value of certain fields. I would like to capture the name of that user, the date and time of the change and what was changed in a table so that i can report on this. (For auditing purposes) I have no idea how to go about this. I would imagine that i would have to create the following tables: tblUser - autUserID - txtUserName - txtUserPassword tblChanges - autChangeID - txtUserName (Linked with above table) - dateChangeDate - txtTableNameChanged (this would probably be the table that where the change happened) - txtFieldNameChanged (this would prob be the field in the table above that changed) - txtRecordIDChanged (thsi would probably be the PRIMARY KEY of the record in the table that was changed) My questions a 1 - Am i on the right track in terms of the structure as above? 2 - If so, how would i capture the old vs the new data - e.g. if in table "tblBooking" the value of the field "dateBookingDate" was changed from "1 March 2008" to "8 March 2008" - how would i capture this in my "tblChanges"? Would i have to add two extra fields; "txtOldValue" and "txtNewValue"? 3 - Lastly, how would my database know which user made the change? I imagine that if the user start's their FE they would have to be prompted for their UserName and Password and the database would have to remember this throughout the session? - How would i implement this? Many thanks for this. I know ask a lot of questions and that it is quite in-dept but i knwo this post will help a lot of users. Thanks, Wayne |
#7
|
|||
|
|||
Difficult one :) - Logging changes in my database made
More than likely, you don't have a reference to DAO set in your database.
You do that by going to Tools References and scroll down to Microsoft DAO 3.6 Object Library. Check the box and click OK. -- --Roger Carlson MS Access MVP Access Database Samples: www.rogersaccesslibrary.com Want answers to your Access questions in your Email? Free subscription: http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L "joseph" wrote in message ... Allen, I tried this fix with my own db and while performing the debug/compile function I get an error "user defined type not defined" and it highlights the section of code that reads: Dim db As DAO.database ' Current database Dim sSQL As String ' Append query. is DAO.database supposed to be where I put the name of my database? If so, I tried that and my database file name has an apostrophe in it ... is that causing the problem and should I rename my db so that isn't an issue? Thank you, Joseph "Allen Browne" wrote: See: Audit Trail - Log changes at the record level at: http://allenbrowne.com/AppAudit.html -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "ant1983" wrote in message ... I have a database where different users would be able to change the value of certain fields. I would like to capture the name of that user, the date and time of the change and what was changed in a table so that i can report on this. (For auditing purposes) I have no idea how to go about this. I would imagine that i would have to create the following tables: tblUser - autUserID - txtUserName - txtUserPassword tblChanges - autChangeID - txtUserName (Linked with above table) - dateChangeDate - txtTableNameChanged (this would probably be the table that where the change happened) - txtFieldNameChanged (this would prob be the field in the table above that changed) - txtRecordIDChanged (thsi would probably be the PRIMARY KEY of the record in the table that was changed) My questions a 1 - Am i on the right track in terms of the structure as above? 2 - If so, how would i capture the old vs the new data - e.g. if in table "tblBooking" the value of the field "dateBookingDate" was changed from "1 March 2008" to "8 March 2008" - how would i capture this in my "tblChanges"? Would i have to add two extra fields; "txtOldValue" and "txtNewValue"? 3 - Lastly, how would my database know which user made the change? I imagine that if the user start's their FE they would have to be prompted for their UserName and Password and the database would have to remember this throughout the session? - How would i implement this? Many thanks for this. I know ask a lot of questions and that it is quite in-dept but i knwo this post will help a lot of users. Thanks, Wayne |
#8
|
|||
|
|||
Difficult one :) - Logging changes in my database made
Hi Allen
I coppied your code and followed instructions on creating an audit trail when Itry to run it I get the error message "Compile error Su or Function not defined" When I clcik ok it is taking me to call AuditEnd in the BeforeUpdate Sub What am I doing wrong Linda "Allen Browne" wrote: See: Audit Trail - Log changes at the record level at: http://allenbrowne.com/AppAudit.html -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "ant1983" wrote in message ... I have a database where different users would be able to change the value of certain fields. I would like to capture the name of that user, the date and time of the change and what was changed in a table so that i can report on this. (For auditing purposes) I have no idea how to go about this. I would imagine that i would have to create the following tables: tblUser - autUserID - txtUserName - txtUserPassword tblChanges - autChangeID - txtUserName (Linked with above table) - dateChangeDate - txtTableNameChanged (this would probably be the table that where the change happened) - txtFieldNameChanged (this would prob be the field in the table above that changed) - txtRecordIDChanged (thsi would probably be the PRIMARY KEY of the record in the table that was changed) My questions a 1 - Am i on the right track in terms of the structure as above? 2 - If so, how would i capture the old vs the new data - e.g. if in table "tblBooking" the value of the field "dateBookingDate" was changed from "1 March 2008" to "8 March 2008" - how would i capture this in my "tblChanges"? Would i have to add two extra fields; "txtOldValue" and "txtNewValue"? 3 - Lastly, how would my database know which user made the change? I imagine that if the user start's their FE they would have to be prompted for their UserName and Password and the database would have to remember this throughout the session? - How would i implement this? Many thanks for this. I know ask a lot of questions and that it is quite in-dept but i knwo this post will help a lot of users. Thanks, Wayne |
Thread Tools | |
Display Modes | |
|
|