log record change in back-end?

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?

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


"GBA" wrote in message
...
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?

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.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"GBA" wrote:

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?

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....

"Jerry Whittle" wrote:

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.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"GBA" wrote:

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?

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.

--
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.


"GBA" wrote in message
...
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....

"Jerry Whittle" wrote:

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.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"GBA" wrote:

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?

On Tue, 29 Sep 2009 12:26:02 -0700, Jerry Whittle
wrote:

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