Capture a table name

[Tara]

I'm looking for a very simple way to track db changes/deletions. So, I
thought maybe I could create a new table to save the user who made the
change/deletion, the date of the change/deletion, the date the ORIGINAL data
was created, and which table the change/deletion took place in. I should be
able to handle most of that pretty easily, but I have no idea how to capture
a table name. Any ideas?

Thanks for any help!

 

[Jack Leach]

Assuming your data is being changed in a form (not by an action query), the
only way I could think to do this would be to read the Recordsource of the
form.

If you only use tables, this should be very easy. The Recordsource will be
the table name.

YourTableValue = Me.Recordsource

However, if you are driving the forms by a query, this would require some
further parsing of the recordsource (which will be either a saved querydef
name, much like the table example, or will be the actual SQL string, which
you would have to pull the desired table name from).


You may want to take a look at Allen Browne's article on Audit Logs, though
I wouldn't call this simple exactly... it is very thorough.

http://allenbrowne.com/AppAudit.html


--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

 

[Banana]

If you are using Access 2000 or later, you could peek into the form's
recordset and determine the source field/table.

Example:

Me.Recordset.Fields("NameofField").SourceTable
Me.Recordset.Fields("NameOfField").SourceField

This is also useful in case a form has a query that uses alias for
either columns or tables.

Note this is DAO code, and help file cautions against querying the
SourceTable on a table-type recordset (e.g. if you set the recordsource
to name of a table, the return may not what you expect; in a brief test,
it worked as expected but caveat emptor)

HTH.

 

[Tara]

Thanks to both of you for heading me in the right direction.