Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-27-2018, 01:36 PM   #16
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

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.

__________________
Aboard Oceans Eleven on Offatts Bayou
inveniemus viam aut faciemus
Thales750 is offline   Reply With Quote
Old 08-28-2018, 08:48 PM   #17
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

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.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 08-29-2018, 01:11 PM   #18
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 Pat Hartman View Post
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.

__________________
Aboard Oceans Eleven on Offatts Bayou
inveniemus viam aut faciemus
Thales750 is offline   Reply With Quote
Old 08-29-2018, 01:59 PM   #19
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 7,345
Thanks: 92
Thanked 1,809 Times in 1,684 Posts
isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light
Re: List of last used tables

Quote:
Originally Posted by Thales750 View Post
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'
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Previously known as ridders : Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 08-30-2018, 06:56 AM   #20
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 ridders View Post
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.
__________________
Aboard Oceans Eleven on Offatts Bayou
inveniemus viam aut faciemus
Thales750 is offline   Reply With Quote
Old 08-30-2018, 08:11 AM   #21
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 12,565
Thanks: 62
Thanked 1,194 Times in 1,094 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: List of last used tables

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.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 08-31-2018, 06:13 AM   #22
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
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?

__________________
Aboard Oceans Eleven on Offatts Bayou
inveniemus viam aut faciemus
Thales750 is offline   Reply With Quote
The Following User Says Thank You to Thales750 For This Useful Post:
Uncle Gizmo (09-23-2018)
Old 08-31-2018, 09:45 AM   #23
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 12,565
Thanks: 62
Thanked 1,194 Times in 1,094 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: List of last used tables

If I remember my statistics correctly, 99.9% of all extreme claims are slightly exaggerated.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
The Following User Says Thank You to The_Doc_Man For This Useful Post:
Thales750 (08-31-2018)
Old 09-06-2018, 08:45 AM   #24
BeeJayEff
Newly Registered User
 
Join Date: Sep 2013
Location: Somerset, UK
Posts: 198
Thanks: 37
Thanked 10 Times in 10 Posts
BeeJayEff is on a distinguished road
Re: List of last used tables

Quote:
Originally Posted by Thales750 View Post
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 ?
BeeJayEff is offline   Reply With Quote
Old 09-06-2018, 08:54 AM   #25
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 7,345
Thanks: 92
Thanked 1,809 Times in 1,684 Posts
isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light
Re: List of last used tables

Quote:
Originally Posted by BeeJayEff View Post
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 ?
It's certainly not uncommon

My largest database has around 330 tables mostly in a SQL BE.
See screenshot in this post https://www.access-programmers.co.uk...97&postcount=2

It is indeed fully normalised though I'm not sure why you think that's relevant.
In fact, fully normalising is likely to reduce the number of fields per table but increase the number of tables
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Previously known as ridders : Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 09-06-2018, 09:36 AM   #26
BeeJayEff
Newly Registered User
 
Join Date: Sep 2013
Location: Somerset, UK
Posts: 198
Thanks: 37
Thanked 10 Times in 10 Posts
BeeJayEff is on a distinguished road
Re: List of last used tables

Quote:
Originally Posted by ridders View Post
It's certainly not uncommon

My largest database has around 330 tables mostly in a SQL BE.
See screenshot in this post https://www.access-programmers.co.uk...97&postcount=2

It is indeed fully normalised though I'm not sure why you think that's relevant.
In fact, fully normalising is likely to reduce the number of fields per table but increase the number of tables
Fair enough - the only time I've seen a database with such a huge number of tables was because it was far from normalised (e.g. one table per month for several years).
BeeJayEff is offline   Reply With Quote
Old 09-06-2018, 09:42 AM   #27
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 7,345
Thanks: 92
Thanked 1,809 Times in 1,684 Posts
isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light
Re: List of last used tables

Ah I see what you were driving at now....

The database I quoted has been in continual development for over 15 years, is used in a number of schools and does cover a huge amount of staff, student and whole school data.

At one time it contained even more tables until I removed some old features that we're no longer in use!
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Previously known as ridders : Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 09-06-2018, 10:42 AM   #28
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

That number of tables in a single database is extremely rare. It happens primarily in two cases:
1. an MRP type system that covers multiple business areas such as the one ridders sells.
2. a poorly designed application that is using tables as spreadsheets.

The largest number of tables I've had at one time was about 120. This application basically ran most of the company. Each time I absorbed a new set of functions, some number of new tables had to be added. I don't think it could get to 330 given what it does now and what it might do in the future but it most like will continue to be expanded.

The second largest was a mainframe CICS/DB2 app that I built a lifetime ago and that one had 95 tables.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 09-06-2018, 11:02 AM   #29
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 12,565
Thanks: 62
Thanked 1,194 Times in 1,094 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: List of last used tables

The database that was used to make personnel management decisions for the U.S. Navy Reserve had only about 240 tables - which sounds like a lot - but fully HALF of those were either "translation" tables that converted codes to a longer name or constant-lookup tables to supply numeric rates based on member attributes. Translations like the rank-rate table that converted E5 to Petty Officer First Class or O4 to Lieutenant Commander. Constants like the yearly uniform allowance based on the reservist's location and rating.

Another third of the tables (about 80 or so) related to external interfaces with about 20 other Navy organizations. They weren't needed for the internal processing but we needed places to hold things when we were preparing files for electronic transmission to an agency that used different codes than we did.

So if you do the math, about 20 tables were the active parts - with three being primary: Person, Billet, and Unit.

That is the MOST complex database I've ever seen or worked around and decidely YES it was properly normalized. It wasn't Access, though. It was an ORACLE monstrosity.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 09-06-2018, 11:58 AM   #30
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 7,345
Thanks: 92
Thanked 1,809 Times in 1,684 Posts
isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light
Re: List of last used tables

It is a very wide ranging application. If anyone is interested, a summary of it's main features can be found here: http://www.mendipdatasystems.co.uk/s...ser/4584605482

I mentioned in a recent thread that I'd made a decision with that monster database over 10 years ago that I'd regretted ever since.

At the end of each academic year all attendance, reporting and other grade tables were archived into separate tables. In addition when students left school all their data was separated into leavers tables plus of course leavers archived tables.
This made finding the data much faster but significantly increased the number of tables together with additional queries, forms, reports and all the code that accompanied them.
Furthermore the end of year routine had to be checked each year and modified where field changes had occurred.

All in all many hundreds if not thousands of hours of work have been added by those decisions and without rewriting the entire database app, it's ongoing work. Luckily, development work has significantly slowed down as it now has all the features needed by all my client schools.

If the decision had been to keep all leavers and archived data in the original tables, the total number of tables would drop by 80 to about 250 with similar drops in numbers for all other database objects. The FE (approx. 130MB) would be smaller and probably faster.

So whilst it still performs very well, it is a behemoth that taught me a lesson I won't forget.

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Previously known as ridders : Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs 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 04:56 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