My FE updater - whadya'all fink?

123 views
Skip to first unread message

Mike MacSween

unread,
Jul 13, 2004, 3:40:06 AM7/13/04
to
I've tried a few approaches. The 'batch file just overwrite the workstation
copy every time' approach. Seems a bit expensive when it might only be
needed 1 time in a hundred (plus I couldn't get Access to open maximized and
couldn't get the command window to go away).

Also opening the workstation front end, checking the version compared to
server update front end, closing if different and running update mdb. I
don't really like the idea of using an mdb to basically do a file copy,
seems a bit expensive. And files having to close themselves to overwrite
themselves etc.

So came up with this VB 'app'

This is a VB app which updates a client version of an Access front end, if
there is a different version, probably on a server.

It takes 5 command line arguments, the 5th being optional, each preceded by
a /
There's no need to delimit file names containing spaces with speech marks

C:\MIKE\Development\FEupdater\UpdateClient.exe - call the updater itself
/mmdatabase.mdb - the client FE to be updated
/\\server\mmdatabase\update\mmdatabase.mdb - the update version to use
/\\server\MMDatabase\WorkGroup\Secured.mdw - the workgroup file
/mmdatabase.ini - a simple text file that store the last username used
/C:\Microsoft Office 2000\Office\msaccess.exe - the path to the Access
executable

The last argument is optional, probably only of any use where there is more
than one version of Access on the machine, otherwise the default version of
Access is used.

UpdateClient.exe must be in the same directory as the file to be updated, as
must the ini file.

UpdateClient expects to find a table called mmSysDbProperties with fields
called PropID, PropName, PropValue, Comments. This is a local table, NOT a
linked table, that needs to be in both versions of the client. There needs
to be a record with a value in the PropName of 'Version' and a corresponding
value, the actual version number, in PropValue. This is what gets compared.
All that is checked is a difference. Not a later version!

The only form in the app mimics the look of an Access login, hence the use
of the ini file to store the last logged on user.

Create a desktop shortcut to this file, with the appropriate arguments, not
to the actual Access file.

Private Sub cmdCancel_Click()
'TVCodeTools ErrorEnablerStart
On Error GoTo PROC_ERR
'TVCodeTools ErrorEnablerEnd
Unload Me
'TVCodeTools ErrorHandlerStart
PROC_EXIT:
Exit Sub
PROC_ERR:
MsgBox Err.Description
Resume PROC_EXIT
'TVCodeTools ErrorHandlerEnd
End Sub


Private Sub cmdOK_Click()
'TVCodeTools ErrorEnablerStart
On Error GoTo PROC_ERR
'TVCodeTools ErrorEnablerEnd
Dim dbWorkStationCopy As Database
Dim dbServerCopy As Database
Dim FullFrontEndPath As String
Dim rstFrontEndVersion As Recordset
Dim rstUpdateVersion As Recordset
Dim WorkStationVersion As String
Dim ServerVersion As String
Dim wrk As Workspace
Dim arrArgs As Variant
Dim fso As New FileSystemObject
Dim fil As File
Dim ts As TextStream
Dim strAccessPath As String
arrArgs = GetCommandLine(5)
FullFrontEndPath = CurDir & "\" & arrArgs(1)
DBEngine.SystemDB = arrArgs(3)
Set wrk = DBEngine.CreateWorkspace("wrkTemp", Me.txtUserName,
Me.txtPassword)
Set dbWorkStationCopy = DBEngine(0).OpenDatabase(FullFrontEndPath)
Set rstFrontEndVersion =
dbWorkStationCopy.OpenRecordset("mmSysDbProperties", dbOpenSnapshot)
rstFrontEndVersion.FindFirst "PropName = 'Version'"
WorkStationVersion = rstFrontEndVersion.Fields("PropValue").Value
rstFrontEndVersion.Close
dbWorkStationCopy.Close
Set dbServerCopy = DBEngine(0).OpenDatabase(arrArgs(2))
Set rstUpdateVersion = dbServerCopy.OpenRecordset("mmSysDbProperties",
dbOpenSnapshot)
rstUpdateVersion.FindFirst "PropName = 'Version'"
ServerVersion = rstUpdateVersion.Fields("PropValue").Value
rstUpdateVersion.Close
dbServerCopy.Close
DBEngine(0).Close
Set fil = fso.GetFile(arrArgs(4))
Set ts = fil.OpenAsTextStream(ForWriting, TristateUseDefault)
ts.Write (Me.txtUserName)
ts.Close
If WorkStationVersion <> ServerVersion Then
fso.CopyFile arrArgs(2), FullFrontEndPath, True
End If
If UBound(arrArgs) < 5 Then
strAccessPath = SysCmd(acSysCmdAccessDir) & "msaccess.exe"
Else
strAccessPath = arrArgs(5)
End If
Shell strAccessPath & " " & FullFrontEndPath & " /wrkgrp " & arrArgs(3) &
_
"/user " & txtUserName & "/pwd " & txtPassword, vbMaximizedFocus
wrk.Close
Unload Me
'TVCodeTools ErrorHandlerStart
PROC_EXIT:
Exit Sub
Unload Me
PROC_ERR:
MsgBox Err.Description
Resume PROC_EXIT
'TVCodeTools ErrorHandlerEnd
End Sub

Private Sub Form_Load()
'TVCodeTools ErrorEnablerStart
On Error GoTo PROC_ERR
'TVCodeTools ErrorEnablerEnd
Dim fso As New FileSystemObject
Dim fil As File
Dim ts As TextStream
Dim arrArgs As Variant
arrArgs = GetCommandLine(5)
Set fil = fso.GetFile(arrArgs(4))
Set ts = fil.OpenAsTextStream(ForReading, TristateUseDefault)
Me.txtUserName = ts.ReadAll
Me.Show
Me.txtPassword.SetFocus
'TVCodeTools ErrorHandlerStart
PROC_EXIT:
Exit Sub
PROC_ERR:
MsgBox Err.Description
Resume PROC_EXIT
'TVCodeTools ErrorHandlerEnd
End Sub

Public Function GetCommandLine(Optional MaxArgs)
'Declare variables.
'TVCodeTools ErrorEnablerStart
On Error GoTo PROC_ERR
'TVCodeTools ErrorEnablerEnd
Dim C
Dim CmdLine
Dim CmdLnLen
Dim InArg
Dim I
Dim NumArgs
'See if MaxArgs was provided.
If IsMissing(MaxArgs) Then
MaxArgs = 10
End If
'Make array of the correct size.
ReDim ArgArray(MaxArgs)
NumArgs = 0
InArg = False
'Get command line arguments.
CmdLine = Command()
CmdLnLen = Len(CmdLine)
'Go thru command line one character
'at a time.
For I = 1 To CmdLnLen
C = Mid(CmdLine, I, 1)
'Test for space or tab.
If (C <> "/") Then
'Not a forward slash - used as argument delimiter
'Test if already in argument.
If Not InArg Then
'New argument begins.
'Test for too many arguments.
If NumArgs = MaxArgs Then
Exit For
End If
NumArgs = NumArgs + 1
InArg = True
End If
'Concatenate character to current argument.
ArgArray(NumArgs) = ArgArray(NumArgs) & C
Else
'Found a forward slash.
'Set InArg flag to False.
InArg = False
End If
Next I
'Resize array just enough to hold arguments.
ReDim Preserve ArgArray(NumArgs)
'Return Array in Function name.
GetCommandLine = ArgArray()
'TVCodeTools ErrorHandlerStart
PROC_EXIT:
Exit Function
PROC_ERR:
MsgBox Err.Description
Resume PROC_EXIT
'TVCodeTools ErrorHandlerEnd
End Function

Mike


Keith Wilby

unread,
Jul 13, 2004, 9:06:45 AM7/13/04
to
"Mike MacSween" <mike.macsw...@btinternet.com> wrote:

> The 'batch file just overwrite the workstation
> copy every time' approach. Seems a bit expensive when it might only be
> needed 1 time in a hundred (plus I couldn't get Access to open
> maximized and couldn't get the command window to go away).

Expensive? I use this method and it works well. To force the command window
to close and maximise on open:

ECHO OFF
XCOPY "X:\MyPath\MyGUI.mdb" C:\MyLocalPath /I
START "MyGUI" /MAX "C:\Program Files\Microsoft Office\Office\MSACCESS.EXE"
"C:\MyLocalPath\MyGUI.mdb" /wrkgrp "X:\MyPath\MyApp.mdw" /user guest

Regards,
Keith.

Arno R

unread,
Jul 13, 2004, 9:47:54 AM7/13/04
to
Hi Mike,

Tony Toews has (since 2000) an Auto-update-utility available.
http://www.granite.ab.ca/access/autofe.htm
This very nice utility works painless on ALL existing versions of Access.
(He even added support for Access 2.0 recently)

This utility can also update more files if needed (helper-mdb's, batch-files, ico's etc)

Arno R


"Mike MacSween" <mike.macsw...@btinternet.com> schreef in bericht
news:40f391d7$0$58817$5a6a...@news.aaisp.net.uk...

Mike MacSween

unread,
Jul 13, 2004, 9:55:18 AM7/13/04
to
"Keith Wilby" <keith...@AwayWithYerCrap.com> wrote in message
news:Xns952590278D...@10.15.188.42...

Right, that's how you do it, thanks Keith.

Yes, I'm aware it's your preferred method. I wasn't having a go or anything
atall. And now that you've shown me how it's done I may well end up doing it
your way. It was still a good learning experience to knock the thing up in
VB anyway, as I don't have anywhere near enough experience in VB. Well, none
atall really!!

I guess the preferred method may depend on size of mdb file, speed of
network etc. And the advantage with the VB app is that it gives the
potential to do more sophisticated updates. Haven't thought what yet though!
Well, for instance, in development you may want to roll out updates only to
certain users who effectively act as beta testers. Or something. Anyway, you
get the point, there could be as-yet-unthought-of-things you could do with
the vb approach.

Oh, and it does allow me to get the version of access I want, if there's a
non standard install. In fact I don't have to worry about the path to
access.exe atall.

Have you had any problems with your approach? 100 users all turning on their
machines at 9.01am and uploading the new version crashing the network or
anything like that?

Cheers, Mike


Mike MacSween

unread,
Jul 13, 2004, 9:58:11 AM7/13/04
to
Thanks. Actually I downloaded Tony's updater sometime ago and just couldn't
get it to work. Probably operator error.

It's been good for me to do this anyway. And now it's done I can add/extend
it as I like. Though it'll be interesting to hear Tony's comments on my
approach anyway.

Cheers, Mike

"Arno R" <arracomn_...@tiscali.nl> wrote in message
news:40f3e84d$0$62367$5fc...@dreader2.news.tiscali.nl...

Keith Wilby

unread,
Jul 13, 2004, 10:27:12 AM7/13/04
to
"Mike MacSween" <mike.macsw...@btinternet.com> wrote:

> Have you had any problems with your approach? 100 users all turning on
> their machines at 9.01am and uploading the new version crashing the
> network or anything like that?

Mike, I wasn't critisising your approach, I just didn't understand
"expensive" and also thought you'd like to know how to get rid of that
pesky black box :o)

I have about 30 concurrent users, maybe 100 all tolled, but there's no "big
bang" of users logging on simultaneously so no network problems as yet.

Best of luck with your method ...

Regards,
Keith.

Trevor Best

unread,
Jul 13, 2004, 1:36:35 PM7/13/04
to
I've had my own for some time (in VB), it takes only one command line
argument, that being a profile name and the profile is stored in a ini
file (originally 16 bit for Access 2.0, never bothered updating to use
reg keys as saw no benefit). Each of my applications has a profile
table, a simple 2 column key and value so in the profile for DbStart (as
it's called) there's a SQL string used to get the version number out of
the profile table.

The clever bit is that it renames the local copy, then copies the newer
version then copies over all but the "CodeVersion" profile thereby
keeping any user preferences. Oh, and the other clever bit, it knows
which version of Access created the mdb so starts the relevant version.
At one point I did have it as the registered app for loading mdbs from
exploder as I worked with multiple versions but doing that does break DDE.

It is tailored for use on applications I've designed, which is why I've
never released it to the general public.

Only problems I've had with this method is sometimes forgetting to check
out data & misc objects from StarTeam[1] before updating the version number.

[1] what I use instead of Visual SourceSafe.

--
Error reading sig - A)bort R)etry I)nfluence with large hammer

Tony Toews

unread,
Jul 13, 2004, 2:11:36 PM7/13/04
to
"Mike MacSween" <mike.macsw...@btinternet.com> wrote:

>Thanks. Actually I downloaded Tony's updater sometime ago and just couldn't
>get it to work. Probably operator error.

Nah, poor documentation on my part. I'm a developer not a tech writer.

>It's been good for me to do this anyway. And now it's done I can add/extend
>it as I like. Though it'll be interesting to hear Tony's comments on my
>approach anyway.

You are using a table with version numbers. Whereas I use the datetime of the FE on
the server and store that datetime on the client. Otherwise very similar in
concept.

FWIW I've been doing a lot of updates recently and am very close to putting a GUI on
mine along with a wizard so as to make it much easier to use.

One update I just put in was that it no longer requires to you to specify the path to
your msaccess.exe and yet you can still pass parameters. But there's a bug in that
logic so it'll be a few more days before that's fixed.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

Tony Toews

unread,
Jul 13, 2004, 4:08:48 PM7/13/04
to
Keith Wilby <keith...@AwayWithYerCrap.com> wrote:

>Expensive? I use this method and it works well. To force the command window
>to close and maximise on open:
>
>ECHO OFF
>XCOPY "X:\MyPath\MyGUI.mdb" C:\MyLocalPath /I
>START "MyGUI" /MAX "C:\Program Files\Microsoft Office\Office\MSACCESS.EXE"
>"C:\MyLocalPath\MyGUI.mdb" /wrkgrp "X:\MyPath\MyApp.mdw" /user guest

Interesting. I've seen your postings before but never paid too much attention. I've
never seen that START command before. Very Interesting.

David W. Fenton

unread,
Jul 13, 2004, 8:00:37 PM7/13/04
to
Tony Toews <tto...@telusplanet.net> wrote in
news:10g8f0df8abftcauk...@4ax.com:

> Keith Wilby <keith...@AwayWithYerCrap.com> wrote:
>
>>Expensive? I use this method and it works well. To force the
>>command window to close and maximise on open:
>>
>>ECHO OFF
>>XCOPY "X:\MyPath\MyGUI.mdb" C:\MyLocalPath /I
>>START "MyGUI" /MAX "C:\Program Files\Microsoft
>>Office\Office\MSACCESS.EXE" "C:\MyLocalPath\MyGUI.mdb" /wrkgrp
>>"X:\MyPath\MyApp.mdw" /user guest
>
> Interesting. I've seen your postings before but never paid too
> much attention. I've never seen that START command before. Very
> Interesting.

It is not reliably usable. The START command will not run on my
Win2K workstation, for instance. I've never been able to figure out
why.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

Mike MacSween

unread,
Jul 14, 2004, 5:56:45 AM7/14/04
to
"David W. Fenton" <dXXXf...@bway.net.invalid> wrote in message

> >>ECHO OFF
> >>XCOPY "X:\MyPath\MyGUI.mdb" C:\MyLocalPath /I
> >>START "MyGUI" /MAX "C:\Program Files\Microsoft
> >>Office\Office\MSACCESS.EXE" "C:\MyLocalPath\MyGUI.mdb" /wrkgrp
> >>"X:\MyPath\MyApp.mdw" /user guest
> >
> > Interesting. I've seen your postings before but never paid too
> > much attention. I've never seen that START command before. Very
> > Interesting.
>
> It is not reliably usable. The START command will not run on my
> Win2K workstation, for instance. I've never been able to figure out
> why.

Really? That makes it a non-starter on this clients network then.

Cheers, Mike


Keith Wilby

unread,
Jul 15, 2004, 4:23:25 AM7/15/04
to
"David W. Fenton" <dXXXf...@bway.net.invalid> wrote:

> It is not reliably usable. The START command will not run on my
> Win2K workstation, for instance. I've never been able to figure out
> why.

Sorry folks, should have mentioned we're on a lowly NT4/Office 97 setup
here. I just assumend it would work on other platforms ...

David W. Fenton

unread,
Jul 15, 2004, 2:53:49 PM7/15/04
to
Keith Wilby <keith...@AwayWithYerCrap.com> wrote in
news:Xns9527601C4F...@10.15.188.42:

It does work on Win2K in most configurations. It doesn't work on
mine.

But I run in a non-standard configuration that has many default
services turned off and security substantially upped in comparison
to the defaults.

But I can't see why that would break START.

Reply all
Reply to author
Forward
0 new messages