Solved

show change date of sql table in access report

Posted on 2012-03-17
14
249 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
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…

816 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

11 Experts available now in Live!

Get 1:1 Help Now