Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Delta, Br. Columbia
    Posts
    115
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Question Win7/Access 2003 Backend link problems

    I have a very strange scenario. I recently implemented some changes on a clients network where there are 4 users. Now, on only two of the four computers, the link to the back end database is lost overnight. They shutdown their stations overnight but not the server. Each morning these two users have to relink to the back end.

    They are all running the same version of software. The database changes included compressing the back end on close; un-checking "Ignore DDE requests" to try and solve a problem with the msg 'There was a problem sending...'; and moving some temporary tables to the front end. The changes to the front end included solving some of the issues raised by FMS Total Access Analyzer (option explicit etc)

    Does anyone have any ideas?

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,482
    Thanks
    3
    Thanked 41 Times in 41 Posts
    These tend to be rather thorny problems to debug. First, are the back-end tables in an Access .MDB file, or are they in SQL Server, mySQL, dBase, or other database engines? Also, if they are in Access (or dBase), are you linking to a mapped drive, and are all of the mapped drive settings the same. I presume all users have their own copy of the front-end, in which case you could try turning off the compact on close and see if that is the cause. (If you are using a single copy of the front-end, that would very much point to issues with the drive mapping for various users, but that approach is not recommended for several reasons.)
    Wendell

  4. #3
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Delta, Br. Columbia
    Posts
    115
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks for the response. It is an Access .MDB file and I have tried turning off Compact on Close to no avail. Each user has their own copy of the front-end. However reading your response I suspect that the issue is in the drive mapping as that was a problem at an earlier installation. I will visit them and see what I can find out.

  5. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,531
    Thanks
    0
    Thanked 23 Times in 23 Posts
    If it is a drive mapping problem, then change it to UNC convention.

  6. #5
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Delta, Br. Columbia
    Posts
    115
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Unfortunately remapping the drive did not work. The drive is mapped to R:, path = \\server\ar_data\ which should be OK. When trying to load the front end the message states that R:\back-end.mdb is not a valid path. I tried removing the connection to the drive and then remapped it just in case. No luck.

    I also have a form built for the user that will automatically relink in the event it is lost; and that also does not work. It uses a stored path that again agrees to above = R:\back-end.mdb

    I then use the Linked Table Manager to manually relink to exactly the same path and file and everything works fine including my form if I try to rerun it after the manual linking.

    Baffled!

  7. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,482
    Thanks
    3
    Thanked 41 Times in 41 Posts
    Have you tried the UNC path rather than using a mapped drive, as Pat suggested? I've never seen something that would link using the Linked Table Manager, and not link programmatically, but there's always a first time. Does it work until the workstation is rebooted, or does it fail as soon as you exit the front-end and try to restart it?
    Wendell

  8. #7
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Delta, Br. Columbia
    Posts
    115
    Thanks
    1
    Thanked 0 Times in 0 Posts
    It works until the station is rebooted. The strange thing is that it has worked perfectly from the first time they called me in at the beginning of February. All I did then was to give each user their own copy of the front-end and have it compress on close. I also compressed the back-end as it was very bloated. This time I made the changes I identified at the beginning of this thread and now we have the problem.

    I am unclear how I would use the UNC path to set up the link the first time through

    Tx

  9. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    5,916
    Thanks
    190
    Thanked 719 Times in 655 Posts
    Peter,

    Here's some code I use to automatically relink the back end every time the user opens the Front End. I know it may be a bit excessive but the tables in the Back End are frequently modified as new requirements emerge and I found this lets me make changes to both ends and load the files remotely and never have to adjust each copy of the Front End. The function to get the path is necessary to make the transition from development on my desktop or laptop seemless to the actual location of the back end in the production environment and adjust for individual users. I hope you can understand this but if not feel free to ask. HTH
    Code:
    Public zDBPath        As String
    Public zStatusMsg     As String
    Public lTimerInterval As Long
    Public Const zCodeVersionNo = "7.0"
     
    '                            +------------------+                 +----------+
    '----------------------------|  ReLinkTable()   |-----------------| 01/11/11 |
    '                            +------------------+                 +----------+
    'Called by: Macro - AutoExec
    'Calls    : [Utilities] zGetDBPath()
    '           [Utilities] HideDBWinsdow()
    'Globals  : lTimerInterval
    '           zStatusMsg
    
    Function ReLinkTable()
    
       Dim zDBFullName    As String
       Dim zBEDBFN        As String
       Dim zTableName(12) As String
       Dim zUserName      As String
       Dim iTblCnt        As Integer
       
       GoTo StartLinking
    
    FileDoesNotExist:
    
       If Err.Number = 7874 Then
         Resume Next
       Else
         MsgBox "Error No: " & Err.Number & vbCrLf & _
                "Description: " & Err.Description
       End If
    
    StartLinking:
    
       zTableName(0) = "Docks"
       zTableName(1) = "Lots"
       zTableName(2) = "Owners"
       zTableName(3) = "PhoneDir"
       zTableName(4) = "StorageLots"
       zTableName(5) = "tblAuxNumbers"  '*** Last table in ARB_be.mdb       ***
       zTableName(6) = "Builders"       '*** Start of tables in ARBReqs.mdb use index in If iTblCnt = below***
       zTableName(7) = "Letters"
       zTableName(8) = "ARBMembers"
       zTableName(9) = "ARBAssignments"
       zTableName(10) = "Requests"
       zTableName(11) = "RequestTypes"
       
       zDBPath = zGetDBPath()
       zUserName = Environ("USERNAME")
       
       HideDBWindow IIf(UCase(zUserName) = "BRUCE", False, True)
       
       If zDBPath = "Error" Then
         MsgBox zUserName & ": is not an authroized user!", _
                    vbOKOnly + vbCritical, "Error: User Not Authorized"
         ExitDB
       End If
       
       Forms("Switchboard").Caption = _
          Forms("Switchboard").Caption & "  " & zDBPath
       zBEDBFN = "ARB_be.mdb"
       zDBFullName = zDBPath & zBEDBFN
      
       For iTblCnt = 0 To UBound(zTableName) - 1
       
          If iTblCnt = 6 Then            '*** Switch back end DB files ***
            zBEDBFN = "ARBReqs_be.mdb"
            zDBFullName = zDBPath & zBEDBFN
          End If
          
          On Error GoTo FileDoesNotExist
          '*** Delete TableDef from FRONT end DB
          DoCmd.DeleteObject ObjectType:=acTable, ObjectName:=zTableName(iTblCnt)
       
          '*** Copy TableDef from BACK end DB to FRONT end DB - Keep in sync!
          DoCmd.TransferDatabase TransferType:=acLink, _
                                 DatabaseType:="Microsoft Access", _
                                 DatabaseName:=zDBFullName, _
                                   ObjectType:=acTable, _
                                       Source:=zTableName(iTblCnt), _
                                  Destination:=zTableName(iTblCnt)
          On Error GoTo 0
          
       Next iTblCnt
                                 
        zStatusMsg = "Tables have been Re-Linked"
        lTimerInterval = 3000    '*** 3 Seconds ***
        DoCmd.OpenForm "frmStatusMsg", acNormal
        Application.SetOption "Themed Form Controls", False
        StdMenuToggle "False"
        
    End Function    'ReLinkTable()
    
    '                          +---------------------+                 +----------+
    '--------------------------|    zGetDBPath()     |-----------------| 05/05/11 |
    '                          +---------------------+                 +----------+
    'Called by: [RelinkTables] RelinkTable()
    'Returns  : STRING = Path to backend DB based on user name and machine name.
    
    Public Function zGetDBPath() As String
    
       Dim zUName    As String
       Dim zCompName As String
       
       zUName = Environ("USERNAME")
       Select Case zUName
         
         Case "Bruce"
             On Error Resume Next
             zGetDBPath = Dir$("\\WD-NETCENTER\Shared Files\ARB Files", vbDirectory)
             If zGetDBPath <> "" Then
               zGetDBPath = "\\WD-NETCENTER\Shared Files\ARB Files\"
             Else
               On Error GoTo 0
               zCompName = Environ("COMPUTERNAME")
               Select Case zCompName
                 Case "INSPIRON15-I5", "DELL9100"          '*** BEK Laptop Or 2nd DT    ***
                   zGetDBPath = "G:\bekdocs\ARB Files\"
                 Case Else                                 '*** BEK Desktop    ***
                   zGetDBPath = "G:\bekdocs\ARB Files\"    '*** Windows 7 Path  ***
               End Select    '*** Select Case zCompName ***
             End If
         
         Case "Wyboo Manager"                                     '*** Harry          ***
    '         zGetDBPath = _
    '             "C:\Users\Wyboo Manager\Documents\ARB Files\"   '*** Windows 7 Path ***
             zGetDBPath = _
                 "\\Goflex_home\GoFlex Home Personal\ARB Files\"  '*** GoFlex NAS Path ***
         
         Case "Property Manager"                                  '*** Fred         ***
    '         zGetDBPath = "\\Wyboomanager-pc\ARB Files\"         '*** Network Path   ***
             zGetDBPath = _
                 "\\Goflex_home\GoFlex Home Personal\ARB Files\"  '*** GoFlex NAS Path ***
         
         Case "WPOA"                                              '*** ARC Laptop     ***
             zGetDBPath = _
                 Environ("USERPROFILE") & "\My Documents\ARB Files\" '*** GoFlex NAS Path ***
         
         Case Else
             zGetDBPath = "Error"
    
       End Select   '*** Select Case zUName ***
    
    End Function    '*** zGetDBPath() ***
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  10. #9
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Delta, Br. Columbia
    Posts
    115
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Many thanks for the code. I have just heard from the client and you are all correct it was a network mapping issue. While I had checked the drive I required for the application, apparently the network manager had changed the way these two computers connected to all the network drives. He has now fixed that and the problem has gone away.

    I am still going to see if I can implement the above code as it seems to me that it will cope with most future problems if their configuration changes in the future.

  11. #10
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,531
    Thanks
    0
    Thanked 23 Times in 23 Posts
    I have more generalised code that relinks the front end to the backend(s) as the front end is started.
    I can cut down a database and send it if you like.
    I use it for all my clients (though those are rapidly disappearing the older I get) as well as my development databases.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •