possible to combine Ken Higg's FE version control WITH DCrake's shell extension code?

[wiklendt]

Hi,

i'm trying to combine the versatility of DCrake's shell extension code (which opens any extension with the native app via shell) with the functionality of Ken Higg's front end loader/version control (which checks and manages version difference of master FE and client FE) but my combinations
'freeze' access.

i can get Ken's loader to work on its own, but don't want to have to manually change the absolute path to the ms access installation depending on the local system and local access version

my split DB is currently distributed over a network share drive (Back End) with local Front End's on users systems -currently limited to 4 machines until i get this FE loader working and i manually update their FE's locally (which isn't so bad, 5 min walk, but automatic would be better)

so i thought DCrake's shell extension code would be a great tweak in Ken's FE loader.

HOWEVER. i can't get the two to dance without stepping in each other's feet. When i add DCrake's code to Ken's module handling the version control, it 'freezes' access and seemingly does nothing (no errors).

Same happens when i put DCrake's code in a form and open the form from the module using "DoCmd.OpenForm"

i feel like i'm way out of my league with trying to modify DCrake's code to work in a module. i once thought i made it work - in fact it seemed to me to work but inexplicably just once and never again.

this is the "just once" code that worked... does anyone have any pointers? (also i can't get the "token" to pass, but actually don't really want/need that). i have hi-lighted in red DCrake's code within Ken's module here (i've broken it up into functions/subs, but it's all one continuous module):

(edit: i've attached this module at the bottom of the post as an exported .bas file, zipped - in case it helps)

header info:

Option Compare Database
'====================================================================================================
' Name: basVersionControl
' Use: Insures user has latest version on their local machine
' Created By: Ken Higginbotham
' Date: 11/14/2007
' Modified By: Agnieszka Wiklendt using Shell Extension code from DCrake
' Date: 11/20/2009
' Called By:
' Calls:
' Update: 11/20/2009, Shell Exntension; by AW
' Notes: Code will now open the front end using the native installed application called by the
'        file extension, rather than relying on the exact installed location of MS Access.
'        This is to allow users to install or update their Access programs without having to change
'        the file path in this code. Also allows users of varying Access Versions to use one loader.
'        Unfortunately, this not longer supports the FE "token" feature.
' Update: 11/15/2007, Tweak; by KH
' Notes: The only requirement for this to run properly is for the FE to have a database variable
'        named whatever is defined in the constant cstrDBVersionPropertyName. There is a
'        complimentary form with code named frmVersionControl which can be imported into the FE that
'        will manage this requirement.
'====================================================================================================
Option Explicit

'Configure these==========================
'Front end file name
Public Const cstrFEFile = "ORDERS_ReferenceLabs_fe.mdb"

'Location of master FE file on server
'Const cstrMasterFEPath = "\\wm-icpmr\Data2\SHARED\Cidmls\Molecular Biology\Orders\"

'Location of master FE file in development environment
Public Const cstrMasterFEPath = "C:\Users\Agnieszka\Documents\EDU + WORK\3. Westmead\2006-8 Molecular\Databases\Ordering DB (Terry)\"

'Location of FE on local machines (including dev. environ.)
Public Const cstrClientFEPath = "C:\Program Files\RLSODB\"
'=====================================

'Name of databse version variable
Public Const cstrDBVersionPropertyName = "DatabaseVersion"

'Shell Execute stuff
[COLOR=Red]Public Declare Function ShellExecute Lib "shell32.dll" _
   Alias "ShellExecuteA" _
   (ByVal hWnd As Long, ByVal lpszOp As String, _
    ByVal lpszFile As String, ByVal lpszParams As String, _
    ByVal LpszDir As String, ByVal FsShowCmd As Long) _
    As Long
Public Declare Function GetDesktopWindow Lib "User32" () As Long
Const SW_SHOWNORMAL = 1
[/COLOR]

Function fncVersionControlStartup()

Function fncVersionControlStartup()
'====================================================================================================
' Name: fncVersionControlStartup
' Use: Checks if FE is loaded on local machine and checks to make sure it is the latest version
' Created By: Ken Higginbotham
' Modified By:
' Date: 11/14/2007
' Called By: autoexec macro
' Calls: fncGetVersion, subCopyMasterOver
' Update:
'====================================================================================================
    On Error GoTo Err_fncVersionControlStartup

    'This is the the token value passed from the loader to the FE to make sure you can't open the FE without using the loader
    Dim strFEToken As String
    strFEToken = "MyTokenValue"
        
    'Section 1: FE Version Stuff
    '------------------------------------------------------------------------------------------------
    Dim objFileSystem As Object                                     'Initiates FileSystem Object
    Set objFileSystem = CreateObject("Scripting.FileSystemObject")  'Initiates FileSystem Object
        
    'Check to see if FE already exists on client
    If Not objFileSystem.FileExists(cstrClientFEPath & cstrFEFile) Then
        'If it does not exist then it calls the function that copies it over
        subCopyMasterOver
    Else
        'If it does exist then check the version
        If fncGetVersion(cstrClientFEPath & cstrFEFile) <> fncGetVersion(cstrMasterFEPath & cstrFEFile) Then
            subCopyMasterOver
        End If
    End If
    
    Set objFileSystem = Nothing 'Kill the FileSystem Object
   
   'Section 2: Open FE and Closed this Utility Loader Appliction
    '------------------------------------------------------------------------------------------------
    
[COLOR=Red]    Dim nDT, nApp
    Dim strFile As String
    
    strFEToken = "/cmd " & strFEToken
    strFile = cstrClientFEPath & cstrFEFile
    nDT = GetDesktopWindow()
    nApp = ShellExecute(nDT, "Open", strFile, "", "C:\", SW_SHOWNORMAL)
    DoEvents
[/COLOR]    
    DoCmd.Quit

Exit_fncVersionControlStartup:
    Exit Function

Err_fncVersionControlStartup:
    If Err.Number = 53 Then
        MsgBox "MS Access application file not found. Please contact administrator.", vbCritical, "System Error..."
    Else
        MsgBox "Please contact administrator." & vbCrLf & Err.Description & vbCrLf & "Code: " & Err.Number & vbCrLf & "Source: fncVersionControlStartup", vbCritical, "System Error..."
    End If
    
    DoCmd.Quit
    
End Function

and if it's required?... Function fncGetVersion

Function fncGetVersion(fstrFile As String) As String
'====================================================================================================
' Name: fncGetVersion
' Use: Returns the version number of the passed filename
' Created By: Ken Higginbotham
' Modified By:
' Date: 11/14/2007
' Called By: fncVersionControlStartup
' Calls:
' Update:
'====================================================================================================
    On Error GoTo Err_fncGetVersion
    
    Dim DB As Database                      'Connection var
    Set DB = OpenDatabase(fstrFile)     'Set db to passed filename/path
    
    'Return version to function
    fncGetVersion = DB.Properties(cstrDBVersionPropertyName)
    
    'Close db
    DB.Close
    
    'Kill object
    Set DB = Nothing

Exit_fncGetVersion:
    Exit Function

Err_fncGetVersion:
    If Err.Number = 3044 Then
        MsgBox "Master FE file could not be found. Please contatct administrator.", vbCritical, "System Error..."
    ElseIf Err.Number = 3270 Then
        MsgBox "Database version number could not be found in master or client FE. Please contact administrator.", vbCritical, "System Error..."
    Else
        MsgBox "Please contact administrator." & vbCrLf & Err.Description & vbCrLf & "Code: " & Err.Number & vbCrLf & "Source: fncGetVersion", vbCritical, "System Error..."
    End If
    
    DoCmd.Quit

End Function

the sub which copies updated FE:

Sub subCopyMasterOver()
'====================================================================================================
' Name: subCopyMasterOver
' Use: Copies FE to client
' Created By: Ken Higginbotham
' Modified By:
' Date: 11/14/2007
' Called By: fncVersionControlStartup
' Calls:
' Update:
'====================================================================================================
    On Error GoTo Err_subCopyMasterOver
    
    Dim objFileSystem As Object     'Filesystem object
    
    'Set Filesystem object
    Set objFileSystem = CreateObject("Scripting.FileSystemObject")
        
    'Copy FE file over
    objFileSystem.CopyFile cstrMasterFEPath & cstrFEFile, cstrClientFEPath & cstrFEFile
        
    'Kill Object
    Set objFileSystem = Nothing
        
Exit_subCopyMasterOver:
    Exit Sub

Err_subCopyMasterOver:
    If Err.Number = 76 Then
        MsgBox "Local directory not found. Please contact administrator.", vbCritical, "System Error..."
    Else
        MsgBox "Please contact administrator." & vbCrLf & Err.Description & vbCrLf & "Code: " & Err.Number & vbCrLf & "Source: fncVersionControlStartup", vbCritical, "System Error..."
    End If

    DoCmd.Quit
    
End Sub

a macro called "autoexec" runs the following: fncVersionControlStartup () to get everything started.

i have looked into many options for FE autoupdating and like Ken's best. the only thing missing is the ability to open the client FE with the local installation of access on users machines.

 

[wiklendt]

ok, here's the really weird thing.

when i open this 'loader' (.mdb) file, autoexec executes the function fncVersionControlStartup (), but hangs.

BUT when shift-open the database so the macro doesn't automatically load, right-click on the autoexec macro, then select "run!" from the context menu, the code works fine (i think this is where i may have thought it worked once only, in fact i may have just run the code from having shift-opened it)

why would shift-open right-click run make it work and not opening the mdb file normally?

(oh, the only thing is it tells me "the command or action Quit is not available now; error 2046", highlights the DoCmd.Quit just under DCrake's code (edit: it worked under Ken Higg's original code).... i've looked into it but no joy?)

 

[boblarson]

(oh, the only thing is it tells me "the command or action Quit is not available now; error 2046".... i've looked into it but no joy?)

The only thing I can think of is that you can't do certain things while code is processing. And that is why I, in my Frontend Auto Update Enabler, went the dynamic build of a batch file to kick off so that the deletion, copying, etc. was happening outside of the Access world.

 

[KenHigg]

Did you ever find a solution to this Wiklendt?

 

[wiklendt]

Did you ever find a solution to this Wiklendt?

not yet. i'm currently working on it (commuiting on the train gives me a few hours a day to mull over things like this).

i've randomly decided that it is DCrake's code that just won't work (in this situation) for some reason, so now i'm attempting to combine your version code with bob's FE management (edit: bob's utility was the only other simple (IMHO) management tool that i could find - i found lots of proprietry programs but i want full control of what code goes into my systems).

that is to say, bob uses tables in the FE and BE that BOTH need to be changed for his code to work/run... and he uses a .cmd text/bat(?) file to copy the FE version then open it using what appears to me to be a shell extension similar to DCrake's in that it doesn't require the full absolute path of the Access program.

i still prefer your method of using the database properties and one form (as opposed to three tables in bob's example), so i want to combine the use of both in that sense.

at the moment i'm just trying to get my head around bob's utility and get that to work in a stand-alone fashion in my DB setup, then i'll tackle integrating it (reverse engineering??) into your FE check code.

it's a big task (for me), but i'm determined and i'm sure i'll learn a lot

i'll DEFINITELY post back with success/progress/failure

(edit: bob's /cmd file DOES hardcode full paths to the FE/BE, but that will be ok in my situation b/c i put the FE client into a subfolder of the program files directory of their machines, so every user has their FE in "C:\Program Files\RLSODB", and obviously the server FE will always be just the one location, so i'll use UNC path and that will be ok too, as for developmental work, i need to use the shift key these days anyhow, so no problem there with accidentally running server code when i don't have access to the server).

 

[KenHigg]

So you need it to find the access .exe on it's on and then create a standard location for the .mdbs?

 

[wiklendt]

So you need it to find the access .exe on it's [own] and then create a standard location for the .mdbs?

yes...? not necessarily to create a standard location for .mdbs, but to open an mdbs without requiring full path to the access app (b/c it is different for different versions, and may change again in future). i like having the mdbs in a standard location (e.g., "C:\Program Files\DBfolder") b/c it then just makes it easier for everybody

and actually, i just realised that bob's .cmd isn't hardcoded for db locations, it probably takes the values from the extra tables before it's generated... but i've only just started looking into bob's system, so i'm not yet all familiar with it - and now i'm at work i won't get to tinker again for another several hours...

 

[boblarson]

and actually, i just realised that bob's .cmd isn't hardcoded for db locations, it probably takes the values from the extra tables before it's generated...

No, it takes it from the currentproject.Path.

 

[wiklendt]

No, it takes it from the currentproject.Path.

i obviously have a need to familiarise myself with the code your are using to see how i might customise it for my own system.

i might totally flop, but i think it's worth looking into

 

[boblarson]

The main part is really just the part that builds the dynamic command batch file:

Public Sub UpdateFrontEnd()
Dim strCmdBatch As String
Dim notNotebook As Object
Dim FSys As Object
Dim TestFile As String
Dim strKillFile As String
Dim strReplFile As String
Dim strRestart As String

' sets the file name and location for the file to delete
strKillFile = g_strFilePath
' sets the file name and location for the file to copy
strReplFile = g_strCopyLocation & "\" & CurrentProject.Name
' sets the file name of the batch file to create
TestFile = CurrentProject.Path & "\UpdateDbFE.cmd"
' sets the restart file name
strRestart = """" & strKillFile & """"
' creates the batch file
Open TestFile For Output As #1
Print #1, "Echo Off"
Print #1, "ECHO Deleting old file"
Print #1, ""
Print #1, "ping 1.1.1.1 -n 1 -w 2000"
Print #1, ""
Print #1, "Del """ & strKillFile & """"
Print #1, ""
Print #1, "ECHO Copying new file"
Print #1, "Copy /Y """ & strReplFile & """ """ & strKillFile & """"
Print #1, ""
Print #1, "CLICK ANY KEY TO RESTART THE ACCESS PROGRAM"
Print #1, "START /I " & """MSAccess.exe"" " & strRestart
Close #1
'Exit Sub
' runs the batch file
Shell TestFile

'closes the current version and runs the batch file
DoCmd.Quit



End Sub

The rest is just checking to see if the versions have changed.

I also have code in the load event of the form to clean up the batch file if it exists, so it checks in the database location and if it is there it deletes it so that there isn't a rogue batch file remaining.

' checks for the existence of an updating batch file and deletes it if it exists
    strFilePath = CurrentProject.Path & "\UpdateDbFE.cmd"
    
    If Dir(strFilePath) <> "" Then
        Dim fs As Object
        Set fs = CreateObject("Scripting.FileSystemObject")
        fs.DeleteFile (strFilePath)
        Set fs = Nothing
    End If

 

[wiklendt]

yes, i was thinking those bits would be the main bits i'd use (i'd figured they'd be the important bits for the copy/open events, BUT i wanted to be sure i understood how the whole thing worked so i wasn't going to mess things up)

the bits that i meant that i was getting my head around was the GUI form - it is unclear which of the "FE master" paths are for the FE client and which for the FE server/master... my first attempt this morning on the train got them the wrong way around and i ended up copying the client over the master (it's ok, i made backups )

for the version checking i was going to use ken's code b/c it uses difference between the database properties of the master FE and client FE (and i THINK ken's code also copies the master FE to the client location if the client doesn't have an existing FE on their machine? i'd have to have another look), and not of version number differences in tables fields between master FE and BE.

but as i said, i'll have a good shot at implementing your bits later tonight on the train trip home hopefully it will be easy(! famous last words... LOL)

 

[wiklendt]

i know i haven't got clarity on this yet, but ken asked and i didn't want it to look like i was ignoring him - so i posted my thought process and what progress i'd made so far. (edit: while i've been working on this for a while, i only just picked up bob's stuff to implement into mine this morning) i will post everything in detail (and if i get stuck) once i get it working

thanks bob for your keenness to help i'm glad you're not offended that i'm nicking bits from your FE updater!

 

[boblarson]

Whatever works for your situation. Some of what you've said has me thinking of making some improvements in my tool. I wrote this about 4 years ago while I was at Providence Health System. They needed a quick and easy way to implement an auto update feature so the main database person didn't have to make sure that each user got a copy.

Of course there are other ways to do it.

1. A batch file on the user's machine which they use to copy down a new copy of the FE everytime they open the database.

2. In my current work position we use a thing called Desktop Authority which has some scripts set up and each user gets a new copy of each of our main databases whenever they log into their machine.

and I'm sure there's several other ways as well. The new 2010 feature allows you to go to the web to select the Access frontend and it downloads to the user's desktop so they can use it. The developer, can upload a copy and, regardless of whether there are any web based items, the database "round trips" all code and objects so that a user can always have the latest published version.

 

[boblarson]

i'm glad you're not offended that i'm nicking bits from your FE updater!

In case you hadn't seen this which is on the main form for my enabling tool:

free to anyone to use and/or modify for their use

 

[wiklendt]

Whatever works for your situation. Some of what you've said has me thinking of making some improvements in my tool. I wrote this about 4 years ago while I was at Providence Health System. They needed a quick and easy way to implement an auto update feature so the main database person didn't have to make sure that each user got a copy.

Of course there are other ways to do it.

1. A batch file on the user's machine which they use to copy down a new copy of the FE everytime they open the database.

2. In my current work position we use a thing called Desktop Authority which has some scripts set up and each user gets a new copy of each of our main databases whenever they log into their machine.

and I'm sure there's several other ways as well. The new 2010 feature allows you to go to the web to select the Access frontend and it downloads to the user's desktop so they can use it. The developer, can upload a copy and, regardless of whether there are any web based items, the database "round trips" all code and objects so that a user can always have the latest published version.

i was thinking about batch files too. i wasn't sure how to run them from access but your .cmd stuff might be transferable. however, i DO like the .cmd file and would like to include it in my system, so i'll stick with it (not to mention you've done all the hard work already! ;P ).

as for your 4-y.o. utility - we can't expect you to keep updating the util to meet random people's desires ;P , we should all try to contribute and grow together it's a fab little tool, bob, despite my reasons for not using it as-is.

obviously the windows environment changes over time, and i expect also a lot more people will be using microsoft products on non-windows OS's and i'm sure SOMEONE will want to adapt this stuff even more.

but like you say, whatever works!

 

[wiklendt]

In case you hadn't seen this which is on the main form for my enabling tool:

actually, i HAD missed that bit. thank you for elaborating.

i feel the same about whatever solution i come up with - i will be posting it here for all to see

 

[KenHigg]

Seeing has how Bob's taken over and seems to be going a good job I think I'll back out of the thread Wik. PM me if you need help with the sample stuff I posted

 

[wiklendt]

Seeing has how Bob's taken over and seems to be going a good job I think I'll back out of the thread Wik. PM me if you need help with the sample stuff I posted

LOL, i wouldn't say he's taken over but sure, thanks.

won't happen overnight, but it will happen! ;P

 

[wiklendt]

i can't believe it. i think i've done it

using the "SysCmd(acSysCmdAccessDir)" argument from:
http://www.access.qbuilt.com/html/update_front_end.html
(edit: which natively serves to return the directory of the access installation!)

i managed to keep almost all of ken's original code.

the only bit i had to change was to remove the Constant for full path to access and replace with:

    Dim cstrMSAccessAPP As String   'Folder path of Access Executable
    
    cstrMSAccessAPP = SysCmd(acSysCmdAccessDir) & "MSAccess.exe"

immediately before the string is used. seemed to fix it. also means we can keep the token so the FE client cannot be opened directly by the user

Access help states that acSysCmdAccessDir returns the path of the access executable there are other variables that return other nifty things, like access version number (acSysCmdAccessVer).

i just have to test it on a previous version of access! LOL and also to test if the setup works on our server

(edit: i knew one last google search was in order!)

 

[wiklendt]

ok, works sweet on our shared network (the local machines use office 2003 on XP; my machine is office 2007 on Vista) thanks guys for your help and interest. the only thing i needed to change was as described in my previous post.

if anyone need me to clarify more, let me know. thanks