Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-26-2018, 09:52 AM   #1
Thales750
Formerly Jsanders
 
Join Date: Dec 2007
Location: Galveston, Texas
Posts: 1,614
Thanks: 42
Thanked 28 Times in 28 Posts
Thales750 is on a distinguished road
List of last used tables

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

__________________
Aboard Oceans Eleven on Offatts Bayou
inveniemus viam aut faciemus
Thales750 is offline   Reply With Quote
Old 08-26-2018, 10:27 AM   #2
MarkK
Super Moderator
 
MarkK's Avatar
 
Join Date: Mar 2004
Location: Vancouver BC
Posts: 7,761
Thanks: 10
Thanked 1,287 Times in 1,225 Posts
MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all
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
__________________
formerly known as lagbolt | Windows 10 | Access 2010 | Visual Studio 2013 | "Institutions have a vested interest in perpetuating the problems to which they are the solution." - Clay Shirky
MarkK is offline   Reply With Quote
Old 08-26-2018, 10:57 AM   #3
June7
Newly Registered User
 
Join Date: Mar 2014
Posts: 940
Thanks: 0
Thanked 214 Times in 214 Posts
June7 will become famous soon enough
Re: List of last used tables

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

__________________
To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

Last edited by June7; 08-26-2018 at 01:16 PM.
June7 is offline   Reply With Quote
Old 08-26-2018, 10:58 AM   #4
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 690
Thanks: 14
Thanked 161 Times in 159 Posts
MajP will become famous soon enough
Re: List of last used tables

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
MajP is offline   Reply With Quote
Old 08-26-2018, 01:16 PM   #5
Thales750
Formerly Jsanders
 
Join Date: Dec 2007
Location: Galveston, Texas
Posts: 1,614
Thanks: 42
Thanked 28 Times in 28 Posts
Thales750 is on a distinguished road
Re: List of last used tables

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.
__________________
Aboard Oceans Eleven on Offatts Bayou
inveniemus viam aut faciemus
Thales750 is offline   Reply With Quote
Old 08-26-2018, 01:43 PM   #6
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 690
Thanks: 14
Thanked 161 Times in 159 Posts
MajP will become famous soon enough
Re: List of last used tables

Quote:
There are over three hundred tables.
And you are still using Access? Might be time to upgrade.
MajP is offline   Reply With Quote
Old 08-26-2018, 02:16 PM   #7
Thales750
Formerly Jsanders
 
Join Date: Dec 2007
Location: Galveston, Texas
Posts: 1,614
Thanks: 42
Thanked 28 Times in 28 Posts
Thales750 is on a distinguished road
Re: List of last used tables

Quote:
Originally Posted by MajP View Post
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.

__________________
Aboard Oceans Eleven on Offatts Bayou
inveniemus viam aut faciemus
Thales750 is offline   Reply With Quote
Old 08-26-2018, 02:19 PM   #8
Thales750
Formerly Jsanders
 
Join Date: Dec 2007
Location: Galveston, Texas
Posts: 1,614
Thanks: 42
Thanked 28 Times in 28 Posts
Thales750 is on a distinguished road
Re: List of last used tables

Quote:
Originally Posted by June7 View Post
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.
__________________
Aboard Oceans Eleven on Offatts Bayou
inveniemus viam aut faciemus
Thales750 is offline   Reply With Quote
Old 08-26-2018, 03:35 PM   #9
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 690
Thanks: 14
Thanked 161 Times in 159 Posts
MajP will become famous soon enough
Re: List of last used tables

Quote:
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.
Wow. Do not know if I should laugh or cry. sounds like you should gave this up a long time ago.
MajP is offline   Reply With Quote
Old 08-26-2018, 07:29 PM   #10
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,448
Thanks: 13
Thanked 1,422 Times in 1,355 Posts
Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light
Re: List of last used tables

Quote:
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.
__________________
Bridge Players Still Know All the Tricks

Last edited by Pat Hartman; 08-26-2018 at 07:39 PM.
Pat Hartman is offline   Reply With Quote
The Following 2 Users Say Thank You to Pat Hartman For This Useful Post:
Thales750 (08-27-2018), Uncle Gizmo (08-27-2018)
Old 08-27-2018, 04:44 AM   #11
Uncle Gizmo
Nifty Access Guy
 
Uncle Gizmo's Avatar
 
Join Date: Jul 2003
Location: Newbury Berks UK
Posts: 9,553
Thanks: 368
Thanked 784 Times in 749 Posts
Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough
Send a message via Skype™ to Uncle Gizmo
Re: List of last used tables

Quote:
Originally Posted by Pat Hartman View Post
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/
__________________
Code:
                 |||||
               @(~^~)@
-------------oOo---U---oOo-------------
|                                     |
|      Uncle Gizmo              |
|                                     |
|                                     |
| Get $20 worth of "Nifty Code"       |
|      
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
| | Ooo | |_________________ooO____( )________| ( ) ) / \ ( (_/ \_)
Uncle Gizmo is offline   Reply With Quote
The Following User Says Thank You to Uncle Gizmo For This Useful Post:
Thales750 (08-27-2018)
Old 08-27-2018, 10:26 AM   #12
Thales750
Formerly Jsanders
 
Join Date: Dec 2007
Location: Galveston, Texas
Posts: 1,614
Thanks: 42
Thanked 28 Times in 28 Posts
Thales750 is on a distinguished road
Re: List of last used tables

What would be nice, if there was a record count available in the Sys tables.
__________________
Aboard Oceans Eleven on Offatts Bayou
inveniemus viam aut faciemus
Thales750 is offline   Reply With Quote
Old 08-27-2018, 11:00 AM   #13
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 10,464
Thanks: 40
Thanked 3,381 Times in 3,276 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
Re: List of last used tables

Quote:
What would be nice, if there was a record count available in the Sys tables.
there is in the tabledef
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 08-27-2018, 01:24 PM   #14
Thales750
Formerly Jsanders
 
Join Date: Dec 2007
Location: Galveston, Texas
Posts: 1,614
Thanks: 42
Thanked 28 Times in 28 Posts
Thales750 is on a distinguished road
Re: List of last used tables

Quote:
Originally Posted by CJ_London View Post
there is in the tabledef
Is that information available for unopened tables in one of the sys tables?
__________________
Aboard Oceans Eleven on Offatts Bayou
inveniemus viam aut faciemus
Thales750 is offline   Reply With Quote
Old 08-27-2018, 01:27 PM   #15
Thales750
Formerly Jsanders
 
Join Date: Dec 2007
Location: Galveston, Texas
Posts: 1,614
Thanks: 42
Thanked 28 Times in 28 Posts
Thales750 is on a distinguished road
Re: List of last used tables

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.

__________________
Aboard Oceans Eleven on Offatts Bayou
inveniemus viam aut faciemus
Thales750 is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
List of Tables Thales750 Modules & VBA 2 04-01-2009 06:58 AM
List tables in a combo or list box mjdavie Forms 4 12-10-2008 09:09 PM
I need to list the tables. jal Modules & VBA 3 09-25-2008 05:52 PM
List of Tables cavscout Tables 2 05-09-2003 12:18 PM
A LIST FROM TWO TABLES danaleblanc Queries 4 03-09-2002 12:32 PM




All times are GMT -8. The time now is 01:08 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World