How do I store which user modified a record in Access 2007?

  • Thread starter Thread starter Suddi
  • Start date Start date
S

Suddi

I am new to Access and have an Access 2003 database without user-level
security that I am planning to convert to Access 2007. I would like to add
fields to my database to store create_userid and modify_userid. Can someone
help me with that please?
 
The general concept is that you'd add fields to each table for which you
wish to keep this information.

The fields I generally add are:
[CreateDate]
[CreatedBy]
[LastUpdated]
[UpdatedBy]

Then I add code in each form that displays table data. That code checks to
see if the data is a new record or is an update to an existing record, and
updates the above fields accordingly.

Another way might be the audit trail approach that Allen Browne describes
(check on-line).

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
You then also have a login screen you design at the beginning of the
application that has the user log in or select their name from a dropdown.
The application remembers this and uses this in the fields that track "Who
made the change". Or you try and grab the username from Active Directory
security. Use audit trail if you need to track EVERY change.

My two cents to add to Jeff's description (which is how I do it),
I would usually use an employeeID in the various tables (for CreatedBY and
UpdatedBY) and have tblEmployee,
I don't use the active directory method (but it is an approach that might
fit your situation),
Mark Andrews
RPT Software
http://www.rptsoftware.com
http://www.donationmanagementsoftware.com

Jeff Boyce said:
The general concept is that you'd add fields to each table for which you
wish to keep this information.

The fields I generally add are:
[CreateDate]
[CreatedBy]
[LastUpdated]
[UpdatedBy]

Then I add code in each form that displays table data. That code checks
to see if the data is a new record or is an update to an existing record,
and updates the above fields accordingly.

Another way might be the audit trail approach that Allen Browne describes
(check on-line).

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Suddi said:
I am new to Access and have an Access 2003 database without user-level
security that I am planning to convert to Access 2007. I would like to
add
fields to my database to store create_userid and modify_userid. Can
someone
help me with that please?
Click to expand...
Click to expand...
 
Mark

I actually use a blend, grabbing the network username (initials in some
implementations) on-demand, as it were. Since someone would have to have
logged into the network on the PC, the PC that's running the app uses the
username of that logged on person.

(... and if we ever have to track down someone who messed with a record, and
they claim they were not on their PC at that time, we've got them nailed for
walking off and leaving their PC unprotected!)

Regards

Jeff B.

Mark Andrews said:
You then also have a login screen you design at the beginning of the
application that has the user log in or select their name from a dropdown.
The application remembers this and uses this in the fields that track "Who
made the change". Or you try and grab the username from Active Directory
security. Use audit trail if you need to track EVERY change.

My two cents to add to Jeff's description (which is how I do it),
I would usually use an employeeID in the various tables (for CreatedBY and
UpdatedBY) and have tblEmployee,
I don't use the active directory method (but it is an approach that might
fit your situation),
Mark Andrews
RPT Software
http://www.rptsoftware.com
http://www.donationmanagementsoftware.com

Jeff Boyce said:
The general concept is that you'd add fields to each table for which you
wish to keep this information.

The fields I generally add are:
[CreateDate]
[CreatedBy]
[LastUpdated]
[UpdatedBy]

Then I add code in each form that displays table data. That code checks
to see if the data is a new record or is an update to an existing record,
and updates the above fields accordingly.

Another way might be the audit trail approach that Allen Browne describes
(check on-line).

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Suddi said:
I am new to Access and have an Access 2003 database without user-level
security that I am planning to convert to Access 2007. I would like to
add
fields to my database to store create_userid and modify_userid. Can
someone
help me with that please?
Click to expand...
Click to expand...
Click to expand...
 
Suddi said:
I am new to Access and have an Access 2003 database without user-level
security that I am planning to convert to Access 2007. I would like to
add
fields to my database to store create_userid and modify_userid. Can
someone
help me with that please?
Click to expand...
 
,c, ;;::!§

Jeff Boyce said:
The general concept is that you'd add fields to each table for which you
wish to keep this information.

The fields I generally add are:
[CreateDate]
[CreatedBy]
[LastUpdated]
[UpdatedBy]

Then I add code in each form that displays table data. That code checks
to see if the data is a new record or is an update to an existing record,
and updates the above fields accordingly.

Another way might be the audit trail approach that Allen Browne describes
(check on-line).

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Suddi said:
I am new to Access and have an Access 2003 database without user-level
security that I am planning to convert to Access 2007. I would like to
add
fields to my database to store create_userid and modify_userid. Can
someone
help me with that please?
Click to expand...
Click to expand...
 
Back
Top