Check last updated on a SQL Table which is using Access as Front end

[eggy168]

Hi,

I current have a request from users that they want to see when and who was the last updated on a record(s) on a table (I used Access form to display data for users). The database is in SQL Server 2005 and I am using Access the front end.

I have no clue what should I do to show who/when changed the data in Access form. I can run a trigger in SQL and I see who and when changed the data, but I can't show it in Access form because we are talking about two application here.

Can someone help me to figure out what should I do or give me a guideline where should I start?

Thank You.

 

[boblarson]

You can add two fields to the tables - One for LastUpdate (date/time) and one for LastUpdatedBy (text).

Then in your form's BEFORE UPDATE event, after validating things are right, as long as those fields are in your form's record source (and the record source is updatable), you can use

Me!LastUpdate = Now
Me!LastUpdatedBy = Environ("username")

But if you would rather use this function (as it is more secure) you can use:

http://www.mvps.org/access/api/api0008.htm

And then you would call it

Me!LastUpdatedBy = fOSUsername


Bob Larson
FORMER Microsoft Access MVP (2008-2009, 2009-2010)
Free Tutorials/Samples/Tools:

http://www.btabdevelopment.com

 

[eggy168]

Thanks Bob.
It solved my problem after using the 1st solution. I am going to try the 2nd solution this afternoon.
I am wondering how it goes in asp.net 4.0 because I am trying to convert some of the Access forms to web base (again, base on the users requests). May be I am in the wrong forum to ask this question. However, if anyone knows the answer, please let me know.
Anyway, thanks Bob.