Sharing FE Database with users on Network.

[Plukey]

Our AOR has a Database that was created by another colleague (Data Admin) that's no longer involved. It isn't split at the moment & its currently being used by 5-6 users that work for a subsidiary and only has access to our network folder where the Db lives. Its only 24,000 Kb it is used for auditing not for Data entry. Its constantly crashing and creating backup's because the 5 users are using the single shortcut... I advise them that ...what they're doing isn't recommended. My big mouth...I was asked to help. We have very strict rules here. I have to request to use .bat & .vbs scripts.
My plan is ...I'm going to split. and I need figure out the best way to share with the other users and limit the crashes and the lock up's... So, my question is...
1.Do I split & make a FE copy for each user?
2.Do I split & create a shortcut of the FE & share that?
3. Id love to use vbs script that uninstalls & installs and create shortcut. I

 

[Plukey]

Im using Bob's command as we speak... I've got two members of the group now using it. I didn't think of doing shortcut file location. One member just contacted me stating he received the msg, hit ok & it didn't re-open. and he got the following msg. His msg below..
View attachment 100229

Oh..FYI This is a test copy at the moment. I haven't done the actual split to the original yet.

 

[welshgasman]

So is that the DB?
Hiding all the path is not really helpful?

What do you think you would give away from a simple path?

If you are that paranoid, put it in C:\temp and post back then.

 

[welshgasman]

All I can suggest is locate CheckFE() and set a breakpoint and walk through the code.
This is my standard advice for first debugging.

 

[Plukey]

All I can suggest is locate CheckFE() and set a breakpoint and walk through the code.
This is my standard advice for first debugging.

Apologies... Its just been standard practice to hide. Not trying to get a call from our cyber security. But, I did figure it out. The user placed the shortcut in a different Drive letter. Moving forward, I'm just going to share a link. Thanks for your penitence, what yall are doing for us common folk is very appreciative. I really enjoy doing this stuff & I do put the effort in learning on my own.

 

[welshgasman]

I did make one change in the code as I found sometimes I got stuck in a loop.
Where it compares versions it uses <>, I changed that to <, and that seemed to stop any chance of a loop.

 

[Plukey]

I did make one change in the code as I found sometimes I got stuck in a loop.
Where it compares versions it uses <>, I changed that to <, and that seemed to stop any chance of a loop.

First... Thanks Welshgasman for the patience & the help! much appreciated!

I never got Bob Larsen's Auto Updater working correctly. It would pop up the message that there was an update & after clicking ok. It would either not re-open or open & close continuously over and over. I'm sure it was user error but, I ended up using a colleague's script's that they use to deploy their DB's on our network.

It consist of a Install.vbs, Uninstall.bat, & a Open.vbs, You'll need an Icon picture .ico if you want to change the Icon.ico -

I created the shortcut from the Open.vbs script and renamed it, then changed the Icon. That is what I used as my Shortcut.lnk in the Install Vbscript
It also gets copied to the users desktop. And when they click, It will run the Open.vbs , check for the latest update copy & replace. Then opens DB.
I had to change the Target: C:\Folder\Open.vbs & Start in: C:\Folder\ in order for it to be used as the user's desktop shortcut & check for the update's.
This is where I needed permission (McAfee whitelist) & also for the File folder that contained the scripts. This was my biggest challenge.

I started with the Open.vbs and moved on to the next till they all worked separately. Once all was complete I created a Hyperlink from the Install.vbs and sent to the users via email. The only issues I had as stated previously, were with permissions to use the vbscript & .bat files. It does seem like a lot, & I'm sure there's easier options. But, this is what worked for me. I've tested in the office over the Network & also working remote. My DB isn't that big so, I didn't have any issues
****INSTALL.VBS****

'1
'2   INSTALL SCRIPT
'3
'4
'5   This progam will install the  Database on a users PC
'6   It will make directories and copies the files necessary
'7   to run the  Database
'8   For Questions and Concerns contact Input NAME
'9
'10
     Dim inst_db         'variable for installing the database
     Dim inst_dt_lnk     'variable for installing the desktop shortcut
     Dim dba             'Database administrator for use in sending error message to
     Dim dba_email       'The email address for the administrator
'15
     dba = "Name"
     dba_email = "EMAIL"
'18
'19
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set objShellScript = CreateObject("WScript.Shell")
    With objShellScript
            StartMenu = .specialFolders("StartMenu")
            Desktop = .specialFolders("Desktop")
    End With
    Set objShellApp = CreateObject("Shell.Application")
    Set olApp = CreateObject("Outlook.Application")
    Set objMail = olApp.CreateItem(olMailItem)
'29
'30
     inst_db = MsgBox("This script will install the files necessary to run the" & vbNewLine & _
        "Database." & vbNewLine & vbNewLine & _
        "Would you like to continue?", _
         vbQuestion + vbYesNo + vbDefaultButton1, _
         "Database Install")
'36
     If inst_db = 6 Then     '6 = yes, 7 = No
'38    
         If fso.FolderExists("C:\FOLDER") Then
         ' Do Nothing the Folder Exists
         Else
             fso.CreateFolder ("C:\FOLDER")
         End If
'44
    If fso.FolderExists(StartMenu & "\Programs\FOLDER") Then
             ' Do Nothing the Folder Exists
         Else
             fso.CreateFolder (StartMenu & "\Programs\FOLDER")
         End If
'53 Copy the network Database Frontend Files to Your hard drive:
'54 The Database
         fso.CopyFile _
            "\\LOCATION\DATABASE.accde", _
            "C:\FOLDER\", True
'58
'59 The Shortcut  THIS IS WHERE I CREATED A SHORTCUT FROM THE OPEN.VBS & RENAMED IT
         fso.CopyFile _
               "\\LOCATION\Shortcut.lnk", _
            "C:\FOLDER\", True
'65 The VBS file that checks to see that you have the latest version
         fso.CopyFile _
               "\\LOCATION\" & _
               "Open.vbs", _
            "C:\FOLDER\", True
'70
'71 The uninstall File
         fso.CopyFile _
               "\\LOCATION\" & _
               "uninstall.bat", _
            "C:\Folder\", True
'82
'83 The Icon
       fso.CopyFile _
          "\\Location\Name of Icon.ico", _
           "C:\Folder\", True
'87
'88
'89 Copy the Shortcuts to your Start Menu:
         fso.CopyFile _
             "\\Location\" & _
                "Shortcut.lnk", _
            StartMenu & "\Programs\Folder\", True

'94 the Uninstal Files
         fso.CopyFile _
            "\\Location\" &  _
        "Uninstall.bat", _               
        StartMenu & "\Programs\Folder\", True
'99
'100 Asks the User if they would like a desktop shortcut:
        inst_dt_lnk = MsgBox("Would like to place a shorcut to the " & vbNewLine & _
                "Database Name" & vbNewLine & _
                "on the desktop?", _
            vbQuestion + vbYesNo + vbDefaultButton1, _
            "Database Name")
'106
         If inst_dt_lnk = 6 Then
                fso.CopyFile _
                "\\Location\" & _
                "Shortcut.lnk", _
                Desktop & "\", True
    Else
            'Do Nothing, the user does not want a desktop shortcut
    End If
     End If
'116
            MsgBox "The installation has been completed sucessfully.", _
            vbInformation + vbOKOnly, _
            "DFS_Log"
            objShellApp.ShellExecute "MSAccess", _
                "C:\Folder\Database", _
                "open"
'122

****Open Script***

'1        OPEN FILE SCRIPT 
'2
'3
'4   This file will compare the Last Modified Date of the
'5   ".accde" file on the server with the Last Modified Date
'6   of the ".accde" file on the users local machine.
'7   If the file on the server is newer the latest
'8   version of the ".accde" file will be copied to the
'9   users machine and the database will then be opened.
'10  
'11
'12
    Dim svr_db_pth
    Dim lcl_db_pth
    Dim accde_fname
    Dim Guide_Fname
    Dim DatabaseName_Open
'18
    Dim fso
    Dim f
    Dim g
'22
    Dim svr_f_mod_dt
'24
    Dim svr_g_mod_dt
'26
'27  Declare the following variable
'28  Database location on server
           svr_db_pth = "\\Location\"
'30
'31  Local file location
           lcl_db_pth = "C:\Folder"
'33
'34  (.accde/accdb) file name & Guide File Name
           accde_fname = "Database.accde"
'36
'37    'Guide_Fname = "Enter a name here"
'38
'39  DO NOT change any code below this point!!!
'40  ***************************************************************'
        Set fso = CreateObject("Scripting.FileSystemObject")
'42
'43  Get the Last Modified date for the server file
        Set f = fso.GetFile(svr_db_pth & accde_fname)    '"\accde\" &
'45   
        svr_f_mod_dt = f.DateLastModified
'47
'48  Get the Last Modified date for the local file
        Set f = fso.GetFile(lcl_db_pth & "\" & accde_fname)
'50    
        lcl_f_mod_dt = f.DateLastModified
'52
'53  Check if the server file is newer
        If svr_f_mod_dt - lcl_f_mod_dt > 0 Then
'55  Server file is newer, copy new ".accde"
'56
'57  Copy the new accde files
    fso.CopyFile _
        svr_db_pth & accde_fname, _
        lcl_db_pth & "\", True
'61
'    fso.CopyFile _
'        svr_db_pth & Guide_Fname, _
'        lcl_db_pth & "\", True
'65
'    fso.CopyFile _
'        svr_db_pth & URO_MRC_Open, _
'        lcl_db_pth & "\", True
'69
    End If
'71  Now do the same thing for the Local File
'72  Get the Last Modified date for the server file
'        Set g = fso.GetFile(svr_db_pth & accde_Local_fname)    '"\accde\" &
'74   
'        svr_g_mod_dt = g.DateLastModified
'76
'77  Get the Last Modified date for the local file
'78       Set g = fso.GetFile(lcl_db_pth & "\" & accde_Local_fname)
'79    
'       lcl_g_mod_dt = g.DateLastModified
'81
'82  Check if the server file is newer
        If svr_g_mod_dt - lcl_g_mod_dt > 0 Then
'84  Server file is newer, copy new ".accde"
'85
'86  Copy the new accde files
'    fso.CopyFile _
'        svr_db_pth & accde_fname, _
'        lcl_db_pth & "\", True
'90
'    fso.CopyFile _
'        svr_db_pth & Guide_Fname, _
'        lcl_db_pth & "\", True
'94
'    fso.CopyFile _
'        svr_db_pth & URO_MRC_Open, _
'        lcl_db_pth & "\", True'75
'98
'99  Wait until the modified date of the
    Do Until svr_f_mod_dt = lcl_f_mod_dt
        lcl_f_mod_dt = f.DateLastModified
        WScript.Sleep 1000
       Loop
'104
        End If
'106
'107 start the database
    Set objShellApp = CreateObject("Shell.Application")
'109
        objShellApp.ShellExecute "MSAccess", _
    lcl_db_pth & "\" & accde_fname, _
    "open"
'    End If

'115

****Uninstall.bat****

@ECHO OFF
ECHO:********************************************************************************
ECHO:
ECHO:   This will Remove all files and shortcuts from your computer
ECHO:
ECHO:
ECHO:
ECHO:   You will be asked if you want to delete files, if the locations and files
ECHO:                 appear correct type "y" and hit "Enter"
ECHO:
ECHO:
ECHO:********************************************************************************
Pause

Rem     Some users have the program loaded in Documents and Settings\All Users, others have 
Rem    the program loaded to their User name This checks to see where it is loaded
 
if Exist "C:\Documents and Settings\All Users\Start Menu\Programs\Folder" goto AllUsers

if Exist "C:\Documents and Settings\%username%\Desktop\Shortcut.lnk" goto LocalUser

:AllUsers
DEL "C:\Documents and Settings\All Users\Desktop\Shortcut.lnk"
DEL "C:\Documents and Settings\All Users\Start Menu\Programs\Folder\*.*"
DEL "C:\Documents and Settings\All Users\Start Menu\Programs\Folder"

REM     If user doesn't have permissions to write to all users, the above will fail. 
REM     Therefore, test for presence of the link. If Not Found - then goto to the user's paths.
if Not Exist "C:\Documents and Settings\All Users\Desktop\Shortcut.lnk" goto EXIT

:LocalUser
DEL "C:\Documents and Settings\%username%\Start Menu\Programs\Folder\*.*"
DEL "C:\Documents and Settings\%username%\Start Menu\Programs\Folder"
DEL "C:\Documents and Settings\%username%\Desktop\Shortcut.lnk"


Rem Delete the Program Files data
CHDIR c:
DEL C:\"Folder\*.*"
DEL C:\"Folder"
if Not Exist "C:\Documents and Settings\All Users\Desktop\Shortcut.lnk" goto EXIT
if Not Exist "C:\Documents and Settings\%username%\Desktop\Shortcut.lnk" goto EXIT

:Uninstall Failed
echo off
ECHO:
echo:   The Database has not completely been removed from your machine.
echo:   Contact a System Administrator
Echo:
Goto Goodbye
Rem pause

:EXIT
echo off
ECHO:
echo: The Database has successfully been removed from your machine.
Echo:

:Goodbye
pause

 

[welshgasman]

****, that is a lot of work.
Glad you got a solution at last.