Auto FE Update

[Guest]

is it possible to update the FE for all users from my pc "admin" . or i have
to copy the latest version to every one every now and then?

 

[Guest]

I've been working on an auto-update solution for the past two weeks, and
though others may think otherwise, I think I have it down for all intensive
purposes.

I don't know how you have your database set up, but it sounds similar to
what I've got. My database is used by about 10 PCs on a computer network. I
have my BE stored on a network drive (M and everyone has their own copy of
the FE. All of the tables and data are in the BE, of course, except for one
table that is stored locally in the FE that keeps track of the version
number. A separate linked table in the BE keeps track of the version that is
available on the network.

So, we need to make it to where when the database starts up it will check
the tables for both file versions and if necessary, copy the new FE off the
network drive onto the local hard drive.

Unfortunately, this can't be done solely with Access, as you can't
delete/overwrite a file that is in use. So, we need to create a launcher
program. You might be able to create an access database that will do this
with autoexec macros and such, but a better solution would be to make a
Visual Basic application. Microsoft is releasing Visual Studio 2005 right
now I think, but that requires .NET 2.0, so you might be better off using
Visual Basic 6, which doesn't have any .NET dependancies.

It took me a while to write this, so I hope you can make use of my code and
save yourself some time. I couldn't get it to work on Win98 computers for
some reason, even though they have the STKIT432.DLL library. It worked fine
on XP computer though. I don't know about 2000 or NT. The program basically
does the following: 1) Get file versions and compare them. 2) If necessary,
out with the old file, and in with the new. 3) Start up the Access database
by using one of the shortcuts provided in the program folder.

Step 3 is extremely jury-rigged, if you look at the code. Probably could
have made the code better with some work, but hell, reading from the registry
is a nightmare as far as code goes. If it ain't broke, don't fix it! And it
works, so I'm fine with it.

There are something like five different shortcuts for the database for
different versions of Access, and the code looks to choose which one should
be launched. I needed this because I was having permission errors if I
didn't use a shortcut to launch for some reason. No idea why. It was like
it didn't recognize my BE tables if I didn't launch from the shortcut, or my
mail merge letters wouldn't open, or a plethoria of other problems.

Basically though, if you have any sort of security built in to your
database, you might need to stick to this jury-rigged soultion. Otherwise,
if you don't use security or separate workgroup files, you could probably
just ShellExecute into the database without any problems.

hth,

Nick

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Visual Basic 6 Code:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Private Declare Function fCreateShellLink Lib "STKIT432.DLL" (ByVal
lpstrFolderName As String, ByVal lpstrLinkName As String, ByVal lpstrLinkPath
As String, ByVal lpstrLinkArgs As String) As Long
Sub Main()
On Error GoTo EH
Dim objConn As New ADODB.Connection
Dim objRs As New ADODB.Recordset
Dim arguments As String
ConnectionString = "Provider=Microsoft Jet 4.0 OLE DB Provider;Data
Source=C:\Program Files\MVC Marketing\bin\prototype.mdb;Jet OLEDB:System
database=M:\MVC Marketing\_prototype\bin\workgroup.mdw;User
ID=mvcinfo;Password=mvcinfo;"
objConn.Open ConnectionString
CommandText = "SELECT [version] FROM [_local] WHERE [index]=1"
Set objRs = objConn.Execute(CommandText)
verlocal = objRs(0)
CommandText = "SELECT [version] FROM [_settings] WHERE [index]=1"
Set objRs = objConn.Execute(CommandText)
vernetwork = objRs(0)
objRs.Close
objConn.Close
Set objRs = Nothing
Set objConn = Nothing
Set fs = CreateObject("Scripting.FileSystemObject")
If vernetwork > verlocal and fs.FileExists("M:\MVC
Marketing\_prototype\bin\prototype.mdb") Then
MsgBox "A new verion of the database is available on the network! It
will be downloaded automatcially.", vbInformation, "New Version"
If fs.FileExists("C:\Program Files\MVC Marketing\bin\prototype.mdb")
Then
Kill "C:\Program Files\MVC Marketing\bin\prototype.mdb"
End If
FileCopy "M:\MVC Marketing\_prototype\bin\prototype.mdb","C:\Program
Files\MVC Marketing\bin\prototype.mdb"
End If

Set fs = CreateObject("Scripting.FileSystemObject")
If fs.FileExists("C:\Program Files\Microsoft
Office\Office13\msaccess.exe") Then
Shell Environ$("comspec") & " /c ""c:\program files\mvc
marketing\links\office13 - mvc marketing.lnk"""
Else
If fs.FileExists("C:\Program Files\Microsoft
Office\Office12\msaccess.exe") Then
Shell Environ$("comspec") & " /c ""c:\program files\mvc
marketing\links\office12 - mvc marketing.lnk"""
Else
If fs.FileExists("C:\Program Files\Microsoft
Office\Office11\msaccess.exe") Then
Shell Environ$("comspec") & " /c ""c:\program files\mvc
marketing\links\office11 - mvc marketing.lnk"""
Else
If fs.FileExists("C:\Program Files\Microsoft
Office\Office10\msaccess.exe") Then
Shell Environ$("comspec") & " /c ""c:\program files\mvc
marketing\links\office10 - mvc marketing.lnk"""
Else
If fs.FileExists("C:\Program Files\Microsoft
Office\Office\msaccess.exe") Then
Shell Environ$("comspec") & " /c ""c:\program
files\mvc marketing\links\office - mvc marketing.lnk"""
Else
MsgBox "Microsoft Office was not detected on this
computer! Please verify installation or reinstall to default directories.",
vbCritical, "Microsoft Office not found!"
End If
End If
End If
End If
End If
Exit Sub
EH:
Set fs = CreateObject("Scripting.FileSystemObject")
If fs.FileExists("M:\MVC Marketing\MVC Marketing Installer.exe") = False
Then
arguments = "Please check network connections and drive mappings."
Else
arguments = "Files may be missing or corrupt. Please re-install
application."
End If
MsgBox "Error launching MVC Marketing database. " & arguments,
vbCritical, "Error"
Exit Sub
End Sub

 

[Joan Wild]

I don't know how you have your database set up, but it sounds similar
to what I've got. My database is used by about 10 PCs on a computer
network. I have my BE stored on a network drive (M and everyone
has their own copy of the FE. All of the tables and data are in the
BE, of course, except for one table that is stored locally in the FE
that keeps track of the version number. A separate linked table in
the BE keeps track of the version that is available on the network.

So, we need to make it to where when the database starts up it will
check the tables for both file versions and if necessary, copy the
new FE off the network drive onto the local hard drive.

Unfortunately, this can't be done solely with Access, as you can't
delete/overwrite a file that is in use. So, we need to create a
launcher program.

I use this method and just have a batch file that copies the new frontend.

In Access, after it checks the versions, and an update is available...

Shell "C:\hmksch\update.bat", vbNormalFocus
DoCmd.Quit

Other solutions:
http://www.granite.ab.ca/access/autofe.htm
http://www.databasejournal.com/features/msaccess/article.php/3286111