Solved List Database Design Changes, since ..

[strive4peace]

thanks, @Pat Hartman

>really hoping that you had figured out the problem and when I found that you hadn't, I was so bummed I just forgot to thank you for what you did do

The dates are different. That IS the problem. All the Analyzer does is document what's there. Obviously it can't fix discrepancies in Access.

The Solution: As far as asking the Access team to spend time changing that, I wouldn't. Their focus is better spent on tasks with greater benefit.

> specifically look at some setting, object by object

yes, that is how you have to get the property values for an object. You can see some great stuff in the MSys tables, but not everything you can using DAO. The system tables are intrinsic to how Access works, and I really wouldn't want to see them start changing a lot because it would have a water-bed effect. And, in my opinion, drag time away from something else more important.

> There was no way to loop through the objects.

The Analyzer already loops through all the containers, it has to, to populate all the tables that it does. So just figure out what date you want and let the Analyzer do the looping. The Analyzer structure is patterned after DAO, so you should be able to find the right table to look in. Then modify the RecordSource for the report where you want to see it, or make a new report and change even more. Everything is open so you can see exactly what you want.

Or, if you rather, adapt your tool. By the way, you can run the Analyzer on itself, so you can document all of its tables, fields, etc, with descriptions.

 

[Pat Hartman]

They don't need to change anything about the MSysObjects table except to update it when an Object changes. Making a value correct shouldn't break anything.

Your code, which I presumed (but I haven't examined in depth yet) looked at the dates using VBA by examining the properties in the collections, comes up with the SAME date as the MSysObjects table and that date is incorrect. That is what the picture I posted showed. The dates in the Nav Pain(sic) are correct but the dates in MSysObjects are not correct. I am also showing in the picture, the dates the Analyzer pulled up and they match MSysObjects rather than the Nav Pane. So, the Analyzer also obtains incorrect dates. You might want to add a comment to the results form to the effect that the Update date is unreliable.

If I could get the correct dates by looping through the collections, it would be a PITA compared to using MSysObjects but certainly doable and I wouldn't be looking for MS to provide a solution. But the collections have the wrong dates also. Only the Nav Pane has the correct dates and there is no way to get there from what I have found so far. MS tells us we can rely on the collections. We just cannot rely on the MSys tables. But apparently, we can't rely on the collections either. This is not a new problem from what I can tell. It has been around for at least 20 years since that was when I first started trying to build my own documentation tool.

 

[strive4peace]

dates are stored in multiple places, it just depends where you're looking. If the Navigation Pane gets it, then it's somewhere.

> Making a value correct shouldn't break anything.

surely you're joking. It's been wrong for years, probably always. And the code that writes to it was created a long, long time ago. Things could be better, but in the big scheme of things, it is ok.

 

[Pat Hartman]

But Access doesn't care whether the date is 3/5/2022 or 7/21/22. We're talking about data here. Are you saying that your applications cares if the customer name is Acme or BigTime?

If the Navigation Pane gets it, then it's somewhere

Yes it is somewhere. But no one I've spoken to knows how to retrieve it. Ben took a stab at it. Not having the correct date makes my documenter incorrect and yours also.

 

[The_Doc_Man]

The whole thing boils down to a simple rule. If you want something done right, do it yourself. In my biggest project, I kept strict version management and had a "development journal" as part of the project. I would make an entry tied to the current three-tiered version number and date. If at any time I wanted to know what was in a given version, I could look back to see when something was added to the code. It was tied to the built-in "trouble reporting" scheme since this wasn't a commercial project, it was a departmental project - single developer (me). My clients (actually, co-workers) could look up anything they had entered as a trouble report and see what had been done about it. I didn't rely on anything in Access itself to track anything. I tracked it myself.

 

[strive4peace]

@Pat Hartman It has never been important enough to me to compare all the dates, but since you seem to care so much, which dates did Ben say to use? To me that is something fairly trivial compared to everything else.

 

[Pat Hartman]

@strive4peace I'm using the date to automatically decide if I want to make a backup of the FE when I close it. I keep a table of the last backup date and if I change an object, I'd like to be asked if I want to create a backup. The code is written to ask only me (or other developers if there are any) since the users don't change the FE and if they do, we don't want to back up anyway.

If this isn't something you are interested in, don't bother. I'll just keep looking. Thanks anyway.

 

[strive4peace]

@Pat Hartman what dates were suggested? you didn't answer that ...

 

[Pat Hartman]

The dates I want are the update date for each object. I can't find the email from Ben it was a couple of years ago. His suggestion was code that needed to look up the dates by passing in each specific object name. But the code didn't work reliably and I couldn't make it work so I dropped it. It had problems similar to the MSysObjects table and the collections. Sometimes Access makes the update when it should but not always. The issue revolves around DAO. If DAO is involved somehow, the update date is always correct. If not, it is sometimes correct. Only the date shown in the Nav Pain (sic) seems to be accurate.

 

[strive4peace]

aside from each collection, there are other collections you might want to check, the ones for CurrentProject like AllModules, AllForms, etc -- they also have dates.

 

[Pat Hartman]

Thanks