Solved List Database Design Changes, since ..

[GUIDO22]

Is there a way to determine across the entire database what entities (tables/reports/ forms etc.) have changed since a specific date..?
I am not talking about records at table level... I mean VBA code and database design/structure changes
I am not using source code control or anything sophisticated and would like a quick means of seeing exactly what I have changed since a 'date' that my changes began...

Thank you in advance.

 

[CJ_London]

you can get the datecreated and lastupdated properties by looping through the currentdb.tabledefs and querydefs. For forms, reports and modules loop through the currentproject.allforms etc for datecreated and datemodified properties

 

[GUIDO22]

you can get the datecreated and lastupdated properties by looping through the currentdb.tabledefs and querydefs. For forms, reports and modules loop through the currentproject.allforms etc for datecreated and datemodified properties

Thanks CJ.
The TableDefs collection only appears to have : DateCreated and LastUpdated properties..
.. but when I loop through the timestamp differences are very small , only a few seconds...
However, if I inspect a table I know has changed from the main Design panel in the main window (right click the table properties in the database objects panel), I see a 'Modified' timestamp... this shows a timestamp more repsentative of what I would expect to see..

...if not in the TableDefs, where is this Modified information stored...?

Thx

 

[arnelgp]

you can Query MsysObjects table for the DateUpdate field:

select DateUpdate from MSysObjects Where Type = 1 And Name = "yourTableName";

for different Type see this:
MS Access - Listing of Database Objects (Tables, Queries, Forms, Reports, ...) | DEVelopers HUT (devhut.net)

 

[CJ_London]

...if not in the TableDefs, where is this Modified information stored...?

my understanding is that is the correct value - however looks like Arnel has a better and simpler suggestion since this can be done in sql

as an alternative

select Name, Type from MSysObjects Where DateUpdate >=[Enter a Date (yyyy-mm-dd]

 

[GUIDO22]

Thanks both of you - I now have a routine I can run on any of the main object types - because of the similarity in the two date fields - (presumably on account of when I recently copied the database over to my devt. laptop), I have had to add this condition to weed out the tables I have worked on in my recent session...

If(![DateUpdate] > DateAdd("n", 10, ![DateCreate])..

 

[GUIDO22]

The strange thing is, when I run it .. the 'DateUpdated' timestamps given for forms and reports appear to be for when I last acccessed them and not when I made deisgn changes to the objects themselves.....

In the example below, I havent knowingly/intentionally changed this form since end of May 2022 ...
For clarity the 'theDate' variable shown in the picture is a parameter I pass to the function representing the date from which I know I have made changes to the database design..

 

[strive4peace]

hi @GUIDO22

While MSysObjects does store DateUpdate, I seem to recall that using DAO (as mentioned by @CJ_London) gets a different value ...

here's a free tool with source code that may be helpful for you:

Analyzer for Microsoft Access
http://msaccessgurus.com/tool/Analyzer.htm

 

[strive4peace]

ps, @GUIDO22

As for records in tables, the dates stored in MSysObjects and the tabledefs collection are for when design changes were made, not when records were added or changed. To track that, it's helpful to add these 2 fields to every table:

dtmAdd, date/time, default value = Now(), when record was added
dtmEdit, date/time, default value = Now(), when record was edited -- modify on form BeforeUpdate event

If I'm watching a database to see where information is being added, I run the Analyzer periodically because it counts records in every table. This is only an indicator of where data is, but helpful to see which tables are being used and which ones aren't.

 

[sonic8]

Is there a way to determine across the entire database what entities (tables/reports/ forms etc.) have changed since a specific date..?

No.
Without using source code control or any other external/additional means of tracking, there is absolutely no way of knowing reliably when the design or code of objects was changed.

 

[GUIDO22]

hi @GUIDO22

While MSysObjects does store DateUpdate, I seem to recall that using DAO gets a different value ...

here's a free tool with source code that may be helpful for you:

Analyzer for Microsoft Access
http://msaccessgurus.com/tool/Analyzer.htm

That's a great help - thank you. Nice interface too....

 

[arnelgp]

Without using source code control or any other external/additional means of tracking, there is absolutely no way of knowing reliably when the design

i tested with table/query, Access updates the MsysObjects table.
so access is "intelligent" enough to know the date when you change it (the design).
but not the Module?

 

[sonic8]

so access is "intelligent" enough to know the date when you change it (the design).
but not the Module?

Yes, that is the mayor problem.
The whole VBA project is handled by Access as if it were just one object. When you change just one module all modules are considered updated by Access.
VBA internally does not track a modification date for the modules.

 

[CJ_London]

For many years for modules I use a 'log module' using commented out text to record changes, why and when together with a reference e.g. CJ1 then wherever I've made changes I include the reference plus additional information if required. Easy to search on the reference to see all places that have been modified.

 

[strive4peace]

hi @Pat Hartman

I found a discrepancy between the dates too ... and also have SQL similar to yours for quickly getting information from MSysObjects -- always interesting though, to see how you do it, which is a little different. Thanks.

In the Analyzer, if I recall right, a_Objs table gets MSysObjects information, and it gets container properties. dtmCreate is the MSysObjects date. o_DateCreated is the container date, which is filled by code because of the way it is named.

The way the code works, all it needs is the property to collect to be defined in the table thats looping. For instance, in a_Tbls, there's a field called t_ValidationRule that's short text to store, obviously, the Validation Rule if there is one. Each table, for the most part, uses a different prefix to trigger the code so if you ever want it to record another property -- o_ is object, t_ is table, f_ is field, etc. You can easily see them when you go to the table design. The fields that aren't named this way are filled specicially by code.

In a_Tbls, there are also other fields that are populated such as CrDate to get DateCreated from the tabledef, iirc. Then you can compare the dates.

Each of the objects behaves similarly so if you see a batch of fields with this naming and want the Analyzer to get another property, you can simply add the field to store it in and name it the right way

The Analyzer could have a lot more reports than it does -- the idea though, is for developers to make their own queries to show what they want.

 

[strive4peace]

@Pat Hartman -- nothing positive? Really?!? I spent hundreds of hours on the Analyzer and didn't have to share the tool or the source code but I did. So far, everything I've posted has source code because I'm a teacher. Only reading negative comments from someone is disappointing.

> a_r_DeepAnalysis No column header for detail

On the DeepAnalysis report ... the detail description is in the FOOTER, not above, because it doesn't line up, but it is in order. Many of the values are narrow so not enough room to describe them, and some of the columns have multiple pieces of information, depending on what's filled.

> [more complaining]

Everything is open! If you want something else on any of the reports, you can add it yourself, and I know YOU could do that quite easily. There also could be many, many more reports.

A valuable thing is that you can make your own queries and reports, and get the Analyzer to capture any properties it doesn't already get by simply creating fields for them in the right place and naming them with the appropriate pattern, which is described in my previous post.

I know you've written your own tools to get what you want but many don't have the skills to do that. Why so negative? Don't we both want to make it easier for others to use Access effectively and keep Access thriving?

 

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

 

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

 

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