?
Solved

show change date of sql table in access report

Posted on 2012-03-17
14
Medium Priority
?
256 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 

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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Suggested Courses

770 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