List of last used tables

[Thales750]

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]

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

? DMax("Created", "tSomeTable")

hth
Mark

 

[June7]

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

 

[MajP]

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.

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]

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]

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]

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.

 

[Thales750]

What would be nice, if there was a record count available in the Sys tables.

 

[CJ_London]

What would be nice, if there was a record count available in the Sys tables.

there is in the tabledef

 

[Thales750]

there is in the tabledef

Is that information available for unopened tables in one of the sys tables?

 

[Thales750]

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]

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.

 

[Thales750]

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]

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]

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]

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

 

[Thales750]

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.

How did you derive the 99.9%?

Building database is so much less about technologies, and so much more about business models, and psychology. After that, data structure is more important than controls and their functions.

But, back to the 99.9% thing. Is that 99.9% of, code writers, table makers, UI designers, requirements gatherers, testers, trainers, or documenters?

Or, you in the top .1% of all that?

 

[The_Doc_Man]

If I remember my statistics correctly, 99.9% of all extreme claims are slightly exaggerated.

 

[BeeJayEff]

There are over three hundred tables.

I'm intrigued by this and find it hard to countenance the design of such a system. The biggest db I look after only has about 30 tables of which about 10 are fairly static lookup tables - but the relationships diagram is quite complex enough, and it is properly normalised.

Is a properly normalised Access database with hundreds of tables a frequent occurrence ?