Solved

show change date of sql table in access report

Posted on 2012-03-17
14
247 Views
Last Modified: 2012-03-21
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?
thx
Kongta
0
Comment
Question by:Kongta
14 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 37732744
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.
0
 

Author Comment

by:Kongta
ID: 37732781
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?
0
 
LVL 42

Expert Comment

by:dqmq
ID: 37732851
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')
0
 

Author Comment

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

last_user_update
NULL
NULL

even the table is fully filled!?
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 37733462
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.
0
 
LVL 42

Expert Comment

by:dqmq
ID: 37733657
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')
0
 

Author Comment

by:Kongta
ID: 37734243
@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?!
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 42

Expert Comment

by:dqmq
ID: 37735151
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.
0
 

Author Comment

by:Kongta
ID: 37735213
@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.
0
 
LVL 42

Expert Comment

by:dqmq
ID: 37735269
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.
0
 

Author Comment

by:Kongta
ID: 37735449
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.
0
 

Author Comment

by:Kongta
ID: 37743908
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
0
 
LVL 42

Accepted Solution

by:
dqmq earned 500 total points
ID: 37748588
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')
0
 

Author Comment

by:Kongta
ID: 37749407
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
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now