log record change in back-end?

[GBA]

is there a method, in the back-end file, to log that a value (any value) in a
record was changed? - - adding a new field to the table just for this purpose
is ok.

I can easily see a method if one can put vba in the front-end form that the
user uses. I can use vba to insert a change code into that record's log
field.

But I can not see any method that would exist only in the back-end file. Is
there any technique out there?

 

[Jeff Boyce]

Access doesn't offer table-level 'triggers'. Have you looked into using
SQL-Server or another more robust back-end?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jerry Whittle]

Not with Access.

If this is a hard requirement, you could upsize to something like SQL Server
or Oracle. They both support auditing that can tell you who was messing with
the data. They also support triggers at table level which could be used to
record when a record was changed and by whom.

 

[GBA]

thanks both...you confirmed my thinking.

I did find however a technique to meet my current need - - - I keep a Prior
copy of the back end tables...then periodically I compare the current Back
End with the Prior - and can identify all changed records. And rotate the
old Prior to archive, the current back end to Prior - and await the set time
period for the next compare with current.

Not the right solution in every situation - but works in my case..... thanks
again for your input....

 

[Allen Browne]

You might like to keep an eye open for the changes proposed for Access 2010
when it is released next year.

The new version will support data macros, which give you similar results to
triggers.

 

[Armen Stein]

Not with Access.

If this is a hard requirement, you could upsize to something like SQL Server
or Oracle. They both support auditing that can tell you who was messing with
the data. They also support triggers at table level which could be used to
record when a record was changed and by whom.

Yes. Keep in mind that SQL Server can only record *who* made the
change based on the SQL login. If you use Windows authentication,
you're fine - you can stop reading.

But if you use SQL Server authentication with a generic application
login (which is a simpler approach and preferred in some scenarios),
then you'll need to get the user another way. You can have each form
load the current Windows user into a ChangedByUser field in the table,
then have the trigger pick up the value of that field for the change
logging table, then clear the value in the main table so it doesn't
get picked up accidently the next time.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com