MSysObjects - Update all queries Modified Date and time- TOUCH

[fredp1]

Hi,

I need to update the modified date/time of all queries to have a particular date and time.
This would be similar to a 'TOUCH' command.

By having all queries with the same date/time, Auditors can aesily tell is a query has been modified.

Does anyone have some code to do this as a SQL Update on MSysObjects is not allowed.

Thanks
Fred

Cheers
Fred

 

[JoeAtWork]

By having all queries with the same date/time, Auditors can aesily tell is a query has been modified.

No they can't, because you just cheated by artificially changing them to whatever value you fancy. Access already updates the modified date, without bias, so there's no need for you to do anything.

I would strongly advise not to try to tamper with the system tables. Among other reasons, you may end up corrupting your database.

Joe Schwarz
Custom Software Developer

http://www.plandev.net

 

[fredp1]

Hi,

Any suggestions on changing it programmatically?

Cheers
Fred

 

[MichaelRed]

"I would strongly advise not to try to tamper with the system tables. Among other reasons, you may end up corrupting your database."

all of the hidden MSsys* Tables are S Y S T E M tables. More or less the equivalent of dbSchema. They are used by the underlying sbsoftware to maintain an ACCURATE version or what is in the system (Ms Access application). Mucking about in these is the quickest and sureest way to corrupt (destroy) you application.




MichaelRed

 

[SkipVought]

Any suggestions on changing it programmatically?

I would strongly advise not to try to tamper with the system tables. Among other reasons, you may end up corrupting your database.

It could not be any clearer!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[fredp1]

While i appreciate the criticality of the system tables, I was just trying to get the date modified updated.

The way the responses are written is best if you do not touch them, as appossed to its impossible to do.

If it is impossible to do with a program, I'm forced to use a cumbersome manual metthod of re-saving the queries one by one.

I just wished that a elegant solution was available!

Thanks
Fred

Cheers
Fred

 

[JoeAtWork]

I don't understand why you want to change the date modified. Whatever it says is accurate, it's the date that it really was modified. If you went and changed it it would have no useful meaning.

Joe Schwarz
Custom Software Developer

http://www.plandev.net

 

[fredp1]

In the 'old' days, there was a program called TOUCH.
It was used to change the modified date of a file. That way developeres could distribute code and easily tell if source or a file has been modified. The date would stand out for that one file if a change was made.

I'm doing the same thing with all my queries so I can readilly tell if a query has been modified compared to the released version.

Specifically I using access for data migration from one ERP system to another. I work in the Pharmaceutical industry where everything has to be documented and validated, so documenting the queries with a specific mod date as part of our testing phase and then documenting the queries used during Go-Live, I can easily control and show to auditors that the code didn't change.

Fred

Cheers
Fred

 

[MichaelRed]

So, the "Audit" is ONLY for the queries? Transfering data can (often does) require more than queries. If ANY other items exist in the process ([UDF | Form | UDT | ... ) which are not 'audited', the results can be different and no one cares?

Alternatively, you can (sort of) apply the technique of changing all the dates of all of Ms. A's top level objects by creating a NEW (version at least) of the app and importing all the objects from the previous version.

Then, again, if the audit is going to JUST be on hte basis of some arbitrary date, the db app (ye olde .MDB file) itsself included its creation and modification dates. Of course these are modified by the OS so it is difficult to maintain them as 'static' and still use them.

Thirdly, just capturing the revision dates of individual top level items in a .MDB app is as simple as dumping (selected fields of) MSysObjects to some (any?) other entity and providing this list along with access to the database in question seems as useful as the effort you keep trying to invest.

On a pratical level, you can even link to the MSsy* tables in two different versions of an app and (with the exception of MODULES) directly compare the creation and revision dates.

So, now you have a host of hordes advising you against mucking about with, in, and around system tables AND at least some (by no means all possible or even reasonable) suggestions on ways to avoid running against the grain while still accomplishing the goal.

But, of course, (there is always a BUT!) you needed to participate in a somewhat positive manner - not just continue to insist on following your initial thought - despite the (best?) advice available.




MichaelRed

 

[PHV]

A starting point in VBA:

Set myDb = CurrentDb
For i = 0 To myDb.QueryDefs.Count - 1
  strSQL = myDb.QueryDefs(i).SQL
  myDb.QueryDefs(i).SQL = strSQL
Next
myDb.QueryDefs.Refresh

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886