Results 1 to 7 of 7
  1. #1
    scienceguy is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2019

    Store current date/time in a table each time Access file structure is changed and saved


    I have an Access table, which has a record called "current_version." My idea (and low tech solution) for versioning my access database is each time I add or edit the design of the file and save the file, the "current_version" field gets updated. This versioning only applies to the design of the project (e.g. add a new form, edit a table's structure, etc). If I were programming in Excel, I would go to the "ThisWorkbook" object and use the sub "Workbook_BeforeSave". I have been unable to figure out how to do something comparable in Access. Any direction would be appreciated!



  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Ottawa, Ontario, Canada; West Palm Beach FL
    Perhaps a separate table --tblVersionInfo.
    With fields

    VersionID -Unique id
    VersionDate -Date on which version became effective
    VersionComment -Info related to the version; corrective; enhancements; etc.

    If more than 1 person could invoke/activate the version, you may want to identify that person in a separate field in the table.
    This would give you some overview history of the various releases and implementation dates.

    Good luck with your project.

  3. #3
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    I would go to the "ThisWorkbook" object and use the sub "Workbook_BeforeSave". I have been unable to figure out how to do something comparable in Access.
    Access per se does not have any events that you can run, only events on forms/reports - but these won't run if you save a form from design view, and there are no events for changes to tables or queries. So there is no equivalent event to Workbook_BeforeSave.

    I've not tried them but there are a couple of macros you could try - System Commands>Close Database or QuitAccess - probably to run a vba function to update your table. But no idea how you would determine if the db has changes that warrant a new version number. Just opening Access incurs a change to the file. Perhaps the vba can include a prompt 'do you want to update the version number'

    Access does not work like Excel - with Excel you save (or not) the whole file, with Access you save (or not) individual objects.

  4. #4
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Ontario, Canada
    I don't think what's needed here is an event so much as a property. You can define custom properties by either adding your own to the CurrentProject (or maybe it's just CurrentDb) or File > View and Edit Database Properties > Custom tab and choose one of the pre-defined properties.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    "Everyone has a photographic memory; some just don't have film." Steven Wright

  5. #5
    Eugene-LS's Avatar
    Eugene-LS is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Dec 2018
    Private Sub test01()
    Dim s$
        s = CurrentProject.Name ' = "TestDB_v004.accdb"
        s = Mid(s, 9, 3)        ' = "004"
        'Debug.Print "Version is : " & s
        'Version is : 004
    End Sub

  6. #6
    Join Date
    Apr 2017
    You have a specific folder on some network resource, where you do all editing, and where you save your latest version of your DB (FE and BE in case the DB is split);
    You save the new version there with name which includes version number (like YourDbName_v001.accdb);
    You have a table in DB (in both FE and BE in case DB is split), with field(s) for current DB/FE/BE name or version number, or for both, and a fields for current path for folder where new versions are created, and for version registering date and time;
    You must have some Main form (hidden or visible, even for BE) which is activated when DB is opened;
    You design e.g. OnOpen event for Main form, which;
    1. Checks, does current folder match with one in version table. When not, exits event (so version number is not updated when DB file is copied to another location and the name is changed);
    2. Checks, does current DB file name or version number in name match with one in version table. When yes, exits event;
    3. Checks, is version number in file name bigger than one in version table. When not, exits event;
    4. Overwrites current DB file name and/or version number in version table with new one read from current file name. Overwrites registering date and time.

    You create a new version, close and save it with new version number in name. Then you open it again (NB! NOT in edit mode!), and close saving it. It's done!

  7. #7
    scienceguy is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Thank you, **ALL** for your suggestions! You've given me a lot of ideas to try out! Really appreciate the collaboration and coaching!


Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 8
    Last Post: 07-18-2019, 01:36 AM
  2. Replies: 2
    Last Post: 11-06-2016, 09:23 AM
  3. Replies: 1
    Last Post: 08-14-2012, 03:22 AM
  4. Replies: 1
    Last Post: 04-25-2012, 12:36 PM
  5. VB coding to show current date n time in access form
    By cwwaicw311 in forum Programming
    Replies: 6
    Last Post: 02-10-2010, 09:53 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
Other Forums: Microsoft Office Forums - Senior Forums