Versioning of database

[jaryszek]

hi Guys,

i would like to add mechanism or something like that to have version of my database.
For example any changes to table1 (adding rows, deleting them) should be write as version2.

And next time i should easily get to database and have possibility to choose which version i want to see.

What are your best solutions for this?



Thank you,
Best Wishes,
Jacek

[sdel_nevo]

Hi jaryszek

I have had this requirement as well, but to be honest with you, the way that works best for me and the one that's best is to copy the db call it "your DB name v2" then make the amendments to this version

for example

if my database is called test.mdb

rename the copy to test_v2.mdb

then make the changes to this new version


if your backend is split, you will need to relink the front end tables to the newly edited version.

Steve

[isladogs]

I have a table with version number and date both of which can be updated from a form.
I also have a version.txt file in the same folder which contains the version number and is automatically updated from the same form.

In addition, I have a backups folder with subfolders for each version.
When a backup is made, this is saved with version number and current date & time added in yyyymmddhhnnss format automatically
E.g ridders52_3571_20180223080523.accdb

This is also included in the version update as follows
A backup of the 'old' version is first saved, then the version number and date is updated together with the text file.
Next a new backup subfolder is created for the new version
Finally a backup is made of the 'new' version

The method works well.
Possibly overkill but it makes it almost impossible to lose work

[jaryszek]

thank you Guys,

ridders52 - so you have possibility to select which version of BE user can relink front-end?

So you are creating manually backup (versions) and have possiblity to choose what you want to use?

Jacek

[isladogs]

thank you Guys,

ridders52 - so you have possibility to select which version of BE user can relink front-end?

So you are creating manually backup (versions) and have possiblity to choose what you want to use?

Jacek

Yes - if necessary - I also have routines to backup all linked backend databases using the same suffixes

Everything I described is done using VBA procedures
Updating the version number causes everything else to happen automatically

One more thing - I also have two autokeys macros related to this ... purely for my convenience
1. Increase version number by 1
2. Decrease version number by 1

When either of these keyboard shortcuts are used, all the code is run again automatically ....PROVIDED the logged in user is me as the developer

[jaryszek]

Love your idea!

Could you please (very please) share with sample database?

I would love to use this code in my model.

How are you creating backups ? Simple copying database? (do not do compact and repair - it can blow up your database and it is dangerous).

the best option would be have all history within this version form.
So you can add description to database somehow and see what was changed in new version (for example added Person1 to table Person).

Jacek

[Minty]

I use a similar technique to Ridders.

However I do compact and repair and decompile, before releasing a new version.
It reduces the file size and keeps the gremlins at bay - it certainly doesn't cause corruption.

[jaryszek]

Minty, thank you.

Please read this article:

http://rogersaccessblog.blogspot.com...e-feature.html

and you have find out why automatically back ups are not good idea.
Eventually copy your database to your local disk, do compacting.

What about sample database?
Coul you please share?

Best,
Jacek

[isladogs]

Love your idea!

Could you please (very please) share with sample database?

I would love to use this code in my model.

I'm sorry - I need to treat that as proprietary software though I've published various parts of it in response to questions here & in other forums
The code is fairly complex & located in a large number of places.
It also took me a long time to get just right

How are you creating backups ? Simple copying database? (do not do compact and repair - it can blow up your database and it is dangerous).

I use code to create the backup as already explained
Disagree with your comment in brackets
I also compact the backup copy - C&R can cause issues on current copy in certain circumstances but if done wisely it's a very important developers tool

Also I follow exactly the same process as Minty

I do compact and repair and decompile, before releasing a new version.
It reduces the file size and keeps the gremlins at bay - it certainly doesn't cause corruption.

the best option would be have all history within this version form.
So you can add description to database somehow and see what was changed in new version (for example added Person1 to table Person).
Jacek

Up to you how you to an audit of changes.
I no longer keep a version history record in the database as I do far too many changes for that to be practical.
Also I once lost a large database including all change history so its not the method for me
Instead I comment the code extensively with date / version & purpose and do a version modifications summary in a word document

[jaryszek]

Thank you ridders52,

I'm sorry - I need to treat that as proprietary software

It is a pity but i will write similar code which unfortunately will take more time.
And will share here for others ;-)

I use code to create the backup as already explained

When a backup is made

It is a bunch of methods for creating backups, are you just copying database or what VBA code are you using?

Disagree with your comment in brackets
I also compact the backup copy - C&R can cause issues on current copy in certain circumstances but if done wisely it's a very important developers tool

You can do it of course but when you are working in network environment and something goes wrong (for example connection error) your database copy will be corrupted.
Good post where i was asking about it is here:

https://www.accessforums.net/showthread.php?t=67800

I also used automatically backups , here is my code:

Set fso = CreateObject("Scripting.FileSystemObject")    
    Set db = CurrentDb


    DateUnderscore = Format(Now(), "yyyy-mm-dd")
    FilePath = "\\nt-wro4-01\dane\ihrm\dokument\worek\KADRY\DOKUMENTY _BRAKI\Back-End\"
    OriginalFile = "Aplikacja_Braki_BE.accdb"
    FileWithoutExtention = Left(OriginalFile, InStr(OriginalFile, ".") - 1)
    DoCmd.Hourglass True
    FilePathDestination = "\\nt-wro4-01\dane\ihrm\dokument\worek\KADRY\DOKUMENTY _BRAKI\Archiwum_kopie\"
    
TryAgain:
    'Compact the Back-End database to a temp file.
    
    If CheckLock(FilePath & OriginalFile) = True Then
        Access.Quit
    End If
    
    DBEngine.CompactDatabase FilePath & OriginalFile, FilePath & FileWithoutExtention & "Temp.mdb", , , ";pwd=1234"
    
    'Delete the previous backup file if it exists.
    If Dir(FilePathDestination & FileWithoutExtention & ".bak") <> "" Then
        Kill FilePathDestination & FileWithoutExtention & ".bak"
    End If


    'Rename the current database as backup and rename the temp file to
    'the original file name.
    Name FilePath & OriginalFile As FilePath & FileWithoutExtention & ".bak"
    fso.copyfile FilePath & FileWithoutExtention & ".bak", FilePathDestination & FileWithoutExtention & ".bak"
    Kill FilePath & FileWithoutExtention & ".bak"
    
    Name FilePath & FileWithoutExtention & "Temp.mdb" As FilePath & OriginalFile
    DoCmd.Hourglass False
    
Access.Quit
    
Exit_cmdCompact:
    Exit Function


Err_cmdCompact:
    If Err.Number = 3356 Then
        Resume TryAgain
    ElseIf Err.Number = 3045 Then
        Resume TryAgain
    Else
        MsgBox Err.Number & ": " & Err.Description
        Resume Exit_cmdCompact
    End If

Best Wishes,
Jacek

[Bulzie]

Just curious, why not do what you need inside your database. Anytime a record is added or changed, you can write that to a history table and include the current record and then the changed record and along with all the data fields, put same unique record key and datetime on both so you know they go together. And if its an add, just add the 1 record. This will give you history of anything added or changed in your database. The user would just see the latest updates but you could create a history form to show all the updates if needed.

If you really want to actually do backups and version, then forget the above. But that could get really confusing as in how many versions will you have, when will you create the version and all the relinking tasks.

[Minty]

I'm not compacting a network version. I'm compacting my local development copy.

I have a Development Database that lists all my current projects in a list box. It lists the current live version number, the dev version number, and when I last opened it.
When I select one to open the target database is date time stamped backup (copied to a network store), then opens the local dev copy.
I close, compact and repair, frequently daily and about once a month go and tidy up the 100's of back ups I have in the network storage.
Worst I have ever lost was about 2 hours work when my hard drive crashed properly...

The networked FE copy is locked down "read only" to normal users to copy to their local machine. This stops them inadvertently trying to run it over the network, and corrupting the master copy.
I have write access to that folder, and use version numbering to control when updates are "forced" onto end users.

All my Backend databases are SQL Server based - that's another topic :-)

[jaryszek]

Thank you Bulzie !

You are talking about something like in my attachement?

thank you Minty, that is explanation!

When I select one to open the target database is date time stamped backup (copied to a network store), then opens the local dev copy.

Wow! Amazing!

So you are opening local copy of target dababase, creating copy into network store (already compacted) with good time stamped and done changes within in, yes ? Could you please confirm if my thinking is correct?

Thank you,
Jacek

[Minty]

The copy I open and work on is the development copy. Every time I open it it gets backed up.
My development backups are onto a enterprise storage array network, that is itself backed up as part of the business infrastructure.

Before publishing I always compact and repair and version number update my Dev copy - then Publish to the Master FE location making an automatic previousVersion_DatabaseName in that location as well.
The network saved Master FE for users to get to their desktops is also on a different SAN.
There is always the previous version available there as well, in case of some failure by the developer to spot the glaring obvious cock up he's just introduced. (Despite apparently extensive testing)

[isladogs]

Minty beat me to it.
I was also talking about compacting my local development copy - NEVER the copy on the network
As already explained I do that as part of a through backup strategy so I also rarely lose anything of significance

I share a large proportion of my code both here and even more is available on other forums.
However I have to make a living as well and NEVER share code written as part of contracts for individual clients

It is a pity but i will write similar code which unfortunately will take more time.
And will share here for others ;-)

Fine by me