Automatic deployment of front end by VBA and/or batch file

[fat controller]

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

 

[arnelgp]

see this link and choose which one is easiest for you. http://stackoverflow.com/questions/17980732/how-to-update-front-end-for-ms-access-2007

but this one is the easiest, on my opinion:
http://www.databasejournal.com/feat.../Automatically-Deploy-a-New-Access-Client.htm

 

[fat controller]

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]

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:

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?

 

[fat controller]

OK, I have got my glasses on now (always helps ) and things are a little clearer (I think )

I am assuming that this bit of 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?

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

 strSource = strPath & "ClientDB.mdb"

 

[arnelgp]

here is the pdf i downloaded from the site

 

[fat controller]

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:

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

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?

 

[fat controller]

After a little bit of experimentation, I now have this working beautifully. Thank you for all your help

 

[hardsoft]

@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]

see this link and choose which one is easiest for you. http://stackoverflow.com/questions/17980732/how-to-update-front-end-for-ms-access-2007

but this one is the easiest, on my opinion:
http://www.databasejournal.com/feat.../Automatically-Deploy-a-New-Access-Client.htm

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]

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]

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]

You put it there as part of your distribution/development process, then it's up to date when they load that version.

 

[bastanu]

If anyone is interested in an alternative please feel free to try my free utility:

http://forestbyte.com/ms-access-utilities/fba-db-launcher/

Cheers,
Vlad

 

[DatabaseTash]

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??

 

[essaytee]

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??

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]

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!!