Autoload frontend

[Luzbel]

I have an Access 2000 file with the backend in SQL Server 2005. The Access file is currently in a shared folder. The 40 users have already have the shortcut on their Desktops. I need to make an Access file VBA code routine to create a file in each users “My Documents” folder and have them run that file.
I am thinking of somehow when I open the mdb file everyone is looking at
If there isn’t a file created in their %USERPROFILE% create it and run it. If there is a file in the folder check somehow if different to the “master” .mdb file in the shared folder. If it is then copy (overwrite) to the their %USERPROFILE%.
I really don’t want to have to change the already created link on each user. They are a bit hard to get though.

 

[lameid]

Your lucky, I recently had to deal with my documents. Here is some code on the folder side of things to get you started.

Function MyDocumentsFolder() As String
    Dim strPath As String
    Dim WshShell As Object
    Dim objEnv As Object
    Dim intpos As Integer
    Dim intpos2 As Integer
    Set WshShell = CreateObject("WScript.Shell")
    Set objEnv = WshShell.Environment("User")
    strPath = WshShell.RegRead("HKCU\Software\Microsoft\Windows\CurrentVersion\Explorer\User Shell Folders\Personal")
    intpos = InStr(1, strPath, "%")
    If intpos > 0 Then
        intpos2 = InStr(intpos + 1, strPath, "%")
        strPath = Environ(Mid(strPath, intpos + 1, intpos2 - intpos - 1)) & Right(strPath, Len(strPath) - intpos2)
        MyDocumentsFolder = strPath
    Else
        MyDocumentsFolder = strPath
    End If
    Set objEnv = Nothing
    Set WshShell = Nothing
End Function

Sub CopyFile(strFileSource As String, strDestination As String)
    Dim FSO As Object
    Set FSO = CreateObject("Scripting.FileSystemObject")
    FSO.CopyFile strFileSource, strDestination
    Set FSO = Nothing
End Sub

Function SubFolderPrep(strPath As String) As String
    'Ensure Path ends in a backslash
    SubFolderPrep = strPath & IIf(Right(strPath, 1) = "\", "", "\")
End Function

I would use a new frontend file name for the existing application. Replace the existing frontend with a file that does as you describe.

You will probably want to use shell to run your local copy of access.

Version control is usually handled with the FE with a local table that it compares to the BE.

There are also some FAQ's on distributing FE's. You can probably modify one of those to meat your needs.

To run your code... use an autexec macro in your network mdb that uses the runcode to execute your Vba.