Results 1 to 8 of 8
  1. #1
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Hello Fellow DBers,

    Here's a problem that has me vexed.

    Environment:
    Access 2003 SP-3.
    The database is split Front End: ARB.mdb Back Ends: ARB_be.mdb & ARBReqs_be.mdb.
    These files both reside on a P-P Lan.
    Record level locking of the edited record is set on both the FE & BE.

    On my test lan at home Windows 7 32-Bit on my desktop and Windows 7 64-Bit on my laptop. With this setup everything works just as it is supposed to..no problemo!

    However, on the production lan in our Community Manager's office also P-P Lan...
    Access 2003 SP-3.
    Same DB Files in same Directory structure. (I have code that automatically relinks the FE/BE when the FE is opened. {see below})
    One PC (with FE/BE files running Windows 7 64-bit)
    One PC (with FE only running Windows XP Pro 32-Bit)

    The problem:
    If either PC is in the database with a form open the other PC will not relink the tables (sorry I'm not there and forgot the error).
    If either PC is in the database on the switchboard page the other PC can open and relink the tables no problem.
    I also get errors when exiting the update forms about a query that is run automatically not being updateable? The query (an Action Query) deletes any Owner Table entry that no longer has a Lot Table entry associated with it.

    So is this just a problem because of the different OSes? Or is there something more fundamental here that is going to bite me even if the one PC is upgraded to Windows 7?

    Thanks,

    RG

    FYI: Here's the Relink Code:
    Code:
    Function ReLinkTable()
    
       Dim zDBPath        As String
       Dim zDBFullName    As String
       Dim zBEDBFN        As String
       Dim zCompName      As String
       Dim zUName         As String
       Dim zTableName(12) 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"
       
       zUName = Environ("USERNAME")
       Select Case zUName
         Case "Bruce"
             zCompName = Environ("COMPUTERNAME")
             If zCompName = "INSPIRON15-I5" Then                       '*** BEK Laptop     ***
               zDBPath = "\\DELLQUADCORE\bekdocs\ARB Files\"           '*** Network Path   ***
             Else                                                      '*** BEK Desktop    ***
               zDBPath = "G:\BEKDocs\ARB Files\"                       '*** Winows 7 Path  ***
             End If
         Case "Wyboo Manager"                                          '*** Harry          ***
             zDBPath = "C:\Users\Wyboo Manager\Documents\ARB Files\"   '*** Windows 7 Path ***
         Case "PropertyManager"                                        '*** Martin         ***
             zDBPath = "\\Wyboomanager-pc\ARB Files\"                  '*** Network Path   ***
         Case Else
             MsgBox zUName & ": is not an authroized user!", _
                    vbOKOnly + vbCritical, "Error: User Not Authorized"
                    
       End Select
    
       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
        StdMenuToggle "False"
        
    End Function    'ReLinkTable()
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  2. #2
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Midwest, USA
    Posts
    108
    Thanks
    5
    Thanked 5 Times in 4 Posts
    What is the purpose of relinking the tables everytime you open the database? I have production databases that have been in use continuously for over 10 years. They are all split front end and back end. All have multiple users on the lan. The only time I've ever had to relink a table is after a very serious lan problem. The databases are in a 24/7 environment and are inconstant use except for one 8 hour down day per week

    Re-linking everytime you open the database sounds like a recipe for disaster.

  3. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Quote Originally Posted by John MacVean View Post
    What is the purpose of relinking the tables everytime you open the database?
    John,

    The reason for the relinking is this DB is in constant flux. We're trying to get the management of our community association down so managers who are not particularity computer literate can use the DB w/o problems. I also travel quite a bit (retired) and need to manage the DB remotely {upload fixes, etc}. In the past I've had problems with the links {especially when making changes with the BE} and this code has solved all those problems. I've been using the code for over 2 years w/o problem until this current situation with XP and Win 7 machines.

    RG
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  4. #4
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Just a quick question here, (Well not really that quick)
    Even IF you are Re-Linking, why do you need to Delete the Link and then ReLink?
    Why not just do a Refreshlink on the existing link against the new BE Locations?
    What happens if you do not delete the linked table and then re-attach, but just do a refreshlink using the tabledef object rather than with a Macro Action?
    I always use DAO and a Refreshlink against Tabledef's if re-linking to an Access BE.

    The Basic Syntax using DAO object Model is

    Code:
    Dim tdf as DAO.TableDef, strConnect as String
    
    strConnect=";DATABASE=" & strLocation & strDatabase 'Where strLocation Is Path and strDatabase is Name of Database
    'You Can Loop this next bit with Variables for Table name
    
    Set tdf=CurrentDB.tableDefs("Name of Table")
    tdf.Connect=strConnect
    tdf.RefreshLink
    Also are the FE's shared as well as the BE or does each user have their own FE?
    If the FE is shared I can definitely see issues that could arise.
    If a shared table is in use by the same FE whilst another copy is trying to do a relink I can see a potential for a problem.
    Does the Switchboard use data in the linked tables?

    What happens when you step the code?
    Does it fail altogether, or just not do the Relink.
    Is it the Drop of the Table that fails or the Link to the new table?

    Unfortunately it is one of those problems that you have to test in situ unless you can replicate the behaviour elsewhere.
    Andrew

  5. #5
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Midwest, USA
    Posts
    108
    Thanks
    5
    Thanked 5 Times in 4 Posts
    RG,

    After re-reading your original post, my guess would be that the Win 7 machine wants to take exclusive control of the database once that user is past the switchboard, The solution may be creating a user account on the Win 7 machine for the other users of the database. Rights would then be able to be set for those users.

    It sounds like if the other users open the database first, they have exclusive use of it. If the Win 7 user is already using it, the other users are locked out.

    Are the mdw files the same on all the users computers? The Win 7 machine mdw may be trumping the other users rights.

    Since you didn't have the problem before Win 7 that is where I would look.

    John

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Andrew & John,

    Ok, let me tackle these questions:

    Q. Why do you need to Delete the Link and then ReLink?
    A. I use this code because the tables are also in development flux so this insures I get the latest tabledef linked.

    Q. Are the FE's shared as well as the BE?
    A. No each machine/user has their own copy - there are only 2 machines/users

    Q. Does the Switchboard use data in the linked tables?
    A. No. The switchboard table is in the FE.

    Q. What happens when you step the code?
    A. ???

    Q, Does it fail altogether, or just not do the Relink.
    A. Does not Relink.

    Q. Is it the Drop of the Table that fails or the Link to the new table?
    A. It's the link.


    Q. Are the mdw files the same on all the users computers?
    A. I haven't set up any security in Access for this DB.

    Results of further testing:

    I forgot that my Linux computer dual boots XP Pro so I tried the FE on this system (this is at home) and everything works Fine!!!! Now I'm really stumped! Since the Office computers will link under some circumstances I'd rule out file permission problems at least at the windows level.

    Monday I'll get back to the office computer and see if I can get some debugging info, e.g. Error messages and results of stepping through the code.

    Thanks for all your help so far.

    RG
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #7
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Midwest, USA
    Posts
    108
    Thanks
    5
    Thanked 5 Times in 4 Posts
    RG,

    Check out this thread on the MS WinSeven forums: drive mapping

    It may be a simple share problem since the db is stored on the Win 7 machine. Drive mapping appears to work differently with Win 7 and may require you to set up a share for the db to work properly.

    John

  8. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Y'all take a good look at all the egg on my face. I assumed that what the person who setup up the computer and created the share for the folder containing the BE did it correctly, as they swore to me they did. Not So! They set up a Read/Only share. A quick change to the permissions to Full Control and everything works as it should.

    I'd like to thank all those who took their valuable time to respond to this thread. I appreciate it and learned a few extra things along the way.

    RG
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

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