Auto-update an Access front-end.. 1

[thatguy]

Hey there folks--

I have a split database (Access 2002) with the tables on a network drive and linked to from the frontend. Obviously, for the speed aspect, I would like to run the front-end from the user's local machine.

I have a table in the front-end to store the version. Theoretically, I would like to compare the local version number with the network version number, and, if the local is older than the network, copy the network version down and run it. Presently, I have two databases, one is the actual front-end and the other consists only of a module to do the comparison/copy. Past that, I have a batch file running the CheckVersion and then the front-end.

My question: how can I do this more efficiently? The batch file window won't close until after the front-end has completed, and I've never really liked the idea of using a batch file in Windows anyway... Does anyone have any suggestions? Thanks!

-- michael~

 

[Kotaro24]

You could use the ShellExecute API call to avoid the batch file, but it's really no more efficient.

1) Old_App Startup sees a new version, copies it to local PC with a different name.
2) Old_App Uses Shell_Execute to run New_App, then immediately shuts down.
3) New_App looks at its own name, and if not the same as Old_App's Name copies itself to Old_App's Name.
4) New_App Uses Shell_Execute to run New_App, then immediately shuts down.
5) New_App which has been renamed to Old_App's name sees New_App in the same folder and deletes it.

'Module
Private Declare Function apiShellExecute Lib "shell32.dll" _
Alias "ShellExecuteA" _
(ByVal hWnd As Long, _
ByVal lpOperation As String, _
ByVal lpFile As String, _
ByVal lpParameters As String, _
ByVal lpDirectory As String, _
ByVal nShowCmd As Long) _
As Long

'***App Window Constants***
Public Const WIN_NORMAL = 1 'Open Normal
Public Const WIN_MAX = 3 'Open Maximized
Public Const WIN_MIN = 2 'Open Minimized

'***Error Codes***
Private Const ERROR_SUCCESS = 32&
Private Const ERROR_NO_ASSOC = 31&
Private Const ERROR_OUT_OF_MEM = 0&
Private Const ERROR_FILE_NOT_FOUND = 2&
Private Const ERROR_PATH_NOT_FOUND = 3&
Private Const ERROR_BAD_FORMAT = 11&


'See Useage examples at the top of this module
Public Function ShellExecute(stFile As String, lShowHow As Long)
Dim lRet As Long, varTaskID As Variant
Dim stRet As String
'First try ShellExecute
lRet = apiShellExecute(hWndAccessApp, vbNullString, _
stFile, vbNullString, vbNullString, lShowHow)

If lRet > ERROR_SUCCESS Then
stRet = vbNullString
lRet = -1
Else
Select Case lRet
Case ERROR_NO_ASSOC:
'Try the OpenWith dialog
varTaskID = Shell("rundll32.exe shell32.dll,OpenAs_RunDLL " _
& stFile, WIN_NORMAL)
lRet = (varTaskID <> 0)
Case ERROR_OUT_OF_MEM:
stRet = &quot;Error: Out of Memory/Resources. Couldn't Execute!&quot;
Case ERROR_FILE_NOT_FOUND:
stRet = &quot;Error: File not found. Couldn't Execute!&quot;
Case ERROR_PATH_NOT_FOUND:
stRet = &quot;Error: Path not found. Couldn't Execute!&quot;
Case ERROR_BAD_FORMAT:
stRet = &quot;Error: Bad File Format. Couldn't Execute!&quot;
Case Else:
End Select
End If
ShellExecute = lRet & _
IIf(stRet = &quot;&quot;, vbNullString, &quot;, &quot; & stRet)
End Function

 

[thatguy]

Hey there..
Sorry `bout the delay in response, but I got sidetracked.. anyway, my thinking is this: have a database consisting of a module with the ShellExecute and CheckVersion functions. When the db is opened, an Autoexec macro runs the CheckVersion func. It seems to work alright if I Stop and step thru CheckVersion, but if I actually try to run it with no Stops, Access locks up (CPU Utilization goes to 100% and I have to end task). Here's the CheckVersion function, am I missing anything?

Public Function CheckVersion()
Dim wrkMain As Workspace
Dim dbs As DAO.Database
Dim rstNet As DAO.Recordset, rstLocal As DAO.Recordset
Dim cNetV As String, cLocalV As String
Dim lcretval As String

Set wrkMain = CreateWorkspace(&quot;&quot;, &quot;admin&quot;, &quot;&quot;, dbUseJet)
Set dbs = wrkMain.OpenDatabase(&quot;Q:\FrontEnd\QuoteSys&quot

Set rstNet = dbs.OpenRecordset(&quot;SELECT * FROM Version&quot
cNetV = rstNet!Version
Set rstLocal = CurrentDb.OpenRecordset(&quot;SELECT * FROM Version&quot;, dbOpenDynaset)
cLocalV = rstLocal!Version

rstNet.Close
dbs.Close
wrkMain.Close
Set rstNet = Nothing
Set dbs = Nothing
Set wrkMain = Nothing

If Not (cNetV = cLocalV) Then
DoCmd.SetWarnings False
FileCopy &quot;Q:\FrontEnd\QuoteSys.mdb&quot;, &quot;C:\QuoteSys\QuoteSys.mdb&quot;
DoCmd.SetWarnings True
rstLocal.edit
rstLocal!Version = cNetV
rstLocal.Update
End If
rstLocal.Close
Set rstLocal = Nothing

lcretval = ShellExecute(&quot;C:\QuoteSys\QuoteSys.mdb&quot;, WIN_MAX)
If Not InStr(lcretval, &quot;-1&quot Then
MsgBox lcretval
End If

DoCmd.Quit
End Function


Thanks
-- michael~