Results 1 to 9 of 9
  1. #1
    RMittelman is offline Advanced
    Windows 7 32bit Access 2007
    Join Date
    Dec 2010
    Location
    Simi Valley, CA
    Posts
    211

    Re-Link back-end tables when password protected

    Sorry, tag says Access 2007, but this is Access 2013.
    I'm having a big problem re-linking tables. I have a routine which loops through each table, and if it's a linked table refreshes the link to the back-end database.
    This is needed because I change the back-end database periodically (this is a multi-company situation, and each company has their own back-end database).
    Here is the code I use:
    Code:
    Public Function ReLinkTables(beDatabasePath As String, _
                                 Optional password As String = "", _
                                 Optional includeTables As String = "", _
                                 Optional excludeTables As String = "") _
                                 As Boolean
    
    
        Dim fso     As New FileSystemObject
        Dim db      As DAO.Database
        Dim tdf     As DAO.TableDef
        Dim tblName As String
        Dim connStr As String
        Dim oldPath As String
        
        On Error GoTo error_handler
        
        'Continue if back-end database exists.
    
    
        If fso.FileExists(beDatabasePath) Then
            
            Set db = CurrentDb
            For Each tdf In db.TableDefs
                
                tblName = tdf.Name
                If InStr(1, tblName, "~TMP", vbTextCompare) = 0 Then
                    If (InStr(1, tdf.Connect, "DATABASE=", vbTextCompare) > 0) Then
                        If (includeTables = "") Or (InStr(1, "," & includeTables & ",", "," & tblName & ",") > 0) Then
                            If (InStr(1, "," & excludeTables & ",", "," & tblName & ",") = 0) Then
                                Stop
                                oldPath = GetStringSegment(tdf.Connect, "DATABASE=", ";")
                                connStr = Replace$(tdf.Connect, oldPath, beDatabasePath)
                                tdf.Connect = connStr
                                tdf.RefreshLink
                            End If 'Not in exclude list.
                        End If 'In include list (or no include list).
                    End If 'Is a linked table.
                End If 'Not a temp table.
                
            Next
            ReLinkTables = True
            
        Else
            MsgBox "Database " & beDatabasePath & " not found.", vbCritical, "Error"
            ReLinkTables = False
        End If
        
    exit_handler:
        On Error Resume Next
        Set tdf = Nothing
        Set db = Nothing
        Set fso = Nothing
        Exit Function
        
    error_handler:
        
        'Password error. Try adding password if supplied, and we haven't tried already.
        If Err.Number = 3031 Then
            If password > "" Then
                If InStr(1, connStr, "MS Access;PWD=", vbTextCompare) = 0 Then
                    connStr = "MS Access;PWD=" & password & ";DATABASE=" & beDatabasePath
                    tdf.Connect = connStr
                    Resume
                Else
                    ReLinkTables = False
                    Resume exit_handler
                End If
            Else
                ReLinkTables = False
                Resume exit_handler
            End If
            
        Else
            Stop
            MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure ReLinkTables of Module modUtil"
            ReLinkTables = False
            Resume exit_handler
            Resume
        End If
    
    End Function
    This works just fine if back-end database is not password protected. If it is, I get the error 3031. My error handler creates a new Connect property for the tableDef (containing the password), and it should refresh properly. But, I'm still getting the error 3031 (invalid password).

    every article I find says the connect should be like:


    "MS Access;PWD=ron;DATABASE=M:\FolderName\FolderName\D BName.mdb"(assuming the password is "ron").

    Perhaps Access 2013 works differently? Can anybody help with this?
    thanks...

  2. #2
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    when you add a password to the back end, you cannot just relink - you need to delete the original tabledef and create a new one

  3. #3
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Have you tried doing a

    debug.print tdf.Connect

    Maybe you can update one of the msys tables if it is not possible to do it via the Connect property. I just now started developing with 2013, so I do not have any first hand experience with its security stuff.

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Don't know if it is causing a problem, but "password" is a reserved word in Access.
    Might try "strPWD" or "sPassword" instead.

  5. #5
    RMittelman is offline Advanced
    Windows 7 32bit Access 2007
    Join Date
    Dec 2010
    Location
    Simi Valley, CA
    Posts
    211
    Quote Originally Posted by Ajax View Post
    when you add a password to the back end, you cannot just relink - you need to delete the original tabledef and create a new one
    Thanks for the quick answer. This is surprising, as there are so many examples out there of how to build the Connect string which include the password.
    I'm a bit curious about the wording of your reply. You said "when you add a password..." Does that does that specifically mean adding a password, versus trying to link to a table in a back-end database that ALREADY has a password applied? Don't mean to split hairs...

    So you say I need to drop the tabledef and re-add it, specifying the password at that time, right? I will try this. Thanks...

  6. #6
    RMittelman is offline Advanced
    Windows 7 32bit Access 2007
    Join Date
    Dec 2010
    Location
    Simi Valley, CA
    Posts
    211
    Thanks Steve, but that is not the issue. I built the Connect string as described in OP, without using a variable like "password".

  7. #7
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    You said "when you add a password..."
    I meant if a back end does not have a password and you subsequently add a password you need to delete the old linked table and relink. Your connect string is correct - it is the same structure I have for linked tables so I don't think that is the issue.

  8. #8
    RMittelman is offline Advanced
    Windows 7 32bit Access 2013
    Join Date
    Dec 2010
    Location
    Simi Valley, CA
    Posts
    211
    Quote Originally Posted by Ajax View Post
    I meant if a back end does not have a password and you subsequently add a password you need to delete the old linked table and relink. Your connect string is correct - it is the same structure I have for linked tables so I don't think that is the issue.
    The database will always have a password. It is supplied by external customer, and is used with their application. My Access application wants to link to some tables from the database. In the past, knowing the password, I went to the trouble of opening the BE database in Access and removing the password (with the vendor's approval). Then I can do my re-linking as needed when I switch from one BE database to another. This has always worked, but now the vendor put something in their application which re-establishes the database password periodically. I believe it is when the user compacts and repairs that database, using the vendor's UI functionality.

    Now, I believe it is easier to teach my Access FE database to use the password, rather than removing it first. I will try this again, starting from scratch. I think in my testing I simply added a password to the BE database, but didn't remove and re-add the linked tables. Thanks for that tip. I think the only challenge is if one BE database has a password, and another one does not, it may cause a problem switching between them. But I will experiment. Eventually, they will all have passwords, so we will see if this works. Thanks again. I will try this and report the results.

  9. #9
    RMittelman is offline Advanced
    Windows 7 32bit Access 2013
    Join Date
    Dec 2010
    Location
    Simi Valley, CA
    Posts
    211
    Ajax, you rock! That totally worked. All of the googling I did returned no results showing I actually had to remove the links first. I think I was testing my code without doing what needed to be done.

    It turned out the answer wasn't quite so simple. For example, if I have 5 different BE databases and some have passwords, that complicated things. So my code first tries to link without the password, then if I get the error it tries to link with the password. Now I can successfully switch back and forth between PW- and NonPW-BE databases and all works.

    Funny thing, I didn't change my code at all. I guess removing the linked tables and putting them back manually first (using the password) got me past the problem. I can then switch between the 2 BE databases without issue. I even went back to the password-protected BE database and linked, then externally added the password into the non-password-protected database, then in my application switched to that one, and the linking still worked. Now all is well. I will re-introduce the default password into all 5 BE databases and not have to worry about this again.

    Thanks again...

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 09-21-2012, 05:12 PM
  2. VBA to automatically re-link back end tables
    By bdaniel in forum Programming
    Replies: 2
    Last Post: 12-19-2011, 05:45 PM
  3. Open a password protected MDB/MDW
    By abcc14 in forum Security
    Replies: 8
    Last Post: 11-02-2011, 07:41 AM
  4. Replies: 4
    Last Post: 09-14-2011, 12:33 AM
  5. Virtual Password-Protected Connection
    By marianne in forum Sample Databases
    Replies: 6
    Last Post: 02-18-2011, 10:41 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums