Hi, I developed a database in Wind

[GPM4663]

Hi,
I developed a database in Windows/Access2000 and split it into front end/ back end. In the front end I added some code that would check (on start up)if there was a more up to date version on the network using the following code:

Function UpdateVersion()

Dim strPath2AccessEXE As String
Dim strRun, strRunText As String
Dim strSourceDB As String, strTargetDB As String, strUpdaterDB As String

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

strRun = SysCmd(acSysCmdAccessVer)
strPath2AccessEXE = strRun & "\MSACCESS.EXE"

strSourceDB = "\\UPUD\Data\UPU-Docs\Production_fe\Production.mdb"
strTargetDB = "C:\Documents and Settings\" & Environ("username&quot & "\Desktop\Production.mdb" 'my latest version on the network
strUpdaterDB = "\\UPUD\Data\UPU-Docs\Production_fe\Updater.mdb"

If FileDateTime(strTargetDB) < FileDateTime(strSourceDB) Then
strRunText = &quot;&quot;&quot;&quot; & strPath2AccessEXE & &quot;&quot;&quot; &quot;&quot;&quot; & strUpdaterDB
' Give the &quot;Shell&quot; command a window to operate

'Open a new instance of Access and pass it the command line variable.
Call Shell(strRunText, vbNormalFocus)

'Then close this instance of Access

Application.Quit
End If

End Function

As the code shows if there was a more up to date version it would close the current database and open another database called &quot;Updater&quot; that would actually do the copying. The &quot;Updater&quot; database would open a form on start up that would close after 10 seconds (to allow the Production database to fully close) and would run the following code on close:


Function UpdateInProgress()
Dim strSourceDB, strTargetDB, strDB1 As String
Dim strRun, strRunText, strPath2AccessEXE As String
Dim varResult As Variant
Dim oldName, newName As String

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

strSourceDB = &quot;\\UPUD\Data\UPU-Docs\Production_fe\Production.mdb&quot; 'my latest version on the network
strTargetDB = &quot;C:\Documents and Settings\&quot; & Environ(&quot;username&quot & &quot;\Desktop\Production.mdb&quot; 'Current version on User's Desktop
oldName = strTargetDB
newName = &quot;C:\Documents and Settings\&quot; & Environ(&quot;username&quot & &quot;\Desktop\Production.BAK&quot; 'Create Back up version

If FileDateTime(strTargetDB) < FileDateTime(strSourceDB) Then
'Rename of version
Name oldName As newName
DoEvents


'Copy newer Version
FileCopy strSourceDB, strTargetDB
DoEvents

'check if new version is there
If Len(Dir(strTargetDB)) > 0 Then
Kill newName 'if yes delete .bak file
Else
Name newName As oldName 'if no change .bak into .mdb again
End If
DoEvents

End If

'Delete any DB1 File
strDB1 = &quot;C:\Documents and Settings\&quot; & Environ(&quot;username&quot & &quot;\Desktop\DB1.MDB&quot;
On Error Resume Next
Kill strDB1

'open the local MDB file
strRun = SysCmd(acSysCmdAccessVer)
strPath2AccessEXE = strRun & &quot;\MSACCESS.EXE&quot;
strRunText = &quot;&quot;&quot;&quot; & strPath2AccessEXE & &quot;&quot;&quot; &quot;&quot;&quot; & strTargetDB
' Give the &quot;Shell&quot; command a window to operate

'Open a new instance of Access and pass it the command line variable.
Call Shell(strRunText, vbNormalFocus)

'Then close this instance of Access
Application.Quit

End Function

This allowed any user of the database to have updates automatically copied from the network to their desktop and it worked brilliantly until the computers were updated to Windows XP and Office XP! Now the updater database opens as if it is copying the new version but it doesn't actually do it. If I open the Updater database on its own the code works properly but if the original Production.mdb opens it it won't work! Am very confused and would love to get it solved so that I don't have to keep going round to everyones PC manually to update the version on their desktops. I'd really appreciate any help anybody could give me.

Many thanks in advance,

G.P.M.

 

[padinka]

Why are they copying the database to their desktops when you could just have them all open the server database?
Trisha
padinka@yahoo.com

 

[psemianonymous]

I think this was crossposted elsewhere, and he got a response.


In answer to your question, Access runs &quot;better&quot; if the front-end database is locally stored instead of out on a network drive also being run by 10 other users. Thus the auto-updating and such.


You can check the ten commandments at

http://www.mvps.org/access

- they cover pretty much all the &quot;good practices&quot; such as these. Of course they are more focused on the &quot;application&quot; side of Access whereas it seems that many other users are focused on &quot;quick fixes&quot;. --
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

 

[padinka]

I have been working with access since 2.0 beta. I don't agree that access runs 'better' locally. Faster maybe but each user running an individual copy places some strong programming limits on you. I read your 'ten commandments' and agree with them but I don't see where it states in them that each should run a local copy only that you shouldn't let them run on your production database which is a given.
Trisha
padinka@yahoo.com

 

[JeremyNYC]

Trisha,

It's FAR better to have user's running databases from their own PC rather than from the server. It's widely accepted among the Access developer community, and just about insisted upon by the sysAdmin community.

There are tons of reasons this is the case, but there are two that seal the deal. The first one is that if you're running it fro the server than every time the database is started, the entire database (even if it's just the front end) gets pulled across the wire, gumming things up much more than if the front end only comes across the wire when there's a new version out there. The second one is that it makes it MUCH easier to do updates to the front end--no one is hanging onto that file so you can replace it any time you want to.

And if you use the system I've laid out in the other thread in which this was posted (thread705-509887), or one of many other schemes people have for rolling out new versions in a controlled way, it's really quite an easy thing to do.

I don't know what you're referring to when you say &quot;each user running an individual copy places some strong programming limits on you&quot;.

Jeremy

PS to GPM: not a hugely big deal, but it's best to only post things on one forum so that people don't spend their time answering questions that have already been answered.

PPS: In this case it might not have made much of a difference as there's a difference of opinion here, which hopefuly would come out if this was only in one thread. ==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[GPM4663]

Guys,
Thanks for both your inputs I appreciate the view points.

Jeremy,
I honestly didn't mean to post this question twice. The first time (this one) my connection froze and it didn't look like it had been posted, in fact I didn't even realise this one had been posted until I check my emails. Thanks for your input though that you posted on the other one.

Cheers,

G.P.M.