show change date of sql table in access report

I do have a report that takes data from a specific sql table and I would like to give the user an information in a field at the bottom of the Access report where he sees when the latest data have been added/changed in that table. Does anybod knows the easiest way to implement?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

How are you tracking these dates in your SQL table?

Generally, you would have two fields - DateCreated and DateModified

In your SQL table, the default value properties of both fields should be GetDate()

Access forms that modify records in this table should include these fields in their recordsource.

To record the Modified Date, you could add code to the BeforeUpdate event of your form:

Me.DateModified = Now()

With that setup, assuming your report is bound to this SQL table, you would simply have to add a textbox, bound to your DateModified field.
KongtaAuthor Commented:
First, I have linked the SQL tables into Access. If I understand you right, I have to create two fields (dateCreated & DateModified) and fill them with getDate()? Isn't there a more easy way? I assume the ModifiedDate has to be stored somewhere in the table already, not?
It's a little confusing: are looking for the last modify date on a row-by-row basis or the last modify date for the table as a whole.

If row-by-row, then you need to maintain a date_modified column as the dbms does not do that automatically.  It is often prudent to maintain a companion field: last_modified_by.  Since this is an sql server table, I suggest you update those field using a trigger rather than from your Access form.  That makes it much harder, unintentionally or otherwise, to circumvent the change history.  Apply the same technique to date_added if you want that info, as well.

If you want date modified for the table as a whole, you can use the above technique, and then select max(date_modified) for the report.  Or, you can just pull the information from the MSSQL internal tables:

select last_user_update from sys.dm_db_index_usage_stats
where database_id = db_ID('YourDatabase') and object_Id=object_id('YourTable')
5 Ways Acronis Skyrockets Your Data Protection

Risks to data security are risks to business continuity. Businesses need to know what these risks look like – and where they can turn for help.
Check our newest E-Book and learn how you can differentiate your data protection business with advanced cloud solutions Acronis delivers

KongtaAuthor Commented:
I would like to know the table as a whole and was looking for something like in your last line, but there I got


even the table is fully filled!?
If you add the two columns after the table has data in it, all of the existing rows will have NULL for those columns.  You would then need to do something like updating all of the rows so that those two columns have the current date.  E.g.

UPDATE yourtable
SET DateCreated = GETDATE()
      ,DateModified = GETDATE()
     ,LastModifiedBy = 'UNKOWN';

Also, even though you set the DateModified so that it has GETDATE() as the default, that doesn't men that DateModified will change just because you update some other column.  Every time you update a row in the table, you also have to update the DateModified and LastModifiedBy columns.
Two issues to resolve:

Why you are getting two rows back.
Why you are getting nulls back.

What does this return:
select last_system_update from sys.dm_db_index_usage_stats
where database_id = db_ID('YourDatabase') and object_Id=object_id('YourTable')
KongtaAuthor Commented:
@Sorry 8080_Driver, I don't want to add extra fields into my table as this contains thousands of rst and it makes no sense to me only to find one 'DateModified' of the table, not the row!, inflate the whole table.

@dqmq: two rows was what looked strange to me as well. Executing your code does return nothing, no line at all. I executed it in a new Query in the ManagementStudio and choosen my db already, tried in master, modell.... but no return?!
The usage_stats view returns data from all database, therefore, it doesn't matter which one you run it from.  The "where databaseid=db_ID("YourDatabase""  condition narrows the results down to the database of interest.  the object_id condition narrows the results down to the table of interest.  Be sure to qualified database name with the schema,  like [dbo].[YourTableName], otherwise  you can get multiple rows back if the tablename exists in two different schemas.

Tweak this until you get a single row bacK:

select * from sys.dm_db_index_usage_stats
where database_id = db_ID('YourDatabase') and object_Id=object_id('YourTable')

Then look at the columns returned do see if the date you are looking for can be found.
KongtaAuthor Commented:
@dqmq, your input is defenitely the way I should go but it does not work. Even when I use

select * from sys.dm_db_index_usage_stats
where database_id = db_ID('master') and object_Id=object_id('dbo.MSreplication_options')

I do not get any results back. Makes no sense at all, right? Do I have to run a special service to collect all this data? I assume 'database_id', 'last_user_scan' and all the other staff should be standard collected without running a special service. No idea why these data are not recorded on my SQL-Server. Has this something to do with the version? I run 2008. BTW, even when I run the first code where I did get to NULL-rows, I dont' get any info back.
Oh my...I didn't realize you were reporting against SYSTEM tables. Maybe it's a permission issue--I don't know.   I rather think all bets are off if you expect system tables to be exposed the same way as user tables.
KongtaAuthor Commented:
Sorry, I only tried on system tables after on my user tbl it didn't get any results back. Thought have a try in model-db and might be sucessful there. On my db and tbls I dont make any progress.
KongtaAuthor Commented:
If I use

select * from sys.dm_db_index_usage_stats
where database_id = db_ID('master')

then I get some data back like

object_id      database_id      index_id      user_seeks      user_scans      user_lookups      user_updates      last_user_seek
1447676205      5      1      0      1      0      0      NULL
1157579162      5      1      0      4      0      2      NULL
1890105774      5      1      10      0      0      0      20.03.2012 17:50
That's normal.  What is the name of the table you want to monitor?  Please fully qualify it, like:  databasename.schemaname.tablename.

Then, run

select * from sys.dm_db_index_usage_stats
where database_id = db_ID('databasename') and object_Id=object_id('schemaname. tablename')

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
KongtaAuthor Commented:
Thx, I guess the main issue that I don't get results back is because I don't run the Server 24/7 right now and if I start the server in the evening, every stat is set back to Null. I guess I have to think for another way to get the data, but adding a field to a table is defenitely not very efficient. Anyhow, I will close the Q and might come back when I need help again in this issue. thx anyway, rgds Kongta
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.