none
There was a problem sending the command to the program.

    Question

  • Hi,

    Not sure if this problem has anything to do with my code, or just how Office 2007 interacts with the rest of the world. I have a desktop icon which opens a utility database (C:\Access97\Utility.mdb) which has linked tables to a local frontend (C:\Access97\fpsapps.mdb), plus a linked table to the latest frontend stored on the server. After going through a series of checks to ensure that the computer is linked to the appropriate server and that a newer frontend is available, this utility database copies the newer frontend to the local folder, then resets the default staff ID for the local frontend, then opens that frontend and closes the utility database.

    <code snip>

    CmdToOpen = SysCmd(acSysCmdAccessDir) & "Msaccess.exe " & CurrentFE

    If LatestVersion > MyVersion Then
    'get default staff person before copying new frontend
        Set rst = db.OpenRecordset("MyDefaultsTable_StaffPerson", dbOpenSnapshot)
        DefaultStaffPerson = rst!StaffID
        rst.Close
    FileCopy NewestFE, CurrentFE
    'Reset the default StaffID
        Set rst = db.OpenRecordset("MyDefaultsTable_StaffPerson", dbOpenDynaset)
            With rst
            .Edit
            !StaffID = DefaultStaffPerson
            .Update
            End With
            rst.Close
     'Open the local FE
     Set db = Nothing
     Set Tdfs = Nothing
     Set rst = Nothing
    Shell CmdToOpen, vbNormalFocus
    DoCmd.Quit
    Exit Sub
    Else
    Shell CmdToOpen, vbNormalFocus
    DoCmd.Quit
    Exit Sub
    End If
    End Sub

    This works fine on desktops running WinXP: Access 97,2002/2003 and, yes, even 2007. When I run it on a WinXP/Access 2007 SP2 laptop, the procedure appears to run correctly -- the local frontend is updated, the default staffID has been reset to its original value, the updated frontend opens and the utility database closes. Then, more times than not, a message pops up approximately 5 seconds after the frontend has opened and the utility has closed which reads: "There was a problem sending the command to the program." The title bar at the top of the message box reads: "C:\Access97\Utility.mdb".

    The frontend that was opened by the utility database appears to perform normally except for the presence of this message. While trying to problem solve, however, there were a couple times when I closed out of the frontend that there seemed to be a problem in the screen display. Rather than just closing/disappearing from the screen, the window displaying the database seemed to momentarily be projected onto a smaller window (Windows Explorer) which had been open at the time. After about a second or two, the image of the database disappeared, leaving just the normal image of the window for Windows Explorer.   

    Anyone have any insight into why this message pops up? I've looked at a lot of Google links and am still unclear as to the cause or remedy. Haven't run into this problem previously on this same laptop when opening/closing other 2007 databases, 2007 Word or 2007 Outlook.

    And, maybe an even better question is: can I just accept that this message is going to pop up on some of our laptops and just have the user click OK and continue working with the frontend that has opened?

    Thanks for any help.

    Mark

     

    Monday, August 29, 2011 8:31 PM

Answers

  • The Recordset Issue

    As simply a statement of the obvious, the significant difference between the code before and after the "Else" statement is that you open a recordset before "Else" and you don't after. By opening the recordset, the new FE is loaded into memory.  As a consequence, the new FE is loaded quickly by the Shell command.  In contrast, by not opening a recordset on the old FE after the "Else" statement, the Shell command opens the old FE slowly.

    Here are my other thoughts.  I think the db variable refers to the utility database. I think the table in the utility database named "MyDefaultsTable_StaffPerson" is a linked table. The actual table exists in the FE, where the StaffID is stored. I think the following steps describe what's going on after the "If" statement:

    1.   A recordset is opened on the linked table to obtain the StaffID from the old FE.

    2.   The recordset is closed.

    3.   The new FE is copied over the old FE.

    4.   A recordset is opened on the linked table to store the StaffID in the new FE.

    5.   The recordset is closed.

    If the above describes your situation, then you may want to see if you can resolve this issue by opening a recordset after the "Else" statement.  Hopefully, this will get the FE into memory so Shell will load it faster. This may work better than DoEvents. Below is a code demo.

     

    > Can I put DoEvents after every line?

    You could. But I think there should be a better resolution.

     

    > I also use the IP address approach in the FE, itself,
    > to link to the server when the laptop user has records
    > to upload or needs to download an updated copy of
    > the BE to the laptop so that they can use the database offline.

    Where are you using the IP address?

     

    CODE DEMO   

        '   While debugging, send variables to
        '   Immediate window so they can be checked:
        Debug.Print "NewestFE = ", NewestFE
        Debug.Print "CurrentFE = ", CurrentFE
        Debug.Print "CmdToOpen = ", CmdToOpen
    
        '   See if new frontend needs to be copied:
        If LatestVersion > MyVersion Then
        
            '   Get default staff person from current FE
            '   before copying new FE from server:
            Set rst = db.OpenRecordset("MyDefaultsTable_StaffPerson", dbOpenSnapshot)
            DefaultStaffPerson = rst!StaffID
            rst.Close
            
            '   Copy new frontend from server:
            FileCopy NewestFE, CurrentFE
            
            '   Store default StaffID in new frontend:
            Set rst = db.OpenRecordset("MyDefaultsTable_StaffPerson", dbOpenDynaset)
            With rst
                .Edit
                    !StaffID = DefaultStaffPerson
                .Update
            End With
            
            '   Destroy objects:
            rst.Close
            Set rst = Nothing
            Set Tdfs = Nothing
            Set db = Nothing
            
            '   Open the new local FE:
            Shell CmdToOpen, vbNormalFocus
            DoCmd.Quit
            Exit Sub
        
        Else
            
            '   Force Access to load the FE before the
            '   Shell command (to resolve "timing" issue):
            Set rst = db.OpenRecordset("MyDefaultsTable_StaffPerson", dbOpenSnapshot)
            
            '   Destroy objects:
            rst.Close
            Set rst = Nothing
            Set Tdfs = Nothing
            Set db = Nothing
            
            '   Open the existing local FE:
            Shell CmdToOpen, vbNormalFocus
            DoCmd.Quit
            Exit Sub
        
        End If

     


    • Edited by Geoff_G Sunday, September 04, 2011 3:15 AM
    • Marked as answer by mdavisfps Tuesday, September 06, 2011 5:24 PM
    Sunday, September 04, 2011 3:11 AM

All replies

  • Hi Mark,

     

    Maybe this simular thread help you finding the answer:

    http://www.softwaretalk.info/microsoft-access-there-was-a-problem-sending-the-command-to-the-program.htm

     

    Hope this helps,


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    Monday, August 29, 2011 11:12 PM
  • Hi Daniel,

    Yes, that's one of the links I had read. Although most of the links I've come across point to problems with Access 2007 and Win 7, we're running only Win XP. I guess it's possible that desktop computers running Win XP/Access 2007 behave differently than laptops running Win XP/Access 2007. Well, at least they do in our situation. Maybe desktops, however, are just more efficient in running 2007 compared to laptops. Or, maybe our IT dept. disabled "Use DDE" on our desktops and left it checked on our laptops. I kind of doubt it, however.

    I admit that I don't understand all that's mentioned in that link, but it seems to have something to do with opening multiple instances of Access 2007. Perhaps this is not the same thing, but on the laptop there's no problem opening two separate databases side-by-side. Makes me wonder if there isn't a more efficient way of writing the code I've used -- where the utility database opens another database and then closes itself. Microsoft's message obviously is not helpful in pinpointing the problem (at least, not to me).

    I'd hate to think that I have to disable "Use DDE" on all our laptops or (shudder) ask IT to change the registry settings on all the laptops that are running Access 2007 (as suggested by some of the links).

    Correct me if I'm wrong, but doesn't the link you provided indicate that operations may still progress as intended in spite of the message? If so, then maybe it's a bug of Access 2007 that we could learn to live with -- closing that message box every time we use that utility database to automate frontend updates and then open the frontend. But if there's an adjustment to my code that I could make which would avoid the problem altogether, then that would obviously be preferable.

    Thanks, Daniel, for your interest and the link you provided.
    Mark

    Tuesday, August 30, 2011 12:40 AM
  • Hi Mark,

     

    Yes, I am also not sure if you are able to find the answer in there.

    Maybe someone else can provide better insight on the error message.

     

    > But if there's an adjustment to my code that I could make which would avoid the problem altogether, then that would obviously be preferable. <

    I don't see anything wrong in your code.

     

    But if you are interested, Tony has an excellent tool, which does the job for you:

    http://www.autofeupdater.com/

    Or this simular code you are using, but then slightly different in below thread:

    http://www.access.qbuilt.com/html/update_front_end.html

     

     

     


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    Tuesday, August 30, 2011 4:53 AM
  • Thanks, Daniel.

    I've looked at Tony's autofeupdater and maybe it would work in our situation, but there's some custom things about the FE that I use that might make it difficult. And though it's not very expensive, there's still the matter of getting corporate's approval. I tried the code from http://www.access.qbuilt.com/html/update_front_end.html -- it copied the target FE, replaced the existing FE and then opened the FE fine, but upon closing the FE the .ldb was left hanging around. After manually deleting the .ldb and then reopening the FE I got the Access was left in an unstable state message, etc.

    I appreciate your suggestions. I'm surprised that it's not a bigger issue among developers, but then maybe there's not that much use of this kind of procedure on laptops. I'm at home now working on a Win 7 desktop with Access 2007 and am not encountering the message at the top of this post as I repeatedly run the utility database.

    Mark

    Tuesday, August 30, 2011 8:25 AM
  • CmdToOpen = SysCmd(acSysCmdAccessDir) & "Msaccess.exe " & CurrentFE

    If LatestVersion > MyVersion Then
    'get default staff person before copying new frontend
        Set rst = db.OpenRecordset("MyDefaultsTable_StaffPerson", dbOpenSnapshot)
        DefaultStaffPerson = rst!StaffID
        rst.Close
    FileCopy NewestFE, CurrentFE
    'Reset the default StaffID
        Set rst = db.OpenRecordset("MyDefaultsTable_StaffPerson", dbOpenDynaset)
            With rst
            .Edit
            !StaffID = DefaultStaffPerson
            .Update
            End With
            rst.Close
     'Open the local FE
     Set db = Nothing
     Set Tdfs = Nothing
     Set rst = Nothing
    Shell CmdToOpen, vbNormalFocus
    DoCmd.Quit
    Exit Sub
    Else
    Shell CmdToOpen, vbNormalFocus
    DoCmd.Quit
    Exit Sub
    End If
    End Sub

    Hi Mark,

    If I read your code correctly, then this code is run in a separate application, to check the proper version, if necessary install a new FE, and open the FE.
    I assume that your "MyDefaultsTable_StaffPerson" is in the FE, and that your db-variable refers to that FE.

    Perhaps you have to have a closer look at this part of your code:

        Set rst = db.OpenRecordset("MyDefaultsTable_StaffPerson", dbOpenSnapshot)
        DefaultStaffPerson = rst!StaffID
        rst.Close
    FileCopy NewestFE, CurrentFE
    'Reset the default StaffID
        Set rst = db.OpenRecordset("MyDefaultsTable_StaffPerson", dbOpenDynaset)
            With rst
            ...

    In fact, you set a recordset, change the FE, and using the same variable db, you set another recordset.
    You could try a DoEvents after FileCopy (if the variable is still valid), or close db before and open it again after the FileCopy.

     

    Imb.

     

    Tuesday, August 30, 2011 8:55 AM
  • Yes, Imb, I understand what you're saying about resetting the db variable. I'll give that a try. The resetting of StaffID does complete satisfactorily, however.
    I did trying inserting DoEvents after the FileCopy statement, but it didn't help. Appreciate the suggestions and I'll post back a little later with the results.

    Thanks,
    Mark 

    Tuesday, August 30, 2011 11:27 AM
  • Well, this is interesting. It's the Else part of the above code that seems to be causing the problem. I discovered that whether I reset the db variable or not (I tried it both ways), the code works and the message "There was a problem sending a command to the program" does not appear -- but only if there is a newer version on the server ... the new version is copied, StaffID is reset and the database opens without the message. However, if there is not a new version (the Else part), the database opens, but the message appears. Go figure.

    Thoughts?

    Mark

    Tuesday, August 30, 2011 12:06 PM
  • Correction to the code I posted. In the Else statement I did set the variables to nothing just as I had for the If statement ...

    <code snip>

    CmdToOpen = SysCmd(acSysCmdAccessDir) & "Msaccess.exe " & CurrentFE

    If LatestVersion > MyVersion Then
    'get default staff person before copying new frontend
        Set rst = db.OpenRecordset("MyDefaultsTable_StaffPerson", dbOpenSnapshot)
        DefaultStaffPerson = rst!StaffID
        rst.Close
    FileCopy NewestFE, CurrentFE
    'Reset the default StaffID
        Set rst = db.OpenRecordset("MyDefaultsTable_StaffPerson", dbOpenDynaset)
            With rst
            .Edit
            !StaffID = DefaultStaffPerson
            .Update
            End With
            rst.Close
     'Open the local FE
     Set db = Nothing
     Set Tdfs = Nothing
     Set rst = Nothing
    Shell CmdToOpen, vbNormalFocus
    DoCmd.Quit
    Exit Sub
    Else
    Set db = Nothing
    Set Tdfs = Nothing
    Set rst = Nothing
    Shell CmdToOpen, vbNormalFocus
    DoCmd.Quit
    Exit Sub
    End If
    End Sub

    Tuesday, August 30, 2011 12:37 PM
  • Well, this is interesting. It's the Else part of the above code that seems to be causing the problem. I discovered that whether I reset the db variable or not (I tried it both ways), the code works and the message "There was a problem sending a command to the program" does not appear -- but only if there is a newer version on the server ... the new version is copied, StaffID is reset and the database opens without the message. However, if there is not a new version (the Else part), the database opens, but the message appears. Go figure.

    Thoughts?

    Mark


    Hi Mark,

     

    In the "new version" part you do a neat close of the db database:

        ...
        Set db = Nothing
        Set Tdfs = Nothing
        Set rst = Nothing
        Shell CmdToOpen, vbNormalFocus
        DoCmd.Quit
        Exit Sub
      Else
        Shell CmdToOpen, vbNormalFocus
        DoCmd.Quit
        Exit Sub
        ....

    whereas you do not do that in the "old version" part.

    Could that be the difference?

     

    Imb.

    Tuesday, August 30, 2011 12:42 PM
  • Yeah, I saw that too and I jumped up and ran over to the laptop thinking that was the problem ... only to find that in my working copy I had put that in. (It was my posting that left it out) :(
    Tuesday, August 30, 2011 12:46 PM
  •  I think that by eliminating the Else part the problem with the message is resolved. I only tried it once, but moving the Else statements out of the If ... Then ... End If bracket altogether seemed to work. What I have now is:

    CmdToOpen = SysCmd(acSysCmdAccessDir) & "Msaccess.exe " & CurrentFE

    If LatestVersion > MyVersion Then
    'get default staff person before copying new frontend
        Set rst = db.OpenRecordset("MyDefaultsTable_StaffPerson", dbOpenSnapshot)
        DefaultStaffPerson = rst!StaffID
        rst.Close
    FileCopy NewestFE, CurrentFE
    'Reset the default StaffID
        Set rst = db.OpenRecordset("MyDefaultsTable_StaffPerson", dbOpenDynaset)
            With rst
            .Edit
            !StaffID = DefaultStaffPerson
            .Update
            End With
            rst.Close
     'Open the local FE
     Set db = Nothing
     Set Tdfs = Nothing
     Set rst = Nothing
    Shell CmdToOpen, vbNormalFocus
    DoCmd.Quit
    Exit Sub
    End If

    Set db = Nothing
    Set Tdfs = Nothing
    Set rst = Nothing
    Shell CmdToOpen, vbNormalFocus
    DoCmd.Quit
    End Sub

    <input id="8d3fff5a-a036-410e-bb32-5cc47250f0a8_attachments" type="hidden" />

    Tuesday, August 30, 2011 4:21 PM
  • Once again, I'm too quick to celebrate. Must have worked only the first time that I eliminated the Else statement. I posted what appeared to be a solution, but when I got a chance later to try it again I got the same results as before:

    When there's a new frontend on the server the code works fine and there's no message. Tried this 10 times in a row and no message. FE updated correctly and default Staff ID was assigned successfully.

    If the versions are the same, however, then the existing FE opens (as intended), and the utility database closes, but the message appears. Even tried it with the local version being greater than the one on the server -- the message appears. Conclusion: anything other than LatestVersion > MyVersion results in the message.

    I sure don't see anything in my code -- especially the original If ... Then statement that would cause this. I even commented out the "false" part of that statement, i.e., what takes place if LatestVersion is not > MyVersion -- the Shell CmdToOpen, vbNormalFocus and DoCmd.Quit statements immediately before End Sub -- which is intended to open the existing FE if there's not a newer version. The results were that the FE did not open (of course) and the utility database did not close (of course). The message still appeared, however.

    Signed,
    Completely Baffled

    Tuesday, August 30, 2011 6:02 PM
  • Hi Mark,

     

    Only a guess, try the DoEvents after the Shell CmdToOpen:

     ...
     Else
       Shell CmdToOpen, vbNormalFocus
       'place DoEvents here
       DoEvents
       DoCmd.Quit
     Exit Sub
     
    

    I have tried to reproduce the error you receive, but to no avail.

    But then I have not an XP operating system and also not an Access 97 version.

     

    Hope this helps,


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    Tuesday, August 30, 2011 7:14 PM
  • Daniel,
    I'll try the DoEvents after Shell CmdToOpen, vbNormalFocus. Sounds like a good thing to try.

    But just to clarify: This problem ONLY seems to be occurring with Access 2007 and ONLY on one of our corporate laptops (Dell Latitude E6410 i5 CPU, 1.92 Gb ram) which is available to me for testing. I ran the code with no problem on one of our older laptops (a Dell D510 with 504Mb -- but running Access 2003). Sorry if the folder name "Access97" was misleading -- it's just a legacy name for where the local files are stored.

    .. just tried it with DoEvents after Shell CmdToOpen, vbNormalFocus, but no improvement. Still works fine on that laptop as long as LatestVersion > MyVersion ... OR I'm running the code on a desktop ... OR I'm running any other version of Access (97 through 2003).

    Mark

    Tuesday, August 30, 2011 7:55 PM
  • I don't know if this may help.
    Just a suggestion:
    Try enclosing the paths in quotation marks.
    For example:

      Dim CurrentFE As String
      Dim CmdToOpen As String
      
      CurrentFE = """C:\My Documents\My Data Sources\OldFE.mdb"""
      CmdToOpen = """" & SysCmd(acSysCmdAccessDir) & "Msaccess.exe"" " & CurrentFE
    
    

     

    Tuesday, August 30, 2011 9:18 PM
  • It's worth a shot. I'll try it tomorrow at work. Thanks for the suggestion, Geoff.

    Mark
    Tuesday, August 30, 2011 10:35 PM
  • Daniel, Imb & Geoff,

    First, thanks for the help!

    I believe I've got it to work. It may still throw that message out occasionally in the future, I'm not sure,but right now it's working repeatedly on the Access 2007 laptop regardless of whether there's an earlier version of the FE on the server, or not.

    Steps I've taken:
    1. Before making any other changes, I placed a message box ("Made it to here") at consecutive steps in the procedure (deleting the previous message box each time). When I added it to the Else statement (where there is not a newer version on the server), the existing FE opened and without the troublesome "There was a problem sending the command to the program" message appearing. This indicated to me that there was a timing issue of some sort interferring with the code -- perhaps the DoCmd.Quit command? -- and the message box allowed enough of a delay that all the processes could complete. Does this make sense?

    2. I added DoEvents after Shell CmdToOpen, vbNormalFocus (per Daniel's suggestion). The frequency of the troublesome message was much reduced, but still present.

    3. I realized that except for the initial checking for whether the local FE was already open, all other sections of the code concluded in opening the local FE. Instead of doing that after each section where the result was to open the FE, I just used "GoTo ExitHere" once at the end of the sub. (Don't know if this did anything other than make the code look a little neater and easier to follow).

    4. I found that it makes no difference whether I wrap SysCmd(acSysCmdAccessDir) & "Msaccess.exe " in extra quotes or not. Either way seems to work:

       a. SysCmd(acSysCmdAccessDir) & "Msaccess.exe " & CurrentFE
       b. """" & SysCmd(acSysCmdAccessDir) & "Msaccess.exe"" " & CurrentFE
    (Why would both ways work?)

    5. Wrapping the path of CurrentFE in extra quotes did not work. FileCopy NewFE, CurrentFE failed when I used CurrentFE = """C:\Access97\Peteapps.mdb""".
    CurrentFE = "C:\Access97\Peteapps.mdb" does work.

    Conclusion:
    Since there was no problem (no troublesome message) when running this code on any desktop using any version of Access (including 2007) and since there was no problem running it on a laptop with only 504Mb of ram, but using Access 2003, I'm guessing that it's a resource issue or speed of processing or some such thing on laptops running Access 2007 -- well, at least on our laptops with the configuration noted up above. I suspect that our laptops may also being doing stuff in the background which intermittently causes Windows XP(?)/Office 2007(?)/Access 2007(?) to throw out that message -- in spite of the FE still opening as it should and the utility database closing.

    FWIW, here's my final code for the desktop icon which opens one of our databases:

    Option Compare Database
    Option Explicit

    Private Sub Form_Open(Cancel As Integer)
    'THIS CODE IS FOR THE MOOREFIELD ICON
    'Note: Gateway to server is 10.57.2.1; filepath to server is 10.57.2.2
    '*********
    Dim obj As Object, strWQL As String
    Dim CurrentDefaultGateway As String
    Dim MyDefaultServer As String
    '*********
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim tdf As TableDef
    Dim Tdfs As TableDefs
    Dim MyTableLinks As String
    Dim CurrentFE As String 'always "C:\Access97\Peteapps.mdb"
    Dim NewestFE As String
    Dim SharedFolder As String
    Dim MyVersion As Long
    Dim LatestVersion As Long
    Dim DefaultStaffPerson As Long
    Dim CmdToOpen As String
    '*********
    CurrentFE = "C:\Access97\Peteapps.mdb"
    '*********
    'Is the local FE already open?
    If Len(Dir("C:\Access97\Peteapps.ldb")) > 0 Then
    MsgBox "The Moorefield database is already open ...check your Taskbar."
    DoCmd.Quit
    Exit Sub
    End If
    '*********
    'Step 2 (Using WMI, is the computer logged-in to one of the servers?)
    'WMI method
     On Error Resume Next
    With GetObject("winmgmts://./root/cimv2")
      strWQL = "Select * From Win32_NetworkAdapterConfiguration Where IPEnabled=True"
      ' there might be multiple network adapter configurations
      For Each obj In .ExecQuery(strWQL)
         CurrentDefaultGateway = obj.DefaultIPGateway(0)
      Next
     End With

    Select Case CurrentDefaultGateway
    Case "10.57.3.1" 'connected to the Romney server
      SharedFolder = "\\10.57.3.2\Access\"
      Case "10.57.2.1" 'connected to the Moorefield server
      SharedFolder = "\\10.57.2.2\Access\"
    Case Else 'not connected to either server
    GoTo ExitHere
    End Select

    '*********
    'Step 3: (Is there a newer version on the server?)
    '...determine MyVersion and MyTableLinks while rst is open
    Set db = CurrentDb()
    Set Tdfs = db.TableDefs
    Set rst = db.OpenRecordset("MyDefaultsTable_Region", dbOpenSnapshot)
    MyVersion = rst!Version
    MyTableLinks = rst!LinkedTo
    rst.Close

    Set rst = db.OpenRecordset("MyDefaultsTable_Server", dbOpenSnapshot)
    MyDefaultServer = rst!DefaultServer
    rst.Close

    If MyDefaultServer = CurrentDefaultGateway Then
    GoTo ContinueHere
    Else
    GoTo ExitHere
    End If

    ContinueHere:
    'Determine correct NewestFE
        Select Case SysCmd(acSysCmdAccessVer)
        Case 8    'Access 97
            If MyTableLinks = "Cdrive" Then
                NewestFE = SharedFolder & "_NewFE_Home_97\Peteapps.mdb"
            Else
                NewestFE = SharedFolder & "_NewFE_Office_97\Peteapps.mdb"
            End If
        Case 10, 11   'Access 2002
            If MyTableLinks = "Cdrive" Then
                NewestFE = SharedFolder & "_NewFE_Home_2002\Peteapps.mdb"
            Else
                NewestFE = SharedFolder & "_NewFE_Office_2002\Peteapps.mdb"
            End If
        Case 12   'Access 2007
            If MyTableLinks = "Cdrive" Then
                NewestFE = SharedFolder & "_NewFE_Home_2007\Peteapps.mdb"
            Else
                NewestFE = SharedFolder & "_NewFE_Office_2007\Peteapps.mdb"
            End If
        Case 14   'Access 2010
            If MyTableLinks = "Cdrive" Then
                NewestFE = SharedFolder & "_NewFE_Home_2010\Peteapps.mdb"
            Else
                NewestFE = SharedFolder & "_NewFE_Office_2010\Peteapps.mdb"
            End If
        End Select
       
        'Loop through the tables collection to link to Server's DefaultsTable_Region
        For Each tdf In Tdfs
        If Len(tdf.Connect) > 0 Then
            Select Case tdf.Name
                Case "ServerDefaultsTable_Region"
                    tdf.Connect = ";DATABASE=" & NewestFE
                    tdf.RefreshLink
                Case Else
                    tdf.Connect = ";DATABASE=" & CurrentFE
                    tdf.RefreshLink
            End Select
        End If
        Next 'Goto next table
    Set rst = db.OpenRecordset("ServerDefaultsTable_Region", dbOpenSnapshot)
    LatestVersion = rst!Version
    rst.Close

    '*********
    'If appropriate, copy the newer FE to "C:\Access97\Peteapps.mdb"
    '*********
    If LatestVersion > MyVersion Then
    'get default staff person before copying new frontend
        Set rst = db.OpenRecordset("MyDefaultsTable_StaffPerson", dbOpenSnapshot)
        DefaultStaffPerson = rst!StaffID
        rst.Close
    FileCopy NewestFE, CurrentFE
    DoEvents
    'Reset the default StaffID
        Set rst = db.OpenRecordset("MyDefaultsTable_StaffPerson", dbOpenDynaset)
            With rst
            .Edit
            !StaffID = DefaultStaffPerson
            .Update
            End With
            rst.Close
        GoTo ExitHere
    Else
    GoTo ExitHere
    End If

    ExitHere:
            On Error Resume Next
            Set db = Nothing
            Set Tdfs = Nothing
            Set rst = Nothing

    CmdToOpen = """" & SysCmd(acSysCmdAccessDir) & "Msaccess.exe"" " & CurrentFE
    'CmdToOpen = SysCmd(acSysCmdAccessDir) & "Msaccess.exe " & CurrentFE
    '        MsgBox "Made it to here"
    '        Exit Sub

    Shell CmdToOpen, vbNormalFocus
            'MsgBox "Made it to here"
            'Exit Sub
    DoEvents

    DoCmd.Quit

    '*********
    'errhandler?
    '*********
    End Sub

     

    Wednesday, August 31, 2011 3:05 PM
  • (Why would both ways work?)

    Quotes are usually needed when the path contains a space (e.g. "C:\Program Files\..."). However, I think they're not needed in your case as you're passing the path in a variable.  I suggested trying this anyway because of the following.

    I searched the Microsoft website using the following string:

    "Microsoft Access" "There was a problem sending the command to the program"

    Notice the quotes in the above string.  These force a search for the whole of the two strings in quotes.

    This search turned up:

    http://support.microsoft.com/kb/810582

    The above article, which applies to Access 2002, includes the error message you encountered when a database is opened from a web path, which made me think just maybe the problem was with the path.

    The search turns up some more interesting articles - like whether Adobe is installed.

    The error message isn't particularly helpful as it doesn't identify the exact cause of the problem.

    I think your comments about timing make sense. DoEvents would be a way of giving time for things to happen, but I don't know whether your code can successfully complete after you've issued the Quit command. If your database has quit, does DoEvents and Goto ExitHere execute? Your experience seems to suggest they do, as if they're in memory.

    I was interested to read your thread. Sorry I don't have a solution. I thought I'd share how I searched for a solution.

     

    Thursday, September 01, 2011 9:59 PM
  • Thank you, Geoff, for your interest and suggested links.

    I've read through them once and will go back again later. Over my head quite a bit, but makes me want to try \\server\share instead of \\10.57.3.2\share. Then, again, I found the info in the links rather confusing and contradictory. Should I also try "file:/server/share"?

    I don't know whether our security settings are affecting this, or not. I would think that if the path specified in the code was not in a trusted site that I would get a different error message and the file on the server could not be read at all, much less copied -- both activities which the code had no problems with when I had previously tested without the Else part.

    Interesting, though, that the message "There was a problem ... etc. " does not pop up if there's a newer version on the server, but does pop up if there's not (the Else part of the If ...Then statement).
    I also find it interesting that everything seems to proceed as desired in spite of the message appearing. As far as I can tell, it just means telling users to click "ok" and otherwise ignore the message should they even encounter it.
    Right now, however, the message is either not occurring,or it is very infrequent. I plan to test it more today.

    Mark

    Friday, September 02, 2011 8:38 AM
  • Ok, I'm at work now. Out of 50 attempts to open the FE via this utility database, I got the message "There was a problem sending the command to the program" only three times. All 50 attempts were when there was not a new version on the server. This is the condition of the If ... Then ... Else statement that was consistently failing before adding DoEvents and rearranging some of the code.

    I've pretty much concluded that it's not how I specify the path to the server that's causing a problem -- it's not a permissions issue, or that I.E.-7 has assigned the target folder on the server (which has been expressed as an IP address) to the internet, rather than our intranet. I would think that if it's that kind of a problem it would fail 100% of the time. Also, I realized that, up to now, I've been using a different approach to updating users' FEs; I've simply told them that there's a new frontend available and they've used a different utility database on their laptops which identifies the path to the server and shared folder the same way that the above code identifies that folder. There's never been any messages regarding permissions or "There was a problem sending the command to the program". I also use the IP address approach in the FE, itself, to link to the server when the laptop user has records to upload or needs to download an updated copy of the BE to the laptop so that they can use the database offline.

    In this morning's tests, I was able to increase the number of times that I received the message ("There was a problem sending the command to the program") by having either or both Outlook/Microsoft Exchange or I.E.-7 open. The message was still rather infrequent, however, compared to my earlier coding without DoEvents, etc. -- maybe 3 times out of 20.

    But I could consistently generate the message if I first "refreshed" either Outlook or I.E.-7 and then immediately opened the utility database. In fact, a number of times Windows would inform me that it could not find the path to the desktop shortcut which points to the utility database -- but then opened the utility database anyway. It's like Windows was so busy that it couldn't even resolve the shortcut's path, but then said: "oh, wait, there it is." That seems similar to what happens whenever I get the "There was a problem sending the command to the program" message -- the FE has opened anyway, in spite of the message, and then it's business as usual.

    I know that Outlook/Microsoft Exchange periodically and automatically updates its folders. Also, sometimes a message pops up while the user might be working in Word or the database (or Solitaire :)) indicating that the connection to Microsoft Exchange had been lost, but has now been reconnected. It may be that it's this extra activity going on in the background that causes a momentary hiccup in running the code in the utility database. So, I guess anything that I can do in my code to make it more efficient, or to slow down the processing at various points might cause the message to never appear. (Can I put DoEvents after every line? :))  At least it's now working at an acceptable level, I feel.

    Mark

    Friday, September 02, 2011 12:43 PM
  • The Recordset Issue

    As simply a statement of the obvious, the significant difference between the code before and after the "Else" statement is that you open a recordset before "Else" and you don't after. By opening the recordset, the new FE is loaded into memory.  As a consequence, the new FE is loaded quickly by the Shell command.  In contrast, by not opening a recordset on the old FE after the "Else" statement, the Shell command opens the old FE slowly.

    Here are my other thoughts.  I think the db variable refers to the utility database. I think the table in the utility database named "MyDefaultsTable_StaffPerson" is a linked table. The actual table exists in the FE, where the StaffID is stored. I think the following steps describe what's going on after the "If" statement:

    1.   A recordset is opened on the linked table to obtain the StaffID from the old FE.

    2.   The recordset is closed.

    3.   The new FE is copied over the old FE.

    4.   A recordset is opened on the linked table to store the StaffID in the new FE.

    5.   The recordset is closed.

    If the above describes your situation, then you may want to see if you can resolve this issue by opening a recordset after the "Else" statement.  Hopefully, this will get the FE into memory so Shell will load it faster. This may work better than DoEvents. Below is a code demo.

     

    > Can I put DoEvents after every line?

    You could. But I think there should be a better resolution.

     

    > I also use the IP address approach in the FE, itself,
    > to link to the server when the laptop user has records
    > to upload or needs to download an updated copy of
    > the BE to the laptop so that they can use the database offline.

    Where are you using the IP address?

     

    CODE DEMO   

        '   While debugging, send variables to
        '   Immediate window so they can be checked:
        Debug.Print "NewestFE = ", NewestFE
        Debug.Print "CurrentFE = ", CurrentFE
        Debug.Print "CmdToOpen = ", CmdToOpen
    
        '   See if new frontend needs to be copied:
        If LatestVersion > MyVersion Then
        
            '   Get default staff person from current FE
            '   before copying new FE from server:
            Set rst = db.OpenRecordset("MyDefaultsTable_StaffPerson", dbOpenSnapshot)
            DefaultStaffPerson = rst!StaffID
            rst.Close
            
            '   Copy new frontend from server:
            FileCopy NewestFE, CurrentFE
            
            '   Store default StaffID in new frontend:
            Set rst = db.OpenRecordset("MyDefaultsTable_StaffPerson", dbOpenDynaset)
            With rst
                .Edit
                    !StaffID = DefaultStaffPerson
                .Update
            End With
            
            '   Destroy objects:
            rst.Close
            Set rst = Nothing
            Set Tdfs = Nothing
            Set db = Nothing
            
            '   Open the new local FE:
            Shell CmdToOpen, vbNormalFocus
            DoCmd.Quit
            Exit Sub
        
        Else
            
            '   Force Access to load the FE before the
            '   Shell command (to resolve "timing" issue):
            Set rst = db.OpenRecordset("MyDefaultsTable_StaffPerson", dbOpenSnapshot)
            
            '   Destroy objects:
            rst.Close
            Set rst = Nothing
            Set Tdfs = Nothing
            Set db = Nothing
            
            '   Open the existing local FE:
            Shell CmdToOpen, vbNormalFocus
            DoCmd.Quit
            Exit Sub
        
        End If

     


    • Edited by Geoff_G Sunday, September 04, 2011 3:15 AM
    • Marked as answer by mdavisfps Tuesday, September 06, 2011 5:24 PM
    Sunday, September 04, 2011 3:11 AM
  • Well done, Geoff. Makes sense to me. I'll try it Tuesday when I get back to the office. In fact, I'll copy your code since it's so much neater and easier to follow than mine. Thanks for the follow-up. And thanks for the inspiration to writing easier to follow code!

    ... Your thoughts about the situation were correct.

    ... I don't use the laptop's IP address in this particular code. At the beginning of the code, which I had snipped, I needed to find out if the laptop is connected to the corporate server or not and, if so, is it the laptop's "home" server, or are they visiting our other office -- where FileCopy wouldn't work, or at least not as planned. FileCopy would be routed back to the user's "home" server, which would result in about a 10 minute wait for the file to be copied. Using WMI, I found that DefaultGateway worked fine to identify which server they were plugged into, but IP address didn't. I do use IP address, however, for other tasks accomplished by the FE -- transferring records from the laptop to the server (which the user only does when plugged into their "home" server), and in setting up the initial table links of the FE customized for each office.

    I'll post back next week after testing.

    Mark

    Sunday, September 04, 2011 8:26 AM
  • mdavisfps wrote:

    I've looked at Tony's autofeupdater and maybe it would work in our situation, but there's some custom things about the FE that I use that might make it difficult.

    Just curious.  What are those custom things?   I don't mind adding
    features at all.  In another day or two I'll be adding the ability to
    unzip files.   Feel free to email me directly if you would prefer.

    And though it's not very expensive, there's still the matter of getting corporate's approval.

    I can make it more expensive if that would help with credibility from
    corp.  <smile>

    Tony


    Tony Toews, Microsoft Access MVP
    Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
    Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
    For a convenient utility to keep your users FEs and other files   updated see http://www.autofeupdater.com/

    Tuesday, September 06, 2011 3:03 AM
  • Hi Tony,

    Yeah, maybe if it was about $1,500 that would really impress them. <smile>

    Seriously, I probably just didn't understand what all your autofeupdater could do, but I thought it was mostly for computers that are always linked to a server. Our laptops are used mostly at home, rather than at the office. As I think about it, however, I would guess that your autofeupdater easily takes this into account (... "If not linked to the server, then just open the existing FE"). Mostly, however, it's that my "real" job isn't to develop databases so it's a little more difficult for me to approach corp. about stuff like this. Also, I REALLY don't understand all this computer stuff well enough to anticipate what will work and what won't. So, I figured it would be best for me to try to build an "autofeupdater" on my own, given the (limited) knowledge that I already had about our system and our databases and trying to build upon that in small steps (or stumbles).

    I basically have one FE. All development changes go into that one FE. We have two regions, so I just rename the FE for each region just to be able to tell them apart. Then I reset the table links accordingly (separate BE for each region). The FE can also toggle between being linked to the server's BE (which is the setting used for our desktop computers), or to the local BE (mainly for our laptops), and has an option to set a default staff person (for writing progress notes, etc.). We have a mismash of Access versions from 97 to 2007 and after converting/compiling I place the appropriate FE in the appropriate folder on the server.

    I guess I also thought that we would end up needing licenses for many "servers", due to the multiple configurations of the FEs that we have on our computers...

    Romney office:
    icon for Romney database, linked to server
    icon for Romney database, linked to local C-drive
    icon for Moorefield database, but used in the Romney office and linked to the Romney server (we keep a copy of the Moorefield BE in the Romney office).
    icon for Moorefield database, but used in the Romney office and linked to local C-drive.
    Moorefield office:
    (essentially the opposite of the above FE/BE configurations).

    Confused yet? I know I am! :)

    Mark

     

     

    Tuesday, September 06, 2011 5:17 PM
  • Thanks, Geoff. Your suggestion to open a recordset after the Else statement seems to have done the trick. No troublesome message (knock on wood). Sure appreciate your help.

    Mark

    Tuesday, September 06, 2011 5:28 PM
  • mdavisfps wrote:

    Yeah, maybe if it was about $1,500 that would really impress them. <smile>

    Unfortunately sometimes it works that way.

    Seriously, I probably just didn't understand what all your autofeupdater could do, but I thought it was mostly for computers that are always linked to a server. Our laptops are used mostly at home, rather than at the office. As I think about it, however, I would guess that your autofeupdater easily takes this into account (... "If not linked to the server, then just open the existing FE").

    Disconnected systems like that isn't handled gracefully but will be
    handled soon.

    I guess I also thought that we would end up needing licenses for many "servers", due to the multiple configurations of the FEs that we have on our computers...

    Romney office:
    icon for Romney database, linked to server
    icon for Romney database, linked to local C-drive
    icon for Moorefield database, but used in the Romney office and linked to the Romney server (we keep a copy of the Moorefield BE in the Romney office).
    icon for Moorefield database, but used in the Romney office and linked to local C-drive.
    _Moorefield office_:
    (essentially the opposite of the above FE/BE configurations).

    As far as the licencing goes it really comes down to where do the
    startmdb.exe and configuration files reside.  If on one server then one
    server license.   But the FEs and BEs can reside on whatever server you
    want.

    Tony


    Tony Toews, Microsoft Access MVP
    Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
    Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
    For a convenient utility to keep your users FEs and other files   updated see http://www.autofeupdater.com/

    Tuesday, September 06, 2011 5:30 PM
  • Thank you, Tony, for the clarification. I will probably be looking at it again at some point in the future.
    Mark.
    Tuesday, September 06, 2011 5:38 PM
  • Thank you, Tony, for the clarification. I will probably be looking at it again at some point in the future.
    Mark.
    If your code bombs again, I think Tony's Updater would be better choice. Judging from the code and assuming that's the actual code, it doesn't ring any bells but it does ring my alarm bell.
    Wednesday, September 07, 2011 3:53 AM
  • AccessVandal wrote:

    Thank you, Tony, for the clarification. I will probably be looking at it again at some point in the future.

    If your code bombs again, I think Tony's Updater would be better choice. Judging from the code and assuming that's the actual code, it doesn't ring any bells but it does ring my alarm bell.

    Not necessarily.   I do somewhat similar things when starting up Access
    from within the Auto FE Updater so if a problem exists in the registry
    it could affect both methods.

    Tony


    Tony Toews, Microsoft Access MVP
    Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
    Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
    For a convenient utility to keep your users FEs and other files   updated see http://www.autofeupdater.com/

    Wednesday, September 07, 2011 5:42 AM
  • Hi AccessVandal

    I've benefitted a lot from reading your posts in this forum. Just wondering if you could be more specific in regards to why the code is ringing your alarm bell?

    BTW, It never felt like my code "bombed", just needed that extra step after the Else statement, as suggested by Geoff.

    Thanks,
    Mark

    Wednesday, September 07, 2011 10:32 AM
  • Mark,

    Your solution contains some clever stuff.

    > At the beginning of the code, which I had snipped,
    > I needed to find out if the laptop is connected to 
    > the corporate server or not and, if so, is it the
    > laptop's "home" server, or are they visiting our
    > other office -- where FileCopy wouldn't work,
    > or at least not as planned. FileCopy would be
    > routed back to the user's "home" server, which
    > would result in about a 10 minute wait for the file
    > to be copied. Using WMI, I found that DefaultGateway
    > worked fine to identify which server they were plugged
    > into, but IP address didn't. I do use IP address,
    > however, for other tasks accomplished by the FE --
    > transferring records from the laptop to the server
    > (which the user only does when plugged into their
    > "home" server), and in setting up the initial table
    > links of the FE customized for each office.

    I'd like to see your snipped code if you are able to share it. (I'll understand if you can't.) You can send to my following email address.

    Remove NO SPAM from:
    gcgnwsNO@SPAMtalktalk.net

    Geoff

    Wednesday, September 07, 2011 10:26 PM
  • Hi AccessVandal

    I've benefitted a lot from reading your posts in this forum. Just wondering if you could be more specific in regards to why the code is ringing your alarm bell?

    BTW, It never felt like my code "bombed", just needed that extra step after the Else statement, as suggested by Geoff.

    Thanks,
    Mark

    I'll have to ask in this case. Was the database object "db" remains the same after replacing the "db" (the FileCopy command)?

    Set rst = db.OpenRecordset("MyDefaultsTable_StaffPerson", dbOpenSnapshot)
            DefaultStaffPerson = rst!StaffID
            rst.Close
            
            '   Copy new frontend from server:
            FileCopy NewestFE, CurrentFE
            
            '   Store default StaffID in new frontend:
            Set rst = db.OpenRecordset("MyDefaultsTable_StaffPerson", dbOpenDynaset)
    

     

    Thursday, September 08, 2011 6:39 AM
  • AccessVandal,

    Yes, db remains CurrentDb (the utility database running the code). "MyDefaultsTable_StaffPerson" is a table within the utility database and is linked to CurrentFE.

    Mark

    Thursday, September 08, 2011 8:06 AM
  • Mark,

    So the CurrentFE was the "db" object. What about the new "CurrentFE" which was being replace?

    Since the CurrentFE (the old file) you have opened was the "db" object and when you replace it with a new one, the CurrentFE is no longer the same object any more.

    Hence, the object instanced and refered to was not the object "db.OpenRecordset" anymore. I was amazed that it worked. 

    Nowhere in your code that you did include to re-open the object "db". Remember, since you have replaced the new CurrentFE file, you need to re-instance the 'db" object again.

    Usually, a "db.Close" is required if the object instance is no longer the same. I would suggest that you include the code to re-open the "db" object again.

    Thursday, September 08, 2011 8:50 AM
  • AccessVandal,

    Ok, maybe I'm really confused here, but CurrentFE is never the db object. "db" always refers to the utility database that was initially opened (db = CurrentDb). The various rst objects are always run by the utility database, using tables (some linked, some not) that are part of the utility database. CurrentFE does not open until all other checks and actions (if any) have been completed.

    Mark


    P.S. I do set db = nothing. I've read posts that suggest that specifically closing and setting to nothing variables is not actually needed, but is still good practice. Either way, I figured that DoCmd.Quit was sufficient for closing the db variable.

    • Edited by mdavisfps Thursday, September 08, 2011 10:08 AM
    Thursday, September 08, 2011 10:04 AM
  • Mark,

    I'm aware the object "db" was "CurrentDb" but that's not your only problem. The linked table to the CurrentFE (old version), when it is replaced by a new "CurrentFE" file the existing link to the new "CurrentFE" was no longer the same anymore.

    What you're doing here is that you're taking chances that Access refresh the linked table to the new "CurrentFE" when you open the recordset the second time. By any luck, you can get away with it.

    My suggestion is to use the "OpenDatabase" method. You could still use the "CurrentDb" method to open the old "CurrentFE" link recordsets.

    After replacing the new "CurrentFE"....do something like this....

       Dim newDb As DAO.Database
       Dim newRs As DAO.Recordset
       Set newDb = OpenDatabase("FullPathToCurrentFE")
       Set newRs = newDb.OpenRecordset("MyDefaultsTable_StaffPerson")
            With newRs
                .Edit
                    !StaffID = DefaultStaffPerson
                .Update
            End With

       newRs.Close
       newDb.Close

    The path to the "CurrentFE", you can use this variable or object for the path.

    Thursday, September 08, 2011 10:30 AM
  • AccessVandal,

    Perhaps you're right. My thoughts are that as long as the connect string is correct and the target database and target table are in the correct place and named the correct name, then there doesn't have to be any refreshing of the link in order to open a recordset on that table even though that target database/table has been replaced with an identically named database/table just prior to the recordset being opened.

    When Access goes to open the recordset, it would look at the table name specified in the that recordset and then it would look for the connect string for that table and, in this case, it would find the target table at "C:\Access97\fpsapps.mdb". Why would it matter that "C:\Access97\fpsapps.mdb" at been replaced earlier? As long as the linked table is where it's supposed to be when Access tries to open the recordset I would expect the opening of the recordset to complete successfully. No?

    Mark 

    Thursday, September 08, 2011 11:16 AM
  • AccessVandal,

    Just to clarify (I hope) my setup:

    Imagine having a frontend database FE.mdb, which resides in C:\FEfolder, and a backend database BE.mdb, which resides in C:\BEfolder. There's also a backup folder -- C:\Backupfolder, which contains a copy of BE.mdb.

    The backend database has one table tblA. In the frontend database import tblA as a linked table. It's connect string will be "C:\BEfolder\BE.mdb".

    Open the frontend database and run the following code:

    Dim db as DAO.Database
    Dim rst as DAO.Recordset

    Set db = CurrentDb
    Set rst = db.OpenRecordset("tblA", dbOpenDynaset)
       With rst
       'do something with tblA
       End With
       rst.Close

    FileCopy "C:\Backupfolder\BE.mdb", "C:\BEfolder\BE.mdb"

    Set rst = db.OpenRecordset("tblA", dbOpenDynaset)
       With rst
       'do something with tblA
       End With
       rst.Close

    db.Close 'if you would like
    Set db = Nothing
    Set rst = Nothing

    End Sub
    '************************

    AccessVandal ... are you saying there would be a problem with this? ... that the table link would need to be refreshed, plus the BE itself would need to be opened?

    Mark

     

    Thursday, September 08, 2011 11:52 AM
  • Geoff,

    I apologize. I totally missed your post asking for the whole code. It's essentially what was posted above on Wed. August 31st (I think), but with your suggestion added for opening the recordset again as part of the Else statement. Interestingly (or frustratingly), after I included your suggestion, I've installed this on 6 laptops, all running 2007, and no message popped up. Thought I had it totally licked. Last laptop of today, however, was one of our older laptops -- a Dell 510/504mb ram, running 2003 -- and darn if the problem didn't show up on that laptop: if there's no new frontend, then the first time I open the utility database and run the code, no problem. Every time after that, however, the message pops up. Sometimes the message is the one about a problem sending the command to the program, sometimes the message is that there is a problem with the shortcut -- the specified path cannot be found. In either case, the FE opens anyway and the utility database closes. But if I alternate between clicking the shortcut to open the utility database and run the code and after closing the FE (the utility database had already closed) and then open a totally unrelated database, and then go back to the shortcut for the utililty database I never receive any message. I can bounce back and forth between these two unrelated databases and the message does not appear.

    One thing that dawned on me was that the FE itself runs code as the startup form opens (via autoexec macro) and that there is a global function included in that opening procedure which also uses a variable named db set to CurrentDb. So, perhaps between the opening of the FE and the closing of the utility database (via Cmd.Quit), there's a moment of confusion for Access which will kick out one or the other of those messages and, in spite of this, ultimately opening and closing the FE and utility database, respectively, as intended. ???? [I can try using a different name for the variable "db" in the FE's opening code and see what happens.]

    Anyhow, per your request, here's the complete code:

    '*******************************************
    Option Compare Database
    Option Explicit

    Private Sub Form_Open(Cancel As Integer)
    'THIS CODE IS FOR THE ROMNEY ICON
    'Note: Gateway to server is 10.57.3.1; filepath to server is 10.57.3.2
    '*********
    Dim obj As Object, strWQL As String
    Dim CurrentDefaultGateway As String
    Dim MyDefaultServer As String
    '*********
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim tdf As TableDef
    Dim Tdfs As TableDefs
    Dim MyTableLinks As String
    Dim CurrentFE As String 'always "C:\Access97\fpsapps.mdb"
    Dim NewestFE As String
    Dim SharedFolder As String
    Dim MyVersion As Long
    Dim LatestVersion As Long
    Dim DefaultStaffPerson As Long
    Dim CmdToOpen As String
    '*********
    If SysCmd(acSysCmdAccessVer) > 11 Then
    DoCmd.RunCommand acCmdWindowHide
    Else
    'earlier version so do nothing
    End If
    Me.TabTxtbox.SetFocus
    DoEvents
    '*********
    CurrentFE = "C:\Access97\fpsapps.mdb"
    '*********
    'Is the local FE already open?
    If Len(Dir("C:\Access97\fpsapps.ldb")) > 0 Then
    MsgBox "The Romney database is already open."
    DoCmd.Quit
    Exit Sub
    End If
    '*********
    'Step 2 (Using WMI, is the computer logged-in to one of the servers?)
    'WMI method
     On Error Resume Next
    With GetObject("winmgmts://./root/cimv2")
      strWQL = "Select * From Win32_NetworkAdapterConfiguration Where IPEnabled=True"
      ' there might be multiple network adapter configurations
      For Each obj In .ExecQuery(strWQL)
         CurrentDefaultGateway = obj.DefaultIPGateway(0)
      Next
     End With

    Select Case CurrentDefaultGateway
    Case "10.57.3.1" 'connected to the Romney server
      SharedFolder = "\\10.57.3.2\Access\"
      Case "10.57.2.1" 'connected to the Moorefield server
      SharedFolder = "\\10.57.2.2\Access\"
    Case Else 'not connected to either server
    GoTo ExitHere
    End Select

    '*********
    'Step 3: (Is there a newer version on the server?)
    '...determine MyVersion and MyTableLinks while rst is open
    Set db = CurrentDb()
    Set Tdfs = db.TableDefs
    Set rst = db.OpenRecordset("MyDefaultsTable_Region", dbOpenSnapshot)
    MyVersion = rst!Version
    MyTableLinks = rst!LinkedTo
    rst.Close

    Set rst = db.OpenRecordset("MyDefaultsTable_Server", dbOpenSnapshot)
    MyDefaultServer = rst!DefaultServer
    rst.Close

    If MyDefaultServer = CurrentDefaultGateway Then
    GoTo ContinueHere
    Else
    GoTo ExitHere
    End If

    ContinueHere:
    'Determine correct NewestFE
        Select Case SysCmd(acSysCmdAccessVer)
        Case 8    'Access 97
            If MyTableLinks = "Cdrive" Then
                NewestFE = SharedFolder & "_NewFE_Home_97\fpsapps.mdb"
            Else
                NewestFE = SharedFolder & "_NewFE_Office_97\fpsapps.mdb"
            End If
        Case 10, 11   'Access 2002
            If MyTableLinks = "Cdrive" Then
                NewestFE = SharedFolder & "_NewFE_Home_2002\fpsapps.mdb"
            Else
                NewestFE = SharedFolder & "_NewFE_Office_2002\fpsapps.mdb"
            End If
        Case 12   'Access 2007
            If MyTableLinks = "Cdrive" Then
                NewestFE = SharedFolder & "_NewFE_Home_2007\fpsapps.mdb"
            Else
                NewestFE = SharedFolder & "_NewFE_Office_2007\fpsapps.mdb"
            End If
        Case 14   'Access 2010
            If MyTableLinks = "Cdrive" Then
                NewestFE = SharedFolder & "_NewFE_Home_2010\fpsapps.mdb"
            Else
                NewestFE = SharedFolder & "_NewFE_Office_2010\fpsapps.mdb"
            End If
        End Select
       
        'Loop through the tables collection to link to Server's DefaultsTable_Region
        For Each tdf In Tdfs
        If Len(tdf.Connect) > 0 Then
            Select Case tdf.Name
                Case "ServerDefaultsTable_Region"
                    tdf.Connect = ";DATABASE=" & NewestFE
                    tdf.RefreshLink
                Case Else
                    tdf.Connect = ";DATABASE=" & CurrentFE
                    tdf.RefreshLink
            End Select
        End If
        Next 'Goto next table
    Set rst = db.OpenRecordset("ServerDefaultsTable_Region", dbOpenSnapshot)
    LatestVersion = rst!Version
    rst.Close

    '*********
    'If appropriate, copy the newer FE to "C:\Access97\fpsapps.mdb"
    '*********
    If LatestVersion > MyVersion Then
    'get default staff person before copying new frontend
        Set rst = db.OpenRecordset("MyDefaultsTable_StaffPerson", dbOpenSnapshot)
        DefaultStaffPerson = rst!StaffID
        rst.Close
    FileCopy NewestFE, CurrentFE
    DoEvents
    'Reset the default StaffID
        Set rst = db.OpenRecordset("MyDefaultsTable_StaffPerson", dbOpenDynaset)
            With rst
            .Edit
            !StaffID = DefaultStaffPerson
            .Update
            End With
            rst.Close
        GoTo ExitHere
    Else
    GoTo ExitHere
    End If

    ExitHere:
            On Error Resume Next
            Set db = Nothing
            Set Tdfs = Nothing
            Set rst = Nothing
            Set rst = db.OpenRecordset("MyDefaultsTable_StaffPerson", dbOpenSnapshot) 'to load FE in memory
            rst.Close

    CmdToOpen = """" & SysCmd(acSysCmdAccessDir) & "Msaccess.exe"" " & CurrentFE


    Shell CmdToOpen, vbNormalFocus
    DoEvents

    DoCmd.Quit

    '*********
    'errhandler?
    '*********
    End Sub

    '********************

    Mark

    P.S. The utility database has its own autoexec macro, which opens an unbound form and the code is placed in the form's On Open event. I thought I would give the user something pretty to look at while the code was running -- hence, Me.TabTxtbox.SetFocus
    DoEvents

    ... but the image of the form flicks past so quickly that you barely notice it. At least the Navigation Pane is closed!

     


    • Edited by mdavisfps Thursday, September 08, 2011 11:12 PM
    Thursday, September 08, 2011 11:03 PM
  • AccessVandal,

    Perhaps you're right. My thoughts are that as long as the connect string is correct and the target database and target table are in the correct place and named the correct name, then there doesn't have to be any refreshing of the link in order to open a recordset on that table even though that target database/table has been replaced with an identically named database/table just prior to the recordset being opened.

    When Access goes to open the recordset, it would look at the table name specified in the that recordset and then it would look for the connect string for that table and, in this case, it would find the target table at "C:\Access97\fpsapps.mdb". Why would it matter that "C:\Access97\fpsapps.mdb" at been replaced earlier? As long as the linked table is where it's supposed to be when Access tries to open the recordset I would expect the opening of the recordset to complete successfully. No?

    Mark 

    In situation like these, your code looks fine. But there is a problem of timing between Access, your Code and Windows. Have you ever copy a file and immediately tried to open the file? (I do). Chances that it would not open are rarer this days with newer CPU but there are chances that Windows might not respond to your code and Access might not refresh the linked table connection.

    Hence, the timing between copying (FileCopy) the file which is handled by Windows have yet to return the Handle (Read and Write) to Access and your Code (db.OpenRecordset - the second time). In this situation, you would certainly encounter a problem. Problem of this are rare but still it can happen.

    That said, there are also issues with Anti-Virus or File protection software that might delay the file for Read/Write after you had copied thus, impeding the timing further and in the process causing problem with your code.

    The CurrentDb object "db" is not aware of the changes yet to the new file, it needs to refresh the object before you can write to the file. So, if Access did not or could not refresh the link to the file during the process (file being replaced), it would just simply stop without any errors and will re-attempt to refresh again later. In short, you have a timing conflict between Read/Write, you had a temporary disconnection to the linked tables. Or simply put, in the process of copying the file and at the same time trying to Read/Write a file will cause problems.

    [Quote]
    AccessVandal ... are you saying there would be a problem with this? ... that the table link would need to be refreshed, plus the BE itself would need to be opened?
    [\Quote]

    My suggestion to use the "OpenDatabase" method, at least it has a time delay to allow Windows to return the Read/Write handle to Access. In that way, you could at least avoid the timing issue. Or use the API (not that I know of at the moment) in the FileCopy to handle the problem.

    I cannot say that you could refresh the linked table as it might be cache in memory, with any luck the update could work. That is, if it was written to the cache and Access will write into the table later unless Access tried to refresh the temporary disconnected file during this attempt. It could fail during this attempt but if the timing is right, it would not fail.

    Friday, September 09, 2011 1:26 AM
  • Thank you, AccessVandal, for your very clear explanation. Sure wouldn't hurt to try it. Although now it's only that last, older laptop that's throwing either one or the other of the messages. But, who knows what tomorrow will bring. Maybe the message(s) will start popping up on one of the desktops. Hasn't happened yet, but ...

    Mark

    Friday, September 09, 2011 1:48 AM
  • Good to hear. At least you could now understand what goes behind the problem you have encountered or might encounter in the future.
    Sunday, September 11, 2011 3:41 AM
  • AccessVandal

    Well, "tomorrow" happened sooner than I thought :) .
    Couple hours after my post, I started getting the messages on an Access 2002 desktop, which previously has never produced either one of those messages. Mostly, it was the message implying that Windows could not find the target specified in the shortcut -- even though the utility database and then the FE had already opened prior to the message being displayed -- obviously showing that Windows had found the target specified in the shortcut. Occasionally, the message would be the one about "There was a problem sending the command to the program".

    I have since redesigned the opening activities of the FE. Previously, the autoexec macro of the FE opend the MainMenu form. This form's On Load event carried a bunch of code to set up the environment depending upon which version of Access was on the computer and which BE the FE was linked to. One of the procedures in this opening code set it's own "db" variable "= CurrentDb". I'm wondering if this "db = CurrentDb" was conflicting with the utility database's variable of the same name (see above code). [I plan to rename the "db" variable in the FE's code tomorrow to see if that eliminates the messages on that 2002 desktop. I'm testing this just for the sake of knowing.]

    Whether there's been a conflict in having two "db" variables -- even though they're in two separate databases -- or whether the opening code of the FE was too much and was interferring with the procedures of the utility database, I've decided to try a different approach.

    My redesign is to now have the FE's autoexec macro open a StartUp form, whose only OnLoad code is to close the Navigation Pane if SysCmd(acSysCmdAccessVer) > 11. The form's Timer Event is set for 2500 mls and is as follows:

    Private Sub Form_Timer()
    DoCmd.Close acForm, "frmStartUp", acSaveYes
    DoCmd.OpenForm "MainMenu"
    End Sub

    I'm hoping that this delay between opening the FE and running it's setup code will allow sufficient time for Windows/Office/Access to complete its tasks and to close the utility database smoothly. I'm also hoping that this will succeed without any extra opening of a database (NewDb) or recordset (NewRst) after the Else statement in the utility database's code (see above). Perhaps including those extra steps will ultimately prove necessary, but from a pure logic point of view they don't  fit in with the flow of the If ... Then ... Else statement (at least in my mind).

    ... will post back findings.
    Mark

    Sunday, September 11, 2011 5:17 PM
  • Mark,

    Thanks for the code listing.

    Early on in your code, you defer error handling with "On Error Resume Next". 

    You don't resume error handling with "On Error Goto 0", or "On Error GoTo Form_Open_ErrorHandler".

    Therefore, can you be sure your table links are set up correctly and refreshed properly? Any errors are ignored.

    I have some other comments, but cannot complete them just now.

    Geoff

    Sunday, September 11, 2011 10:59 PM
  • mdavisfps wrote:

    I have since redesigned the opening activities of the FE. Previously, the autoexec macro of the FE opend the MainMenu form. This form's On Load event carried a bunch of code to set up the environment depending upon which version of Access was on the computer and which BE the FE was linked to.

    I haven't followed this thread completely but I'm wondering if the
    MainMenu form is bound to any tables, Record Source, or has any
    listboxes or comboboxes bound to tables, Control Source?  That could
    cause interesting things to happen if you relink tables while those are
    still open.

    I prefer to have all my relinking logic in separate modules run before
    any forms are open. Partially in case some future developer not
    realizing interesting things can happen does stuff on the main menu.

    Tony


    Tony Toews, Microsoft Access MVP
    Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
    Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
    For a convenient utility to keep your users FEs and other files   updated see http://www.autofeupdater.com/

    Monday, September 12, 2011 3:36 AM
  • Hi Tony,

    The utility database DOES relink two tables to the FE, but this occurs prior to the FE being opened.

    The MainMenu of the FE is unbound, but there is a subform which is bound to a local table in the FE: MyDefaultsTable_Region. This subform displays the current version number of the FE.
    Also, the OnLoad event of the MainMenu opens a hidden form bound to a small table in the BE to maintain a constant link to the BE.
    I tried a redesigned FE to delay the opening of any bound forms, please see below (or my previous post).

    Interesting results this morning on the WinXP Prof SP3, Access 2002 desktop which had started showing the troublesome messages on Friday ...

    Since I had variables of "db = CurrentDb" and "rst" in the utility database, plus variables of the same name in the opening code of the FE, I tried renaming the variables in the utility database to dbNew and rstNew. This did not eliminate the (unpredictable) appearance of a message.

    I then tried my redesigned FE, where it opens a startup form and after a 2-1/2 second pause, then opens the MainMenu (with all of its start up code), but this also did not fix the problem.

    I went back to the simpler FE, the one without the extra startup form and the 2-1/2 second pause. MainMenu is opened by an autoexec macro.

    I did two trials of double-clicking the autoFEupdater icon, waiting for the FE to open, allowing sufficient time for the message to appear, closing the FE, waiting approximately 5 seconds and then double-clicking the FEupdater icon again.
    I began each trial with a later version number on the server in order to force a FileCopy. As you can see, the presence of the troublesome message(s) is very inconsistent.

    "Yes" means that a message appeared -- typically about 5 seconds or so after the FE opens. In each instance, the utility database has closed and the FE functions normally. Most of the time, the message was that Windows could not find the target specified in the shortcut -- i.e., the utility database, even though the utility database did in fact open. Once or twice the message was that "There was a problem sending a command to the program". But, again, it appears that everything completed in spite of that message.
    "No" means that no message appeared.

    Trial A:
     1. No (no message and FE was successfully updated with newer version)
     2. No (MyVersion = LatestVersion; no file copied; ditto for remaining tests)
     3. No
     4. No
     5. Yes
     6. No
     7. Yes
     8. No
     9. No
    10. Yes
    11. No
    12. No
    13. No
    14. Yes
    15. No

    Trial B:
     1. No (no message and FE was successfully updated with newer version)
     2. No (MyVersion = LatestVersion; no file copied; ditto for remaining tests)
     3. No
     4. No
     5. No
     6. No
     7. No
     8. No
     9. Yes
    10. Yes
    11. No
    12. No
    13. No
    14. No
    15. Yes
    16. No

    I did a third trial after adding back in the opening of rst and then closing rst immediately after ExitHere (see code below), with the results very similar to Trial B -- perhaps one less "Yes" over 20 tests.

    BTW, no other programs were open during these tests, except (as far as I know) those provided by IT -- Symantec Endpoint Protection, PC-Duo Remote Control, Adobe Acrobat 9.

    I think for my next test, I'll spray WD-40 into the vents of the cpu. :)

    Mark

    Option Compare Database
    Option Explicit
    
    Private Sub Form_Open(Cancel As Integer)
    'THIS CODE IS FOR THE ROMNEY ICON
    'Server Gateway: 10.57.3.1; Server Access folder: 10.57.3.2
    
    'Note: When copying this code to the other autoFEupdater,
    'manually replace the above values as appropriate.
    'Do a Find/Replace for "fpsapps"/"Peteapps" as appropriate,
    'starting at this point [here] and only downward to the end.
    'Manually change the value in the first msgbox below as appropriate.
    'All other values remain the same.
    
    'Important: Do not just rename this database ...
    'the initial connect strings for the linked tables
    'will be incorrect.
    
    Dim obj As Object, strWQL As String
    Dim CurrentDefaultGateway As String
    Dim MyDefaultServer As String
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim tdf As TableDef
    Dim Tdfs As TableDefs
    Dim MyTableLinks As String
    Dim CurrentFE As String '... always "C:\Access97\fpsapps.mdb"
    Dim NewestFE As String
    Dim SharedFolder As String
    Dim MyVersion As Long
    Dim LatestVersion As Long
    Dim DefaultStaffPerson As Long
    Dim CmdToOpen As String
    '*********
    
    'For later versions, close the Navigation Pane
    
    If SysCmd(acSysCmdAccessVer) > 11 Then
    DoCmd.RunCommand acCmdWindowHide
    Else 'earlier version so do nothing
    End If
    
    'Set the values of CurrentFE and CmdToOpen
    
    CurrentFE = "C:\Access97\fpsapps.mdb"
    'CmdToOpen = """" & SysCmd(acSysCmdAccessDir) & "Msaccess.exe"" " & CurrentFE
        'this also works? > CmdToOpen = SysCmd(acSysCmdAccessDir) & "Msaccess.exe " & CurrentFE
     CmdToOpen = SysCmd(acSysCmdAccessDir) & "Msaccess.exe " & CurrentFE
    
    
    'Check to see if the FE is already open
    
    If Len(Dir("C:\Access97\fpsapps.ldb")) > 0 Then
    MsgBox "The Romney database is already open."
    DoCmd.Quit
    Exit Sub
    End If
    
    'Determine if the user is plugged into one of our servers and,
    'if so, which one
    
    'WMI method
     On Error Resume Next
    With GetObject("winmgmts://./root/cimv2")
      strWQL = "Select * From Win32_NetworkAdapterConfiguration Where IPEnabled=True"
      ' there might be multiple network adapter configurations
      For Each obj In .ExecQuery(strWQL)
         CurrentDefaultGateway = obj.DefaultIPGateway(0)
      Next
     End With
    
    Select Case CurrentDefaultGateway
    Case "10.57.3.1" '... connected to the Romney server
      SharedFolder = "\\10.57.3.2\Access\"
      Case "10.57.2.1" '... connected to the Moorefield server
      SharedFolder = "\\10.57.2.2\Access\"
    Case Else '... not connected to either server
    GoTo ExitHere
    End Select
    
    'Determine the current version number of the local FE
    Set db = CurrentDb()
    Set Tdfs = db.TableDefs
    Set rst = db.OpenRecordset("MyDefaultsTable_Region", dbOpenSnapshot)
    MyVersion = rst!Version
    
    'While MyDefaultsTable_Region is open, determine whether
    'it is linked to the server or to its local BE
    MyTableLinks = rst!LinkedTo
    rst.Close
    
    'Determine the use's default server, as stored in their local FE
    Set rst = db.OpenRecordset("MyDefaultsTable_Server", dbOpenSnapshot)
    MyDefaultServer = rst!DefaultServer
    rst.Close
    
    'If the user is connected to their home server, proceed;
    'otherwise, open their existing FE
    
    If MyDefaultServer = CurrentDefaultGateway Then
    GoTo ContinueHere
    Else
    GoTo ExitHere
    End If
    
        
    ContinueHere:
    
    '... it's been determined that the user is connected to their home server
    
    '... now determine appropriate path to latest FE stored on the server
    
        Select Case SysCmd(acSysCmdAccessVer)
        Case 8    'Access 97
            If MyTableLinks = "Cdrive" Then
                NewestFE = SharedFolder & "_NewFE_Home_97\fpsapps.mdb"
            Else
                NewestFE = SharedFolder & "_NewFE_Office_97\fpsapps.mdb"
            End If
        Case 10, 11   'Access 2002
            If MyTableLinks = "Cdrive" Then
                NewestFE = SharedFolder & "_NewFE_Home_2002\fpsapps.mdb"
            Else
                NewestFE = SharedFolder & "_NewFE_Office_2002\fpsapps.mdb"
            End If
        Case 12   'Access 2007
            If MyTableLinks = "Cdrive" Then
                NewestFE = SharedFolder & "_NewFE_Home_2007\fpsapps.mdb"
            Else
                NewestFE = SharedFolder & "_NewFE_Office_2007\fpsapps.mdb"
            End If
        Case 14   'Access 2010
            If MyTableLinks = "Cdrive" Then
                NewestFE = SharedFolder & "_NewFE_Home_2010\fpsapps.mdb"
            Else
                NewestFE = SharedFolder & "_NewFE_Office_2010\fpsapps.mdb"
            End If
        End Select
        
        'Refresh the link to the server's DefaultsTable_Region
        For Each tdf In Tdfs
        If Len(tdf.Connect) > 0 Then
            Select Case tdf.Name
                Case "ServerDefaultsTable_Region"
                    tdf.Connect = ";DATABASE=" & NewestFE
                    tdf.RefreshLink
                Case Else
                    tdf.Connect = ";DATABASE=" & CurrentFE
                    tdf.RefreshLink
            End Select
        End If
        Next 'Goto next table
        
    'Determine the latest version number stored on the server
    
    Set rst = db.OpenRecordset("ServerDefaultsTable_Region", dbOpenSnapshot)
    LatestVersion = rst!Version
    rst.Close
    
    'If appropriate, copy the newer FE to "C:\Access97\fpsapps.mdb"
    
    If LatestVersion > MyVersion Then
    
        'There is a newer version, but before copying
        'get the default staff person ID stored on the current FE
    
        Set rst = db.OpenRecordset("MyDefaultsTable_StaffPerson", dbOpenSnapshot)
        DefaultStaffPerson = rst!StaffID
        rst.Close
        
    FileCopy NewestFE, CurrentFE
    DoEvents
    
    'Reset the default StaffID
        Set rst = db.OpenRecordset("MyDefaultsTable_StaffPerson", dbOpenDynaset)
            With rst
            .Edit
            !StaffID = DefaultStaffPerson
            .Update
            End With
            rst.Close
        GoTo ExitHere
        
    Else    '... there is not a newer version on the server
    GoTo ExitHere
    End If
    
    ExitHere:
    
    '?? added so that Else statement will be similar to If statement
    Set rst = db.OpenRecordset("MyDefaultsTable_StaffPerson", dbOpenSnapshot)
    DefaultStaffPerson = rst!StaffID
    rst.Close
    
    'clean up ...
    On Error Resume Next
    Set db = Nothing
    Set Tdfs = Nothing
    Set rst = Nothing
    
    'Open the local FE ...
    
    Shell CmdToOpen, vbNormalFocus
    DoEvents
    
    'Close this autoFEupdater ...
    DoCmd.Quit
    
    End Sub
    

     

     

     

    Monday, September 12, 2011 2:23 PM
  • AccessVandal,

    I understand your good point about timing when using FileCopy. My hunch is that FileCopy suspends code execution until the destination file has been written to disc.  I tested this using the code below to copy a 200 Mb file. The beep sounded 28 seconds after the code started and the Immediate window showed that the destination file existed. I realise this is only one test. 

    I suspect that FileCopy is a wrapper for a Windows API function (like CopyFileEx or SHFileOperation); that it uses a callback function to check on progress; and only releases control back to VBA when the copying process has completed.

    The Microsoft documentation on the FileCopy command is silent on this issue.  If FileCopy causes a timing problem, then perhaps DoEvents after it would give Windows a chance to write the cache, but I think it's probably unnecessary. As a file-copying alternative, there's always the Microsoft Scripting Runtime library or, as you said, the Windows API functions.

    Mark,

    When I tried it a couple of days ago, I thought there was a problem using an IP address in the connection string of a linked table in Access 2002 and this might be the source of the problem. Today, I was able to use 127.0.0.1 (my computer) successfully at the user-interface and programmatically. However, I still think you need to resume error handling after the WMI code block. 

    Good luck with the WD40!

    Geoff

     

    Option Compare Database
    Option Explicit
    
    Private Declare Function timeGetTime Lib "winmm.dll" () As Long
    
    Private Sub CopyVeryLargeFile()
    
        '   Copy a very large file using VBA's FileCopy statement.
        '   FileCopy suspends code execution until file has been copied.
        '   Destination file exists after FileCopy finishes.
        
        '   File size is 200 Mb:
        Const strcFrom As String = "C:\DATA2\AVIRA RESCUE SYSTEM\20110321 rescue_system-common-en.iso"
        Const strcTo As String = "C:\DATA\TEMP\20110321 rescue_system-common-en.iso"
    
        Call CopyFile(strcFrom, strcTo)
    
    End Sub
    
    
    Private Sub CopyFile(strFrom As String, strTo As String)
    
        Dim lngStartTime As Long
        
        '   Get start time in milliseconds:
        lngStartTime = timeGetTime()
        
        '   Copy the file:
        FileCopy strFrom, strTo
        
        '   See if destination file exists:
        If Len(Dir(strTo)) > 0 Then
            Debug.Print "File Exists"
        Else
            Debug.Print "File does not exist"
        End If
        
        '   Send time taken to Immediate window:
        Beep
        Debug.Print "Elapsed time:", timeGetTime() - lngStartTime
    
    End Sub
    
    

     

    Monday, September 12, 2011 11:29 PM
  • Thanks, Geoff. That's a neat bit of code for timing the FileCopy process. I still don't know what's going on with my process. Since the start of this thread, the only consistent finding is that I don't get the message at all if FileCopy takes place -- if LatestVersion > MyVersion. In the two trials I posted above, all the messages occurred when there wasn't a file to copy (no newer version on the server). I also really can't understand why running the process without a newer version on the server (no FileCopy involved) completes satisfactorily (i.e., no message) seven times in a row (Trial-B), throws the message for the next two times, then no message for the next four times, and so forth. No rhyme or reason that I can see.

    At the end of the day, today, I placed the above code on a Win XP, Access 2007 desktop -- which previously had never thrown the message -- and it threw the message every single time that I ran the code. Everything opened and closed, but one or the other of those messages appeared each time. Earlier in the day, I had accidently deleted the target folder under the network share and had to recreate it. When I got to this Access 2007 desktop, I did not log on, but took over from the user who had been logged on for the whole day. Maybe permissions to that recreated folder on the server had not been totally refreshed for that particular desktop -- no logoff/logon -- and there was a delay in obtaining permission each time, which resulted in the message being thrown?? I'll log on in the morning and see if it performs differently. I'm just throwing out stuff here, I really don't know how permissions and network shares work on a domain.

    The Win XP, Access 2007 laptop that I've been doing testing on has not been throwing either of those messages. It's using the same utility database, but it gets its newer version from a different folder on the share because it's FE is linked locally, while the desktops are linked to the server. (I have FEs linked to the local BE stored in one folder and FEs linked to the server stored in another folder; both under the same network share). On the laptop, however, I have not opened and closed the FE more than three or four times in a row. Perhaps if I did it more, it also would throw an occasional message -- like the 2002 desktop in Trial-A and Trial-B noted above.

    I guess a good test might be to unplug a computer from the network (like a laptop when used at home) and run the code consecutively many times and see if the message pops up. The code would always flow to the ExitHere statement -- open the existing FE/close the utility database -- since WMI woul never show it as being linked to one of our servers.

    Mark

    Tuesday, September 13, 2011 12:54 AM
  • Mark,

    > Since the start of this thread, the only consistent
    > finding is that I don't get the message at all if
    > FileCopy takes place -- if LatestVersion > MyVersion.

    As the above is the only consistent thing you've observed, how about the following test?

    What happens if you rem out the "If" statement and all the code from "Else" to "End If" — leaving the code between "If" and "Else"?  The FE would then be updated when it doesn't need to be, but does that matter if it then works (other than it's annoying not to know what's going on with the code between "Else" and "End If")?

    And then there's still that pesky error handler issue I've already mentioned. Is there a reason you don't include the following after the WMI code block? 

    On Error GoTo 0 

    Geoff

    • Edited by Geoff_G Tuesday, September 13, 2011 8:51 AM
    Tuesday, September 13, 2011 8:48 AM
  • Mark,

     

    This part of the code, which I cannot understand why you need to re-link the Tables since it was already linked (except the part I understood on “ServerDefaultsTable_Region”). I hope you could enlighten me.

     

    ·                                                                                                                                                                                                                                                                                         'Refresh the link to the server's DefaultsTable_Region
    ·                                                                                                                                                                                                                                                                                             For Each tdf In Tdfs
    ·                                                                                                                                                                                                                                                                                             If Len(tdf.Connect) > 0 Then
    ·                                                                                                                                                                                                                                                                                                 Select Case tdf.Name
    ·                                                                                                                                                                                                                                                                                                     Case "ServerDefaultsTable_Region"
    ·                                                                                                                                                                                                                                                                                                         tdf.Connect = ";DATABASE=" & NewestFE
    ·                                                                                                                                                                                                                                                                                                         tdf.RefreshLink
    ·                                                                                                                                                                                                                                                                                                     Case Else
    ·                                                                                                                                                                                                                                                                                                         tdf.Connect = ";DATABASE=" & CurrentFE
    ·                                                                                                                                                                                                                                                                                                         tdf.RefreshLink
    ·                                                                                                                                                                                                                                                                                                 End Select
    ·                                                                                                                                                                                                                                                                                             End If
    ·                                                                                                                                                                                                                                                                                             Next 'Goto next table

     

    I see two problems here.

     

    1. “Len(tdf.Connect) > 0”, why do you want to loop thru all linked tables where you could just do it with this tdf.Name = ”ServerDefaultsTable_Region” without additional lines of IfThen or even the Case Select? The second problem of Len() function is the Null condition on the connection string. To avoid such problem, here is what you need… Len(tdf.Connect & “”)…..this should avoid the error that you might receive since you’re going use “On Error Resume Next”. Remember the TableDefs/Collection goes thru the System Objects as well.

     

    1. The Case Else, there is no point of re-linking the tables again as it was already linked. It not like you have replaced the NewestFE file, so you don’t need to do this. The other is the non-linked tables where your “On Error Resume Next” may attempt to do a connection to the CurrentFE which of course will fail. It is possible that VBA simply goes to this line and tried to execute the code. Perhaps it why you’re getting errors in one trial but not in the second or third and so on….?

     

    Do note however that the sequence of the TableDefs list may not be in order.

     

    An alternative code instead of Len() I would use…..

    If tdf.Connect <> “” then

    Since all your linked tables have a string in the connection, you can avoid/skip non-link table and System tables. But in your case, the tdf.Name will do just fine.

     

    You should try to re-link to the newestFE once the code copied the file (after the line FileCopy) and change the record in the linked table.

     

    Geoff,

     

    The problem is not only the “FileCopy” function and nothing to do with file size. I don’t know if it failed to copy the file, does it return a value? Seems like to me, Windows needs to unlock the file for Read/Write. Timing of this may vary. I have many incidents of files can’t be open when it has finish copying even in DOS, Win95/98 before. Just by simply waiting awhile for OS/Windows to finish whatever it doing, only than I can open a file.

     

    As for the Server folders, whenever a new Folder is created on the Server, I cannot seem to view the folder over the network most of the time. I have to wait until the server OS or the desktop OS to refresh the connection. That would take about 15 minitues to an hour. So far, I just simply re-start the desktop computer, which was much faster. Perhaps the OS is still in the process to adding security settings?

     

    As for the files, I have no problems yet.

     

    Although Mark did not indicate whether the Anti-Virus had anything to do with the problem or was configured to scan a new created file or file being copied over and what was the Heuristics setting level. I hope it is not the case of Anti-Virus or any file protection might be the cause.

    Tuesday, September 13, 2011 10:05 AM
  • Morning,

    I don't know why it took me so long to try it, but simply commenting out the DoCmd.Quit at the end of the routine results in zero messages displayed after 20 consecutive openings and closings ...

    'clean up ...
    On Error Resume Next
    Set db = Nothing
    Set Tdfs = Nothing
    Set rst = Nothing

    'Open the local FE ...

    Shell CmdToOpen, vbNormalFocus
    DoEvents

    'Close this autoFEupdater ...
    'DoCmd.Quit    ... COMMENTED OUT

    End Sub

    '************

    Of course, this leaves the utility database open behind the FE in the cases of either the computer is not connected to one of the servers (code goes to ExitHere), or the computer is connected, but there is not a newer version of the FE on the server (code also goes to ExitHere). So, when the user closes their FE, there's the utility database. Maybe I'll just put a label on the splash screen of the utility database: "Close me too!" :)

    Maybe I could have the FE close the utility database, instead of the utility database trying to close itself? Just a thought.

    Still wondering, however, how it can succeed 2/3 of the time without a message and the other third of the time succeed, but throw out one of those messages.

    Incidently:
    When I logged on to the WinXP/Access 2007 desktop computer this morning, it was only throwing out the message at the same rate as Trials A and B for the WinXP/Access 2002 desktop, instead of every single time. I think that particular issue had to do with refreshing the permissions for that computer for the network share's subfolder that I had accidently deleted and then recreated yesterday. At least, I think this is what was going on.

    Also, it didn't help to take the WinXP/Access 2002 desktop offline by unplugging the ethernet cable and rebooting. The messages occurred at about the same rate.

    AccessVandal:

    >This part of the code, which I cannot understand why you need to re-link the >Tables since it was already linked (except the part I understood on >“ServerDefaultsTable_Region”). I hope you could enlighten me.

    Yes, you're right, the only table I really need to relink is “ServerDefaultsTable_Region”. I just grabbed the relinking routine I've always used. The syntax is simply from whatever example I had come across a long time ago. 

    >You should try to re-link to the newestFE once the code copied the file (after the >line FileCopy) and change the record in the linked table.

    I'll look at that. Thanks.

     

    > I hope it is not the case of Anti-Virus or any file protection might be the cause.

    At this point, I don't think so.

     

    I'm thinking of just having the utility database do its thing in terms of identifying whether the user is logged on to the appropriate server, or not, and if they are, determining if there's a newer FE on the server. Whatever the outcome, the utility database opens the FE, followed by End Sub. Somewhere in the opening code of the FE, the FE closes the utility database. I haven't looked yet to see how this is done, but will search later. (Regular job responsibilities taking over right now).

     

    Mark

     

    Tuesday, September 13, 2011 1:27 PM
  • Mark,

    I had wondered whether DoCmd.Quit should be Application.Quit.

    AccessVandal,

    The point about copying a large file was to show that code execution had been suspended by FileCopy.  It took 28 seconds before the beep sounded.  If code execution had not been suspended, the beep would have sounded immediately, in which case, there really would have been a timing issue. That was the point.

    Geoff

    Tuesday, September 13, 2011 9:27 PM
  • Geoff & AccessVandal,

    First ... Geoff, I'm sorry, I seem to keep missing some of your posts. Don't mean to; just not quite used to this forum's format, I guess. I did use an error handler in one of my tests, with a msgbox to display the error number and description. No msgbox popped up -- in spite of the troublesome message being thrown out after the utility closed and the FE opened. If there was an error, it should have produced a message box and ended the sub before reaching DoCmd.Quit.

    AccessVandal ... I also started wondering about Application.Quit, instead of DoCmd.Quit. That'll be my next test. I also thought of inserting something after the opening of the FE and before DoCmd.Quit (or Application.Quit) which maybe focuses the attention more fully back to the utility database ... either another db = CurrentDb, or Me!tabTextBox.SetFocus -- something like that and then close the utility.

    Glad to get a confirmation on the need to log off/ log back on (or reboot) when one of the subfolders on the share has changed.

    I've started searching how to close another open database -- i.e., the FE closing the utility database, instead of the utility closing itself, but so far nothing seems entirely appropriate to what I'm trying to do. I'll keep looking.

    I appreciate the interest and effort from those that have posted to this thread. I'd much rather be reading the neat code solutions that you provide for other visitors to this site!

    Mark

    Tuesday, September 13, 2011 10:18 PM
  • On my way to work to test Application.Quit, but before I forget ...

    Interesting difference in Access 97 between DoCmd.Quit and Application.Quit.
    I placed a button for each on an unbound form. When DoCmd.Quit is clicked, there's a millisecond ghost image of Access as it closes. Almost like it's going to minimize before the application closes. But it's really brief, just a flicker. With Application.Quit, however, the application disappears immediately. Something different is going on -- at least in Access 97. I couldn't visually catch any difference in 2003 or 2007. All three versions use DoCmd.Quit for a button created by the Wizard for Applicatiion -- Quit Application.

    Mark

    Wednesday, September 14, 2011 8:30 AM
  • Success!

    Using Application.Quit, instead of DoCmd.Quit, does the trick. Did 20 tests on a 2002 desktop, 2007 desktop and a 2007 laptop. Zero messages. Didn't need to open/close any extra recordsets, or do anything else special.

    Final code at the end reads:

    ExitHere:

    'clean up ...
    On Error Resume Next
    Set db = Nothing
    Set Tdfs = Nothing
    Set rst = Nothing

    'Open the local FE ...

    Shell CmdToOpen, vbNormalFocus
    DoEvents

    'Close this autoFEupdater ...
    Application.Quit

    End Sub

    Thanks, everyone. Now I don't have to nag staff in two different offices to get the latest FE.

    Mark

    Wednesday, September 14, 2011 12:46 PM
  • Just a quick update ...
    Although replacing DoCmd.Quit with Application.Quit seemed to eliminate any message from popping up on any of the computers I logged onto, when I set up a shortcut to the utility database for another user logged onto her WinXP/Access 2007 computer, she received the message that there was a problem in locating the target specified in the shortcut every single time. But just like every other instance of this message popping up, the FE opened and the utility database closed. When she logged out and logged back in, the message popped up approximately 60% of the time.

    While she is still logged on, if she opens the utility database by double-clicking the filename in Windows Explorer, no message appears during 20 consecutive trials. This also works if I copy the utility database to her desktop, rather than creating a shortcut.

    What I should have tried (but didn't) was to have her completely restart her computer. Perhaps that would have resolved any permissions issue for the shortcut. I suspect that during runtime this OS issue (resolving permissions for the shortcut) was interferring with the Access command to close the utility database.

    Anyhow, corporate IT is supposed to get back to me regarding permissions for this particular user, particularly for new files placed in her local database folder and/or shortcuts created to those new files.  Shortcuts pointing directly to her FE have never (apparently) been a problem. Then again, her regular FE does not involve the more complex processing issues of the utility database/FE scenario. Perhaps, though, a complete restart resolves the shortcut and message popping up issue.

    Regardless, changing to Application.Quit eliminated any messages when I was testing under conditions where I was logged on. So, that seems to be at least part of the solution.

    Also, I've checked with the laptop users where I've installed this utility database and, so far, they have not encountered any messages. Updates of the FE occur as desired and the default staff ID is reinserted, and when there is not a newer FE their exisitng FE opens without any message displayed.

    Mark

    Friday, September 16, 2011 8:30 AM