Automatic deployment of front end by VBA and/or batch file (1 Viewer)

fat controller

Slightly round the bend..
Local time
Today, 01:10
Joined
Apr 14, 2011
Messages
758
To date, whenever I have done an update that requires a new front end, I have ensured that nobody is using the database, then changed the name of the folder that contains the back end, updated the table links accordingly, created an .accde file and then saved that on a shared location for people to copy and paste to their desktops.

Inevitably, some users will create a shortcut to the file in the shared folder and use that, or in some cases will simply navigate to the folder and double click on the file therein with the obvious risks involved. One or two of them are even doing it knowing they are doing it, just because they can.

So, I need to move away from this and deploy front ends in some other way - whether that is via a batch file that tells the PC where to copy the new front end from and then delete the other, or via VBA in the database itself. Trouble is, I do not have a clue where to even begin with this, hence coming to all you experts once again.

Thanks in advance :)
 

fat controller

Slightly round the bend..
Local time
Today, 01:10
Joined
Apr 14, 2011
Messages
758
Your suggestion looks good to me :), thank you

There are a couple of bits of the code that I am not quite getting at the moment (and things have just gone a bit manic here at work which doesn't help); I think my best bet it to try this out on my home network with a copy of the front and back-end files - that way there is no risk to anything with me playing about.

I will come back to this thread over the coming days, no doubt with the odd question, and hopefully I can get my head around it.
 

fat controller

Slightly round the bend..
Local time
Today, 01:10
Joined
Apr 14, 2011
Messages
758
Things have calmed down a bit and I still have a couple of hours, so I thought I would give it a whirl - - I am working on a copy of both front and back end anyway, so if I do destroy anything all is not lost.

I have created the tables as suggested, one linked, one local

I have created a form (I've called it verCheck) and have put the Form Load and Form Timer events in, but need some help in understanding what the code is doing (and how I have to adjust it) from the last part of the code:

Code:
If strVerClient = strVerServer Then
Me.Visible = False
DoEvents
DoCmd.OpenForm "Screens "
Else
strMsg = "You do not have the correct version." & vbCrLf & vbCrLf & _
"Would you like to download the latest client?"
If MsgBox(strMsg, vbExclamation + vbOKCancel, "Update Client") = vbOK Then
 
[B]'I understand everything up to this point[/B]
 
strPath = Left(CurrentDb.Name, LastInStr(CurrentDb.Name, "\")) 
strPath = strPath & "Resources\Update.mdb"
[B]'^I assume that the first line is looking for the filename and path of the current front end?  Is the second line to tell it where to find the new version?[/B]
 
strUpdateTool = "MSAccess.exe " & q & strPath & q
[B]'^What is this fellow doing?[/B]
 
 
Shell strUpdateTool, vbNormalFocus
DoCmd.Quit
End If
End If
End Sub

Apologies for being thick, but I like to understand what code is doing rather than just chucking someone else's hard work in and hoping for the best.

EDIT - reading the article a bit further, and playing with the downloaded code, I am now thinking that I need to have Update.mdb lodged somewhere as it is the key?
 
Last edited:

fat controller

Slightly round the bend..
Local time
Today, 01:10
Joined
Apr 14, 2011
Messages
758
OK, I have got my glasses on now (always helps :D) and things are a little clearer (I think :D)

I am assuming that this bit of code
Code:
strPath = Left(CurrentDb.Name, LastInStr(CurrentDb.Name, "\")) 
strPath = strPath & "Resources\Update.mdb"
Is stating where to find the update utility?

So, in my case if I were to change this to the network location that contains the new client and the update utility, it would find it and run from there?

And this bit of code is telling it to run the update utility?

Code:
strUpdateTool = "MSAccess.exe " & q & strPath & q

(q being the filepath & filename?)

Then the rest of the job is taken care of from within the update utility?

Presumably I will have to adjust the code in that to tell it where to find the new client by changing this line of code

Code:
 strSource = strPath & "ClientDB.mdb"
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 09:10
Joined
May 7, 2009
Messages
19,169
here is the pdf i downloaded from the site
 

Attachments

  • Automatically Deploy a New Access Client — DatabaseJournal.zip
    261.6 KB · Views: 321

fat controller

Slightly round the bend..
Local time
Today, 01:10
Joined
Apr 14, 2011
Messages
758
Thank you - I am following it, but struggling a little to get my head around it.

I have managed to get my current database to identify a new version, then open the update utility (I did this by changing strPath to the location of the database Update.mdb on my network)

Now, the utility has opened and stopped (as per the intention to let me step into the code),

At this point, the following variables have loaded:

strMyDb = CurrentDb.Name [so it is showing: \\networkservername.co.uk\documents\foldername\subfolder1\subfolder2\Update.mdb]

strPath = [is the same as strMyDb]

strDest = the same as the above, with "Resources\", & ClientsDB.mdb" to be added to the end

strBkup = the same as the above with "Resources\", & ClientsDB_bkup.mdb" to be added to the end


And I am now perplexed again.

It seems that the next bit of code is going to create a backup of some sort, but what is it backing up?

Indeed, I do not really need it to do a back up of the front end, as I have already taken care of that long before the user would get to this stage.

The Form Timer event makes some sense to me, but I would have to change:

Code:
strSource = "\\networkservername.co.uk\documents\foldername\subfolder1\subfolder2\NewDatabase.accde"

However I don't get the next bit based on the variables that it has just loaded

Code:
FileCopy strSource, strDest
- - it is the strDest that is confusing me, as it is set for the network location that contains the new database front end and the update utility, when in actual fact I want it to copy the new file to the user's desktop?
 
Last edited:

fat controller

Slightly round the bend..
Local time
Today, 01:10
Joined
Apr 14, 2011
Messages
758
After a little bit of experimentation, I now have this working beautifully. Thank you for all your help :)
 

hardsoft

New member
Local time
Today, 02:10
Joined
Apr 20, 2017
Messages
3
@fat
Please how did you make it work. I'm having the same problem with the Path when trying to use it over network.

Please I need your help.
 

DatabaseTash

Registered User.
Local time
Today, 12:10
Joined
Jul 23, 2018
Messages
149

Thank you for this great link! I am attempting to use the second link and just have a couple of questions.

I'm unsure how the version numbers are populated? I'm guessing that you would need to enter version numbers manually to start with. I'm assuming that each time I change a front end ready for deployment I would add this to the table in the back end? But, how does the table in the front end get populated so it knows that it holds a different version to the server?

Also what does the second line mean? I have input my variables, but not sure what the second line is asking for.
' Load variables with correct file name-path values.
strMyDB = \\IS-SBS001\Shared Folders\Resources\Databases\Survey central database and backups\Survey Central (front end).accdb
strPath = Left(strMyDB, LastInStr(strMyDB, ""))
strDest = Replace(strPath, "\\IS-SBS001\Shared Folders\Resources\Databases\Survey central database and backups", "Survey Central (front end).accdb")
strBkup = Replace(strPath, "\\IS-SBS001\Shared Folders\Resources\Databases\Survey central database and backups", "Survey Central (front end)_bkup.accdb")

Thank you
 

Minty

AWF VIP
Local time
Today, 01:10
Joined
Jul 26, 2013
Messages
10,355
I haven't looked at the links, but the process is that the current version number is stored in a local table (the local version), and the master version number is stored in a shared master table.

The master version number is updated when you do a release , and the FE compares the local version number with the master on start up, the result of which fires or doesn't the update process.

Edit: You really should remove the spaces and brackets etc from your file names, that will cause you grief and a heap of extra typing somewhere down the line.
 

DatabaseTash

Registered User.
Local time
Today, 12:10
Joined
Jul 23, 2018
Messages
149
Thanks for the reply Minty. :)

I understand most of the process. The part I don't understand is how does the version number get into the local table?
 

Minty

AWF VIP
Local time
Today, 01:10
Joined
Jul 26, 2013
Messages
10,355
You put it there as part of your distribution/development process, then it's up to date when they load that version.
 

DatabaseTash

Registered User.
Local time
Today, 12:10
Joined
Jul 23, 2018
Messages
149
You put it there as part of your distribution/development process, then it's up to date when they load that version.

So say the user's copy has the version 2.0.6 and there copy for update with version 2.0.7. They would update fine. When there is an addition version released eg. 2.0.8 how does the local copy on the user's PC know that it currently has 2.0.7? Do I need to go and enter that on every PC? Or is it just going to check it every time against 2.0.6 and think it needs to update every time it opens?

Have I overthought this?? :eek:
 

essaytee

Need a good one-liner.
Local time
Today, 12:10
Joined
Oct 20, 2008
Messages
512
So say the user's copy has the version 2.0.6 and there copy for update with version 2.0.7. They would update fine. When there is an addition version released eg. 2.0.8 how does the local copy on the user's PC know that it currently has 2.0.7? Do I need to go and enter that on every PC? Or is it just going to check it every time against 2.0.6 and think it needs to update every time it opens?

Have I overthought this?? :eek:
I hard code a const in the accde file. On opening the accde I have a routine that checks a text file (version.ini), only updates if the version has increased.
 

DatabaseTash

Registered User.
Local time
Today, 12:10
Joined
Jul 23, 2018
Messages
149
Oh, the penny has just dropped.
The revised version number will be in each new updated version before it is rolled out. I was thinking of the file sitting on the user's PC.

Told you I was overthinking it! LOL

Thanks for your help everyone!!
 

Users who are viewing this thread

Top Bottom