Distributing a DB Front End 2

[mkallover]

You have a nice new database for a small group (~20) users. You've been a good boy and you've split it into a front and back end and placed the back end on a network folder that is backed up to a tape nightly.

Now, how do you go about distributing the front end to the users? Do you just zip it up and email it? Create a .bat file?

I'm just trying to figure out the best solution. If I just zip it up and email out the front end I worry that if I make an update and send out a new front end that some people won't use it. The users can be pretty bogged down with email at times and I'm afraid they would miss an important update.

I figured some of you would have gone through this many times and have some creative ideas.

 

[dhookom]

I use a script (vbs) file that always copies a new front-end to the users H drive and runs the mdb

'===================
'
' File: Ingredients.vbs
'
' ORIGINAL AUTHOR: xxxxx
' Date : 07/14/2005
'
' COMMENT: Create a subdirectory on the users' H: drive and copy
' the latest version of the Ingredients front end
'===================

Dim fso  'to be used for file related code
Dim f
Dim wsh
Set wsh=WScript.CreateObject("Wscript.Shell")
Set fso = CreateObject("Scripting.FileSystemObject")
If Not fso.FolderExists("H:\Ingredients") Then
    Set f = fso.CreateFolder("H:\Ingredients")
End If
'delete the old file if it exists
If fso.FileExists("H:\Ingredients\Ingredients.mdb") Then
    fso.DeleteFile("H:\Ingredients\Ingredients.mdb")
End If
'copy the new version files to the user's H drive
fso.CopyFile "N:\Depts\Access Formulas\Ingredients.mdb", "H:\Ingredients\"

wsh.RUN "H:\Ingredients\Ingredients.mdb",3
WScript.Quit

Tony Toews has an automated updater at

http://www.granite.ab.ca/access/downloadsindex.htm

Duane
Hook'D on Access
MS Access MVP

 

[pbaldy]

I use a utility similar to Duane's, though I wrote mine in VB6 so it's an exe. One difference is that I have a local table in the front end with version information (version number, date installed, description of changes). My utility compares the max version in the local copy versus the copy on the server and only copies the file if it's different. That way the file isn't copied every time the user starts it, which is a little easier on the network (not that it's big deal either way). I also like having the history of changes.

Paul
MS Access MVP 2007/2008

 

[mkallover]

Thanks Duane, I'll have to give that a go and see how it works.

Paul, you're sounds good too but I don't think I'm going to need quite as much release management as that one provides.

 

[Hap007]

Hi,

One more idea.

Place some type of Version table in the Backend.

When the frontend starts, check the version of the frontend with the required version in the backend.

Then, there would be 4 actions:
1) Version of Frontend is correct, just continue to start.
2) Version of Frontend is not current, but the change is minor, so notify user and continue to start.
3) Version of Frontend is not current, and should not be run against this backend. The change is major, so notify user and stop.
4) Version of Frontend is out of date, and should not be run against this backend. So notify user and stop.

Just a couple of thoughts.
Hap...


Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Accounting Solutions - with free source code
Access Consultants forum

http://www.tek-tips.com/threadminder.cfm?pid=1121

 

[georgesOne]

Hi All:

Two questions:

1. I want to use the code posted by Duane but I get an error message when I run the script (for test purposes I use two directories on my machine):

Error: The system cannot find the file specified.
Code: 80070002
Source: (Null)

I checked ok that the folder has been created nd the file is available in the folder.

If I type (copy from script) the path & file name into the DOS 'Run' box directly it works, so the file is available.

Enclosed the script code... any idea, what happens?


Dim fso 'to be used for file related code
Dim f
Dim wsh
Set wsh=WScript.CreateObject("Wscript.Shell")

Set fso = CreateObject("Scripting.FileSystemObject")
If Not fso.FolderExists("C:\Documents and Settings\gpawlowski\My Documents\LemDatabase") Then
Set f = fso.CreateFolder("C:\Documents and Settings\gpawlowski\My Documents\LemDatabase")
End If
'delete the old file if it exists
If fso.FileExists("C:\Documents and Settings\gparrot\My Documents\LemDatabase\LEM ToolScheduler.mdb") Then
fso.DeleteFile("C:\Documents and Settings\gparrot\My Documents\LemDatabase\LEM ToolScheduler.mdb")
End If
'copy the new version files to the user's drive
fso.CopyFile "C:\Documents and Settings\gparrot\Desktop\LEM ToolScheduler.mdb", _
"C:\Documents and Settings\gparrot\My Documents\LemDatabase\LEM ToolScheduler.mdb"

wsh.RUN "C:\Documents and Settings\gparrot\My Documents\LemDatabase\LEM ToolScheduler.mdb"
WScript.Quit

2. 'gparrot' is my machine user name. How can I get the machine user name of the potential users into this script, so that they can copy the file onto their correct paths, e.g. replace
"C:\Documents and Settings\gparrot\My Documents\LemDatabase\LEM ToolScheduler.mdb"
with
"C:\Documents and Settings\smeyer\My Documents\LemDatabase\LEM ToolScheduler.mdb"

Thanks, georges

 

[dhookom]

You might want to try googling this or finding a scripting forum. It may be an issue with embedded spaces.

Duane
Hook'D on Access
MS Access MVP

 

[georgesOne]

Thanks Duane.
Actually I got something (not sure if it is 'really' working) as follows (the f1 folder simulates the server loction):

Dim fso 'to be used for file related code
Dim f
Dim wsh
Dim f1
Dim f2
Dim fpath

Set wsh=WScript.CreateObject("Wscript.Shell")

Set fso = CreateObject("Scripting.FileSystemObject")

fpath = Left(fso.GetAbsolutePathName(""), Len(fso.GetAbsolutePathName(""))-8)

If Not fso.FolderExists(fpath & "\My Documents\LemDatabase") Then
Set f = fso.CreateFolder(fpath & "\My Documents\LemDatabase")
fso.CopyFile fpath & "\Desktop\LEM ToolScheduler.mdb", _
fpath & "\My Documents\LemDatabase\"
Else
Set f1 = fso.GetFile(fpath & "\My Documents\LemDatabase\LEM ToolScheduler.mdb")
Set f2 = fso.GetFile(fpath & "\Desktop\LEM ToolScheduler.mdb")

'delete old and copy the newer version files to the user's H drive
If f1.DateLastModified < f2.DateLastModified Then
fso.DeleteFile(fpath & "\My Documents\LemDatabase\LEM ToolScheduler.mdb")
fso.CopyFile fpath & "\Desktop\LEM ToolScheduler.mdb", _
fpath & "\My Documents\LemDatabase\"
End If
End If

fpath = fpath & "\My Documents\LemDatabase\"
wsh.Run ("""msaccess.exe"" """ & fpath & "LEM ToolScheduler.mdb"""),3
WScript.Quit

However, I would like to have the application opened 'minimized' as you can do that when using a shortcut, so that the start up form appears directly over the screen.
Any idea how to do that or how can I run the shortcut?

Thanks, Georg

 

[PHV]

have the application opened 'minimized'
wsh.Run """msaccess.exe"" """ & fpath & "LEM ToolScheduler.mdb""",[!]2[/!]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[deek05]

I have a vb app that will copy database shortcuts and/or the database front ends to the users pc.

backend table called Version-BE (linked to frontend db)
frontend table called Version-FE

When the database is opened it compares the 2 tables. If they are different it shuts the database down, prompts you to update and the vb app opens for you to select the database you want updated.

I've used something similar to the one mentioned above but found that if the database wasn't closed down completely before it was being copied over it would throw some errors. That's why I have the vb app. It's nice also that if a user deletes or needs access to the database he can open the vb app and recopy.

Hope this helps

DT

 

[dRahme]

Boy, is this a favorite topic of mine. What I ended up doing was putting the onus on the user to get a new FE. I have a folder on the server where the Latest User Front End Resides.

If I have an update, I copy it into the folder and Rev Up the latest revision the front and back end. The user gets a message that there is an update available. He can ignore it three times and then his FE stops working and he has to get the update.

The unintended consequence of this was users short-cutting to the FE rather than copying it to their desktop which caused its own problems. This was fixed by determining where the front end should not be, which is in the LatestUserFolder.

Anyway, here is how I do it:


'See if the User has the Latest Front End

Dim FeCheck As String, tblBeCheck As String, fCounter As Long

'See how many times the FE has Been Opened

fCounter = Nz(DLookup("[FeCounter]", "tblFrontEndRev", "[FEid] =1"), 0)


FeCheck = Nz(DLookup("[FeRev]", "tblFrontEndRev", "[FeID] = 1"), 0) 'Table in Front End
tblBeCheck = Nz(DLookup("[FeRev]", "tblFeRev", "[FeID] = 1"), 0) 'Table in Back End

If FeCheck <> tblBeCheck Then

MsgBox "There is a New Front End (FE) Available in N:\Start Up\Job Databases\LatestUserDb. " & vbLf & vbLf & _
"Please Delete the Old FE from Your Desktop and Replace it with the Latest FE", vbExclamation, "New Front End Available"

'Update the Counter Field

fCounter = fCounter + 1


'Update the Counter if Person has not installed a new front end

DoCmd.SetWarnings False

DoCmd.RunSQL "UPDATE tblFrontEndRev SET tblFrontEndRev.FeCounter = '" & fCounter & "'" & _
"WHERE tblFrontEndRev.FEID = 1 ; "

DoCmd.SetWarnings True


End If

'Terminate the Application if the Counter exceeds 3

If fCounter > 3 Then

MsgBox "Your Database Front End is too old. " & vbLf & vbLf & _
"There is a new Front End available in N:\Start Up\Job Databases\LatestUserDb " & vbLf & vbLf & _
"Terminating Application", vbCritical, "Database Too Old"

DoCmd.Quit

End If


' This looks to see if the user has short cutted to the database - warning if he has.

Dim WhereAmI As String, WhereIShouldNotBe As String


WhereAmI = CurrentProject.Path

WhereIShouldNotBe = "N:\Start Up\Job Databases\LatestUserDb"


If WhereAmI = WhereIShouldNotBe Then

MsgBox "Please do Not ShortCut to the Database on N-Drive" & vbLf & vbLf & _
"Copy the Database from N:\Start Up\Job Databases\LatestUserDb to your Desktop. " & vbLf & vbLf & _
"This Will Ensure you have the Latest Available Database Update and it helps " & vbLf & _
"Maintain Database Integrity. " & vbLf & vbLf & _
"Terminating Application.", vbCritical, "Please do Not Short Cut!"

DoCmd.Quit acQuitSaveNone

End If