Automatically distributing MDE front-end 1

[RSGB]

Hi

I've got an Access database split front-end and back-end over a network, with potentially 50 users though the max at one time doesn't exceed about 5.

I want to distribute the front-end locally as an MDE file, to prevent user modifications and cut down on network traffic. I am, however, making updates and adding new functionality to it on a daily basis.

So what I want is for users to automatically copy the latest front-end version as an MDE locally and run that.

I think I need to create a dummy database with an autoexec that makes an MDE of the latest version of the front-end on the network onto a local drive, then run the new MDE file and finally close itself down.

The MS knowldegebase example uses SendKeys which terribly reliable apparently and as it stands currently doesn't work for me...

Does anyone have a good solution to this?

Thanx in advance

 

[tpearo]

An easier approach would be to write a simple batch file on the local drives and put it in a directory in the path. ex. C:\windows\system32 or C:\winnt etc.. The batch file would look something like this.
Name: CopyNew.bat - or you could name it anything you wish
Copy F:\(Databasedirectory)\(Database Filename) C:\(Database destination directory) exit.
Then just have your users go to Start|Run and then type in the name of the bat file (CopyNew) They do not have to type the whole name with extension. This will copy the newest version of your front end to the local machine - over the version that they currently have. I've used this technique for several databases and it is simple to implement.

 

[JaneInMA]

I would suggest you give your users a shortcut to a mde on the network. When you update your mde (each night?) copy over the old mde that the users were using making sure the name does not change. All the shorcuts will still be good but they will link to the new copy. A simple but easy solution. Just keep all your old versions on your area carefully dated and labelled.

 

[RSGB]

Thanks a lot for this guys.

I realise now I was trying to be over-complicated. As most users have a shortcut to the front end where it currently resides I may as well keep on using this. What I have done is:

Turn the front end into a dummy database which on opening copies the latest MDE file (I make every couple of days or so) to the local drive and opens that. Then it closes itself.

All the user sees is a brief delay until the copied local MDE file starts up. After load-up it performs quicker now and I can update the central MDE file whenever I want to rather than wait for everyone else to go home first, YAY!!!

Cheers guys

V

 

[JaneInMA]

RSGB, I hope you are checking back, Could you post the code you use to do this? And do you think it is possible to write this code to check the version of the version of the mde and only copy it if it is new. Being able to update the mde at any time sounds great but we update our mde sporadically sometimes once a week other times only once a month/two months.
Also do you find their speed of access increases if the mde is on their desktop rather than on a server?
Thanks.

 

[DougAtAvalon]

Can some one explain to me these Auto update front end solutions. I write VBA in access and the only solution I know Over a network is to have a separate icon on the desk top that opens and runs a Access db that copies from the server to the client. I guess using copy file or some method. Also can I put functionality on a work station that imports certain objects from a newtorked db

 

[Quehay]

I don't have the chops with SHELL object and scripting (Michael?). There's got to be a good way to send a mail with a "You've got a new front end" message and an hlink to click and have it load in PC. Having a common front end on the network allows you to swap in the new one with no problem (when everybody's out), however this doesn't help with improving speed or reducing network traffic--both admirable objectives with a file-server db (Access). I've seen one code example but it was heinous.

 

[RSGB]

Hi guys

Sorry for the delay in responding!!! Must've accidentally switched off email notification on this one...

OK the database users link to merely has an autoexec macro that runs the following function:


Function MakeLocalMDE()

'copy latest MDE file on network to c drive and run locally

On Error Resume Next

Dim sourceDB As String, targetdb As String
Dim NAcc As Access.Application

sourceDB = "g:\general\database\RSGB Database Front End.mde" 'my latest version on the network
targetdb = "c:\RSGB Database.mde" 'what it's going to be locally

FileCopy sourceDB, targetdb


'open the local MDE file

Set NAcc = CreateObject("Access.Application&quot
NAcc.OpenCurrentDatabase targetdb
NAcc.Application.Visible = True
NAcc.Application.RunCommand acCmdAppMaximize

'Close this instance of Access
DoCmd.RunCommand acCmdExit

End Function


Laterzzz...

 

[MooseMan]

Hi RSGB,

This is an old post so you may not be checking back anymore, but I am very interested in your code for updating to the lastest version of an MDE.

I attempted to implement the code as you have it, and when I run the MDE I do not get any errors but it will not work either. It opens and closes the instance of Access but does not copy the new MDE from the server, and does not open the old version either.

I am very interested in any suggestions for distributing updated front ends to client machines.

Thanks in advance if you are able to help

Bruce

 

[RSGB]

Hi Bruce,

This did happen on a couple of machines when we first installed it. It was caused by references not being right e.g. if you have a reference set for Word 2000 but the user has Word 97 (application needed to use early binding). My application had references to DAO 3.51, Word 9 and Outlook 9 to match what users should have had according to our IT dept. But a few had the old version of DAO 3.5 and some still had Office 97.

Solution was to upgrade Office to 2000 apart from Access 97, and install SR2b if they had an earlier version of Access 97

The MDB they open should have minimal references set and check what these are before opening the MDE.

Can you just run the MDE?

Here is what my code currently looks like:



Option Compare Database
Option Explicit
Declare Function dllregisterserver Lib "g:\general\database\phGantXControl.ocx" () As Long

Function MakeLocalMDE()




Dim ref As Reference
Dim StrPath As String
Dim run, dbPath As String


'check right version of Access
run = SysCmd(acSysCmdAccessVer) 'this may not work I guess anyway
If run <> 8 Then MsgBox (&quot;Wrong version of Access - this database requires Access 97 SR2&quot: Quit

run = SysCmd(acSysCmdAccessDir)
run = run & &quot;msaccess.exe&quot;
If FileDateTime(run) < #10/10/2000# Then MsgBox (&quot;This database works best on Access 97 SR2 whereas this is Access 97 SR1 or earlier. Some aspects of the database may not function as intended. You are advised to get IT to update to SR2...&quot

'check right path being run
run = Access.Application.CurrentDb.Name
If run <> &quot;G:\General\database\socpilot.mdb&quot; Then MsgBox (&quot;Wrong file location - this database must be run with \\Viper1\Surveys mapped to the G drive (i.e. it lies on 'G:\general\database\socpilot.mdb'&quot: Quit

'Now to check references

On Error GoTo ErrorThing

StrPath = &quot;g:\general\database\phGantXControl.ocx&quot;
'register control
run = Shell(&quot;regsvr32.exe /s &quot; & StrPath)
' Create new reference. If this triggers an error 32813 then it's already set
'Set ref = References.AddFromFile(StrPath)

GoTo CopyBit

ErrorThing:
If Err = 32813 Then Resume Next 'it was already set
MsgBox (&quot;Problem with the reference &quot; & StrPath & &quot; - Error &quot; & Err & &quot;: &quot; & Err.Description)

CopyBit:

'On Error Resume Next


Dim TestWord As Object

Set TestWord = CreateObject(&quot;Word.application&quot

run = TestWord.Version
TestWord.Quit
Set TestWord = Nothing


'On Error Resume Next






'copy latest MDE file on network to c drive and run locally if it is not the latest version

Dim sourceDB As String, targetdb As String
Dim NAcc As Object 'Access.Application

If Val(run) = 8 Then sourceDB = &quot;g:\general\database\Front End v8.mde&quot;: targetdb = &quot;c:\RSGB Database V8 (DO NOT RUN).mde&quot; 'what it's going to be locally'my latest version on the network
If Val(run) = 9 Then sourceDB = &quot;g:\general\database\Front End v8_9.mde&quot;: targetdb = &quot;c:\RSGB Database V89 (DO NOT RUN).mde&quot; 'my latest version on the network


'If FileDateTime(targetdb) < FileDateTime(sourceDB) Then
FileCopy sourceDB, targetdb
'End If

'open the local MDE file

Set NAcc = CreateObject(&quot;Access.Application&quot
NAcc.OpenCurrentDatabase targetdb
NAcc.Application.Visible = True
NAcc.Application.RunCommand acCmdAppMaximize

'Close this instance of Access
DoCmd.RunCommand acCmdExit

End Function



Hope this helps

 

[fule12]

Ih,RSGB

I'm new in Code programming so if you can help me with this of your code.
1. where i put this function in mdb or in mde?
2. i found that you have same ActivX control
- StrPath = &quot;g:\general\database\phGantXControl.ocx&quot;
So my question is :

I make front-end and back-end.Bouth i put on server and i copy front-end(mde) to user pc.
So function i call from on load event from Switchboard ? right?
and what to do about ActivXControle?

Please Help
Thanks
Fule

 

[Kraeg]

The way I went about this was to create an application (using VB5) that is linked to from the desktop/start menu. This application loads the database in 5 seconds unless the user clicks Utilities (which allows them to restore the frontend MDE (if it becomes corrupted), backup the backend data, and repair/compact).

When the MDE loads, it checks a few database properties (version and build). Then, depending on the version of Access running, it checks one of three (for 97/2000/2002) network directories for the source MDE, and checks the same properties of that.

If build and version are higher in the server directory MDE than the local MDE, it writes a file locally (such as &quot;update.08&quot; for Access 97) and notifies the user to exit and restart to update.

The user exits, restarts the VB5 app., which then checks for the &quot;update&quot; file. If found, it copies the appropriate source MDE to the local drive.

When this updated MDE is first loaded, it may copy any updated Word documents etc. that the database uses. Then it creates a flag to show that that has been done and so it won't do it again.

In the frontend MDB I have a module that I run each time I've made changes and am about to create an MDE. This module prompts for and updates some properties, such as the version of Access the frontend is for, and the version and build (it auto-increments build, but I can change it to something else).
If these properties don't exist, they are created then prompted for (when doing conversions to different Access versions they disappear).

I've got some planned changes, though, to make it less obvious to the user it is updating (more hidden and automated).