Access 2010/2013: Latest Module Build Timestamp, Automate Build Number

There does not seem to be a way to get the date and time of the last "Compile..." in an Access App.

I want to keep track of each build and display the same within forms of the app (splash, about...)  
A date and time would be sufficient for this case.  

I am unable to use the *.accdb file's last modified date as this does not always change when a compile takes place and changing data will change this date as well.

Failing that, if I could hook the compile process one could use a timestamp and /or build number captured upon each event and store with the application data but alas I do not see such a hook process either.  

The only version/build numbers are attached to the application object and refer to Access itself
The modules collection does not have a build number of timestamp attached to the module object

Since the application grows in size with each build and I need to Compress and/or uncompile I was hoping there might be some secret stuff that is not exposed to intellisense that I might be able to use...

Any ideas?
Thanks
SpiritofBoazAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
This is one of Access' limitations and why many do not record Access as a development environment.

What I do is simply - from time to time and at least once a day - to close down Access and zip the accdb file appending the file name with a "yyyy-mm-dd hhnn" timestamp. Then I compress and/or decompile the file (with the /decompile switch), compress again, compile, and move on.

This is so easy to do and manage that I get it done.

/gustav
Dale FyeOwner, Developing Solutions LLCCommented:
I include a form (frm_Version) and table (tbl_Version) in all of my applications.  Whenever I'm done adding a feature to the application (generally several times a day), I will add text to that table indicating what has been completed and update the version number (call it the build).

When the form closes, it saves two database level properties:
AppVersion
VersionDate

It also updates the AppTitle to include the Version #

then I complete a compact and save process similar to what Gustav mentioned above.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PatHartmanCommented:
Another imperfect solution is to use a query to find the last change date for certain objects.  I display the top value from this list on my menu as the last changed date for the app.  I tried keeping a manual table log but found that I couldn't be relied on to keep it up to date and this method at least does not depend on me remembering to do anything before I close the app.  In some apps, I additionally keep a log of when the app was changed that uses this date for comparison as the app is shutting down.  If the query returns a date newer than the change date of the last backup, the app prompts to ask if I want to make a backup (it doesn't prompt users since they don't make design changes.  It only prompts me) and then copies the database to a backup folder with a date and time affixed to the db name.  This is a painless way of making sure that you backup frequently.  At the end of the day, or week, you can delete the backups you don't want.

SELECT MSysObjects.Name, MSysObjects.DateUpdate, MSysObjects.Type
FROM MSysObjects
WHERE (MSysObjects.Name Not Like "MSys*")
AND (MSysObjects.Type=-32768 Or MSysObjects.Type =5 Or MSysObjects.Type =-32764 Or MSysObjects.Type=1)
And (MSysObjects.Database is Null)
And (MSysObjects.Connect Is Null)
ORDER BY MSysObjects.DateUpdate DESC;

This looks at the update date for forms, reports, queries, and local tables.  You could also include macros if you use them.  You cannot include modules or linked tables since those dates are updated whenever the link is refreshed which has nothing to do with a schema change and when the app is compiled.  the problem is that the module date is updated for other reasons also.

As others have mentioned, there really is no reliable way to find a definitive last changed date or last compiled date.
SpiritofBoazAuthor Commented:
Given that it is not possible to reliably get the latest code change date... what I ended up doing is letting the code tell me when it has changed tracking each “Build” in a table as Dale suggested.

I created a class module (AppVersions) and matching table to track the following for each “virtual build”

    With myAppVersions
        .intMajorVersion        = m_rsKnownBuilds!MajorVersion
        .intMinorVersion       = m_rsKnownBuilds!MinorVersion
        .lngBuildNumber          = m_rsKnownBuilds!Build
        .dtLastBuildDate        = m_rsKnownBuilds!BuildTimeStamp
        .strBuiltBy             = m_rsKnownBuilds!BuiltBy
        .strBuildComment        = m_rsKnownBuilds!comment
        .strBuildCRC       = m_rsKnownBuilds!BuildCRC
    End With

I used the program’s splash screen’s Load and Unload events to do the following:
 
Upon Form Load (Application startup):
1.)       Load Known versions into  a private record set property within the static (er, I mean global) class. Set m_rsKnownBuilds = CurrentDb.OpenRecordset("SELECT * FROM AppVersions order by Build DESC")
2.)      Build an appropriate display string of the most recent version for the splash screen
3.)      Hide instead of closing the splash screen


Upon Form Unload (exit of the application):
1.)      Enum all code objects procedure by procedure capturing applying a CRC to the actual code in the proc to get a CRC value.  E.g. “TextModuleName.Copy_One_File.Sub.3.5AF55881+ vbCrLf”  Once I have this line for all code procedures I then CRC all these strings to create a final CRC64
2.)      If the new FinalCRC64 is different from the latest version the build number is incremented and saved with the CRC64 and other fields as a new build

I can call the same  function in the AppVersions Class from the Immediate window when I wish to create a new major and/or minor version along with a build comment  along with the bumped build number.

I can also call a function in the Immediate window to output the current list of all procs in all modules along with number of lines and the CRC value.  This method could also allow a hook to actually check in code to a source code repository.   The following link explains this method:

http://www.codeproject.com/Articles/640258/Deconstruction-of-a-VBA-Code-Module
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.