List of last used tables (1 Viewer)

Thales750

Formerly Jsanders
Local time
Today, 01:55
Joined
Dec 20, 2007
Messages
2,061
I've searched around and found no answers, sorry for such a basic question.
Is there a way to find a date or numeric reference for tables indicating their last data entry.

This needs to be on a global bases, so I imagine the data will come from one of the Sys Tables. I am referring to data entry, not table modified.

Thanks in advance
 

MarkK

bit cruncher
Local time
Yesterday, 22:55
Joined
Mar 17, 2004
Messages
8,178
What I routinely do is add a date field to each significant table and call it 'Created', and set its default value to =Now(). Then, that table's last entry date is very simply returned using...
Code:
? DMax("Created", "tSomeTable")
hth
Mark
 

June7

AWF VIP
Local time
Yesterday, 21:55
Joined
Mar 9, 2014
Messages
5,424
As Mark's answer implies, there is no table property for this. Access does not track when table data was last edited. You must accomplish this with code and data. If you want to build an audit trail functionality, review http://allenbrowne.com/AppAudit.html
 
Last edited:

MajP

You've got your good things, and you've got mine.
Local time
Today, 01:55
Joined
May 21, 2018
Messages
8,463
This is a good use for a data macro. Open the table/s in datasheet view. And click on the tab "table". Then select "after insert" and if you want other events. Select "logEvent" give it a descriptive name of what is happening "New Record in Table ABC" or "Deletion from Table ABC" or "Change to Record in table ABC". This will now create a new table log
USysApplication log.
Code:
SourceObject	Data Macro Instance ID	Error Number	Category	Object Type	Description	Context	Created
tblDateTime.AfterUpdate	{5FC276F3-2277-424F-BE40-B3AC8102C18A}	1	User	Macro	Update TblDateTime		8/26/2018 2:47:45 PM
tblCodes.AfterInsert	{7C537ED6-AD0D-4BA8-8279-A41B859D6F11}	1	User	Macro	New Record in TblCodes		8/26/2018 2:52:21 PM
tblCodes.AfterUpdate	{9C45F00F-5EFB-44FF-97BB-FA9CA5F84334}	1	User	Macro	Update To tblCodes		8/26/2018 2:52:29 PM
 

Thales750

Formerly Jsanders
Local time
Today, 01:55
Joined
Dec 20, 2007
Messages
2,061
Thanks guys, I don't actually want to open any tables. There are over three hundred tables.

We have developed a new section of the database with 75 new tables but we started with a copy of the original 225 tables. They have both been used in both systems.

Was looking for a way to generate a last entry view of all the tables. As it turns out, the last modified property will get us close.
 

Thales750

Formerly Jsanders
Local time
Today, 01:55
Joined
Dec 20, 2007
Messages
2,061
And you are still using Access? Might be time to upgrade.

In this project we developed in Access, and then will migrate to SQL Server towards the end of the project. We still use Access Front Ends as well as other technologies.


There are a lot of home grown Access databases with thousands of hours of development over the last 20 years or so. And it is often the case, they offer the least cost to solving mission critical requirements. Which is what we did here. They had several department running on various databases. We built one systems that runs the entire plant. As it turns out, very little of what they had written was able to be ported to the new system. At least we were able to do it in phases and they had a minimum of down time.
 

Thales750

Formerly Jsanders
Local time
Today, 01:55
Joined
Dec 20, 2007
Messages
2,061
As Mark's answer implies, there is no table property for this. Access does not track when table data was last edited. You must accomplish this with code and data. If you want to build an audit trail functionality, review http://allenbrowne.com/AppAudit.html

We have one similar to this. But it does not track changes to admin tables. There are over a hundred of these.

Thanks for the tip. it looks like I will go through every one of them and straighten it out.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 01:55
Joined
Feb 19, 2002
Messages
42,981
Wow. Do not know if I should laugh or cry. sounds like you should gave this up a long time ago.
You are clearly not an Access fan and are somewhat naive in the workings of corporate America. In most companies there is a limited development budget. This is especially a problem in companies that consider IT to be a cost center rather than a profit center. When IT has no budget for your project, should your department not do it or wait 2 years and loose a market opportunity or should they do it in Access using their own resources and get it done probably faster than IT could?

Savvy companies actually encourage departments to do development of new projects in Access. If the need is transitory, it saves the precious IT budget. If the need turns out to be permanent, the concept is much better fleshed out and IT has a working solution to use for guidance. Developing an app in Access will likely cost less than 25% of the cost of some web technology and also take a fraction of the time so Access is much more agile. It took one of my clients almost three years and several millions of dollars to replicate an Access application I built in two months. It took them that long even though they had a working prototype so they didn't even have to design from scratch AND my Access app used SQL Server as the BE because it shared tables used by one of their web apps.

Don't confuse Access with Jet/ACE the database engines sometimes confused with Access. Access is a Rapid Application Development (RAD) tool. It is used to create forms/reports/code. The database engine manages data. With some understanding of client server technology and a little thoughtfulness, you can develop applications using Jet/ACE BE's that can be converted to SQL Server within hours. All of mine can. Some, which are sold to the public, can even hot swap from ACE to SQL Server so the client gets to decide which BE he wants when he installs the project. The trick is to always use the client/server method and never use the "Access" method when you are designing.
 
Last edited:

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 05:55
Joined
Jul 9, 2003
Messages
16,245
Savvy companies actually encourage departments to do development of new projects in Access.

To flesh out on Pats comment, and there's not really any need to, she's got it in a nutshell. But I think there's an important issue with people's perception of Microsoft Access which Luke Chung tackled very well. I was fortunate to see him do a presentation at the Microsoft campus, Reading, Berkshire, UK, I believe in 2010. I reckon this is an excellent summary of what he said:-

Microsoft Access within an Organization's Overall Database Strategy

To Quote Luke:- "Databases evolve over time. Access cannot and was never designed to solve every database problem. What it does offer is a great, cost-effective, and quick solution for a wide range of common database challenges in Windows."

And one of the main takeaways I got from Luke's presentation was that IT departments hate MS Access most probably because someone comes up with a poorly designed Access database for their office or department which works and does the job. Upper management like it but want it upgraded at some stage. Management expects the IT department to sort it out. This causes the IT department a headache. Instead of calling in experts to help sort it out, they just say no we don't want MS Access, it's unsafe, this is wrong, that's wrong. (It's just as safe as your Excel and Word documents... To qualify this is me not Luke)...

This is a copy of Luke Chungs PowerPoint presentation (Saved as a Google Presentation) from the Microsoft Reading Campus presentation:- Where Access Fits in the Overall Database Strategy of an Organization


MS Access is absolutely brilliant at what it does. Seeing as many people move from Excel to MS Access, it's important to understand the differences between MS Access and Excel, differences which if not addressed early on in your development WILL cause you a big headache at some stage. I blogged about it here:- http://www.niftyaccess.com/excel-in-access/
 

Thales750

Formerly Jsanders
Local time
Today, 01:55
Joined
Dec 20, 2007
Messages
2,061
What would be nice, if there was a record count available in the Sys tables.
 

Thales750

Formerly Jsanders
Local time
Today, 01:55
Joined
Dec 20, 2007
Messages
2,061
Thanks everyone for replying.
So far I have gone through 150 or so table by opening them. Not as bad as I had originally thought it would be. Plus it is giving me a chance to glance at the data to see if they are conforming to the rules. Not to mention that I now know what has never been used in the system.
 

Thales750

Formerly Jsanders
Local time
Today, 01:55
Joined
Dec 20, 2007
Messages
2,061
I came up with a fairly simple solution.

I made a table in a new database from an outer join from the sysObject table in both the back ends. It included the modified dates from both.
then I put a check box for each backend.
Check one, or the other, to select the table source for the new backend.
Check both for tables that need further exams.
Later, use the query, in a loop, to transfer tables from either existing one, to the new one.

There may be a simpler way, but this works.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 01:55
Joined
Feb 19, 2002
Messages
42,981
You can't actually rely on the modified dates from the MSysObjects table. You might get more accurate results if you write VBA to get the dates from the object model. I lost interest in the tool I was trying to develop when I discovered that the dates in MSysObjects were completely unreliable so I never wrote code to determine if the object model had more reliable dates.
 

Thales750

Formerly Jsanders
Local time
Today, 01:55
Joined
Dec 20, 2007
Messages
2,061
You can't actually rely on the modified dates from the MSysObjects table. You might get more accurate results if you write VBA to get the dates from the object model. I lost interest in the tool I was trying to develop when I discovered that the dates in MSysObjects were completely unreliable so I never wrote code to determine if the object model had more reliable dates.

Yup, I have found the same thing. I think the Modified Date from the MSysObjects table may reset when you do a Repair and Compact.
 

isladogs

MVP / VIP
Local time
Today, 05:55
Joined
Jan 14, 2017
Messages
18,186
Yup, I have found the same thing. I think the Modified Date from the MSysObjects table may reset when you do a Repair and Compact.

Just tested your theory. Its wrong.
The DateUpdate field wasn't affected by running a C&R.

However doing ANYTHING to a table will reset it,
It doesn't have to be a data change to do so.
For example sorting the records, changing the background colour etc will change the DateUpdate to Now()
Basically anything that triggers the message 'Do you want to change the design of table XXXX'
 

Thales750

Formerly Jsanders
Local time
Today, 01:55
Joined
Dec 20, 2007
Messages
2,061
Just tested your theory. Its wrong.
The DateUpdate field wasn't affected by running a C&R.

However doing ANYTHING to a table will reset it,
It doesn't have to be a data change to do so.
For example sorting the records, changing the background colour etc will change the DateUpdate to Now()
Basically anything that triggers the message 'Do you want to change the design of table XXXX'

Of course, you are absolutely correct. I think I had imported a bunch of tables and had forgotten that part. LOL. my bad.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 00:55
Joined
Feb 28, 2001
Messages
27,001
Theoretically, the only thing done by a C&R is that it copies things in a new, blank database in a way that eliminates slack space. It does this by copying data from each table and each other element of the source DB one item at a time using a linear allocation of free space from the destination file. That means that after a C&R, ALL of table 1 is contiguous within the DB (which is at that point a container file). ALL of table 2 is contiguous. All of table 3 etc. etc. Then the queries. Then forms. Etc. etc.

NOW - as to WHY dates don't change for a C&R even for MSysObjects? Because it is self-referential. That is, MSysObjects table is one of the objects described in MSysObjects, and all of its attributes get copied because ... they are IN MSysObjects on the source side. The point of the C&R is that NOTHING changes including contents, so since the dates are part of something being copied, they won't change either.

Once the copy is complete, the file size might get adjusted slightly but THAT isn't something that is in the destination DB. That's something in the file header in the master file (i.e. File System) structure.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 01:55
Joined
May 21, 2018
Messages
8,463
You are clearly not an Access fan and are somewhat naive in the workings of corporate America. In most companies there is a limited development budget. This is especially a problem in companies that consider IT to be a cost center rather than a profit center. When IT has no budget for your project, should your department not do it or wait 2 years and loose a market opportunity or should they do it in Access using their own resources and get it done probably faster than IT could?
Pat, Clearly I am a fan of Access. I have 10's of thousands of post on the Internet on the subject and know more about Access than 99.9% of so called Access developers. So do not tell me who is a fan of Access. But it is what it is. The development environment looks the same as 25 years ago except of stupid things like multi value fields and split forms. So there are times when it is time to move on.
 

Users who are viewing this thread

Top Bottom