Solved List Database Design Changes, since .. (1 Viewer)

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

 

[Pat Hartman]

Here's a query I use:

SELECT MSysObjects.Name, MSysObjects.DateUpdate, MSysObjects.Type, MSysObjects.Name & " -- " & MSysObjects.DateUpdate AS UpdateData, tblObjectTypeCodes.TypeDesc
FROM MSysObjects LEFT JOIN tblObjectTypeCodes ON MSysObjects.Type = tblObjectTypeCodes.Type
WHERE (((MSysObjects.Type) In (-32768,5,-32764,-32766)))
ORDER BY MSysObjects.DateUpdate DESC;

Here's the table in the join. If you have object types not listed, just add them to the table. Finding all the types is a trial and error endeavor since the MSys objects are not defined anywhere.

Type    TypeDesc
-32768    Form
-32766    Macro
-32764    Reports
-32761    Module
-32758    Users
-32757    Database Document
-32756    Data Access Pages
1    Table - Local Access Tables
2    Access Object - Database
3    Access Object - Containers
4    Table - Linked ODBC Tables
5    Queries
6    Table - Linked Tables or Files
8    Relationships
9    Constraints

The problem with this solution is that it is not reliable. The DateUpdate only seems to get updated when something related to DAO is modified. So, if you add controls to a form/report or move them around, the DateUpdate won't change. However, if you change the RecordSource, it will get updated. I haven't checked Crystal's solution but I will and see if it differs from what i have found to be the flaw with MSysObjects.

 

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

 

[Pat Hartman]

Looks like the Analyzer has the same issue that MSys objects does Too bad. I was hoping you had found the solution. Here's a picture. It shows three differences. The Analyzer is showing the same info as MSysObjects but that differs from what you see in the Navigation Pain (sic). I've talked to a couple of MVPs regarding this discrepancy and there is no good solution that they have found either.

I've got a great backup solution I would love to share but it needs RELIABLE Update dates to actually work. No one was able to explain when MSysObjects did get updated so that is still a mystery also. They only thing they could confirm was that if something DAO related got changed, then the MSysObjects would be in sync.

PS, in case anyone is curious about why the query in #15 selects specific object types is because this is the query I use to determine if i need to create a backup and the dates for tables are irrelevant when you are working with VBA and form/report/query objects and the other object types don't have reliable dates at all. So, for example, you can never tell when code modules changed.

 

[Pat Hartman]

@strive4peace I looked at a bunch of reports and found these things you might want to know about.

a_r_DeepAnalysis    No column header for detail
usys_qObjectsSummary    no date created or updated for  queries and local tables
a_r_FORM_SUBFORM_RecordSource    Only shows SQL strings. Doesn't show table or query name

 

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

 

[Pat Hartman]

I'm so sorry Crystal. I didn't mean to complain. It is a great tool and you did a great job. I can tell how much time you spent with it having done something similar myself. I should have started with that. It is Access that causes the date problem, not you. I was 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.

Maybe you can get MS to give us the answer regarding the dates or fix the MSysObjects table. I know that they specifically say that we should never depend on any MSys tables so they have an out. Ben offered a very convoluted solution last year but I couldn't get it to work. It wouldn't have been useful anyway since it required you to specifically look at some setting, object by object. There was no way to loop through the objects.