Changing linked table paths (1 Viewer)

O

OBBurton

Registered User.
Local time
Today, 05:43
Joined
Dec 26, 2013
Messages
77
Hi,
I'm trying to change the table links to a password protected BE DB. I found an example online, which I adapted to my needs. When I set it up to fail to find the normal BE it seems to work as intended until it gets to the line "Tdf.RefreshLink". Then it crashes with a 3031 "Not a valid password" error. The code is:
Code: 
Private Sub Form_Open(Cancel As Integer)
    Dim Dbs As Database
    Dim Tdf As TableDef
    Dim Tdfs As TableDefs
    Dim NewPathname As String
    
    If Dir("\\OBBURTON-PC\Users\Public\Documents\" & _
            "FUN Stuff\WilburBE.accdb") <> "" Then
        MsgBox "File Founde!"
        DoCmd.OpenForm "frmMain"
        Forms!frmMain.RecordsetType = 1
        DoCmd.Close acForm, "frmStart"
    Else
'The following code came from:
'http://database.ittoolbox.com/groups/technical-functional/access-l/how-to-programme-the-linked-table-manager-using-vba-in-ms-access-5185870
'RelinkTables...Just as the name suggests, pass a path to a
'database eg RelinkTables("c:\windows\test.mdb")
'and it will go through all the tables in your
'database and link them to the new location
'Written by John Hawkins 20/9/99 www.fabalou.com
        
        Set Dbs = CurrentDb
        Set Tdfs = Dbs.TableDefs
        Screen.MousePointer = 11

        NewPathname = Environ("USERPROFILE") & "\Documents\" & _
            "FUN Stuff\WilburBE.accdb"
'Loop through the tables collection
        For Each Tdf In Tdfs
'If the table source is other than a base table
        If Tdf.SourceTableName <> "" Then
            'Set the new source
            Tdf.Connect = ";DATABASE=" & NewPathname
            Tdf.RefreshLink
        End If
        Next 'Goto next table
        Screen.MousePointer = 0
        MsgBox "All the application tables have been relinked " & _
            "to the database at " & NewPathname & vbCrLf & _
            " Thank you...", vbInformation, "Tables Reinked"
        DoCmd.OpenForm "frmMain"
        Forms!frmMain.RecordsetType = 3
    End If
End Sub
I can't find any references or example to relinking a password protected table. Can anyone offer any examples or corrections to my code? I'm afraid I've reached an impass. :banghead:
 
Last edited:
gemma-the-husky

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 13:43
Joined
Sep 12, 2006
Messages
15,653
look at this for a correctly connected table. This is the same connection string

currentdb.tabledefs."tablename".connect

the connect string will look like this, and will include the password

;database=pathname ;password="whatever"

it might say pwd, not password - I forget which. Anyway, that is what your string needs to look like. note that you cannot conceal the password in the connect string, which is a bit daft.
 
O

OBBurton

Registered User.
Local time
Today, 05:43
Joined
Dec 26, 2013
Messages
77
Hi gemma-the husky,
Okay, I admit it. I don't have the foggiest notion how to use the code you gave me. I'm a novice and what I tried to use is probably not in any way correct. I used:
Code: 
Dbs.TableDefs."tblMain".connect ;database=NewPathName ;password="My Password"
Dbs is my CurrentDB and NewPathName is my path. I got a Compile: error - Syntax error.
You're probably having a good chuckle right now. If you could explain it to me in layman's terms, I would be ever-so-grateful. :eek:
 
gemma-the-husky

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 13:43
Joined
Sep 12, 2006
Messages
15,653
no

this line of your code is basically correct

tdf.Connect = ";DATABASE=" & NewPathname

but it just needs to also include the pwd

tdf.Connect = ";DATABASE=" & NewPathname & ";Password=" & password

but I am not sure of the exact syntax for the password bit

the code I gave you is the syntax for a command to show the connect string for a correctly connected table, to see what it should look like. in the immediate window type this for a correctly connected table

?currentdb.tabledefs."tablemain".connect
 
O

OBBurton

Registered User.
Local time
Today, 05:43
Joined
Dec 26, 2013
Messages
77
Thanks!
That cleared up the error and the code ran, but the tables were not linked to the new BE. So, I went back to my example and realized I had somehow deleted the "Tdf.RefreshLink" line. I replaced the line but now I get an "Invalid password" error on that line. None of the other properties of Tdf seem appropriate, so I'm stumped again. The code has changed a bit so I'll show it in its present form:
Code: 
Private Sub Form_Open(Cancel As Integer)
    Dim Dbs As Database
    Dim Tdf As TableDef
    Dim Tdfs As TableDefs
    Dim NewPathname As String
    
    If Dir("\\OBBURTON-PC\Users\Public\Documents\" & _
            "FUN Stuff\WilburBE.accdb") <> "" Then
        DoCmd.OpenForm "frmMain"
        DoCmd.Close acForm, "frmStart"
    Else
        
        Set Dbs = CurrentDb
        Set Tdfs = Dbs.TableDefs
        NewPathname = Environ("USERPROFILE") & "\Documents\" & _
            "FUN Stuff\WilburBE.accdb"
            
'Loop through the tables collection
    For Each Tdf In Tdfs
'If the table source is other than a base table
        If Tdf.SourceTableName <> "" Then
            'Set the new source
            Tdf.Connect = ";DATABASE=" & NewPathname & ";Password=" & "password"
            Tdf.RefreshLink
        End If
    Next 'Goto next table
        MsgBox "The network database is unavailable." & vbCrLf & _
            "You are connected to a Read-Only local copy.", vbInformation, "Read-Only!"
        DoCmd.OpenForm "frmMain", acNormal, , , acFormReadOnly
        DoCmd.Close acForm, "frmStart"
    End If
End Sub
 
Last edited:
O

OBBurton

Registered User.
Local time
Today, 05:43
Joined
Dec 26, 2013
Messages
77
Hi,
I have done some research and found another way to re-link my table. It deletes the existing table and creates the new linked table just fine. It prompts for a password, which is okay with me. But, I have three problems listed in order of importance.:
1) When I open the local database, I open the main form using the acFormReadOnly method. When I open it using the network database, I open the form with the acFormEdit method. The problem is that the form opens in readonly mode no matter which database is in use. When I try to update a record, I get a "This Recordset is not updatable." The form's RecordSource is a simple Select query that is definitely not ReadOnly. The form's AllowEdits, AllowAdditions and AllowDeletions properties are all set to yes. Record Locks is set to No locks. Nothing I have tried sets my form back to edit mode.
2) The form seems to open very slowly and I see no reason for it. I mention it only because I thought it might be related.
3) I get a Microsoft Security Warning about the new database. I tried to suppress it with the DoCmd.SetWarnings, but it didn't work. I realize I might be stuck with that.

The new code is:
Code: 
Private Sub Form_Open(Cancel As Integer)
    Dim FE_FullName As String
    Dim NW_BE_Name As String, Loc_BE_Name As String
    
    FE_FullName = CurrentDb.name
    NW_BE_Name = "\\OBBURTON-PC\Users\Public\Documents\" & _
            "FUN Stuff\WilburBE.accdb"
    Loc_BE_Name = Environ("USERPROFILE") & "\Documents\" & _
        "FUN Stuff\WilburBE.accdb"
            
'Delete existing main table link
    If IsTable("tblMain") = True Then
        DoCmd.SetWarnings False
        DoCmd.DeleteObject acTable = acDefault, "tblMain"
        DoCmd.SetWarnings True
    End If
    If Dir(NW_BE_Name) <> "" Then
        DoCmd.TransferDatabase acLink, "Microsoft Access", _
            NW_BE_Name, acTable, "tblMain", "tblMain"
        DoCmd.OpenForm "frmMain", acNormal, , , acFormEdit
        Forms!frmMain.AllowEdits = True
        DoCmd.Close acForm, "frmStart"
    Else 
        'DoCmd.TransferDatabase acLink, "Microsoft Access",
        '"DB.mdb", acTable,
        DoCmd.TransferDatabase acLink, "Microsoft Access", _
            Loc_BE_Name, acTable, "tblMain", "tblMain"
        DoCmd.OpenForm "frmMain", acNormal, , , acFormReadOnly
        MsgBox "The network database is unavailable." & vbCrLf & _
            "You are connected to a local copy (Read-Only).", vbInformation, "Read-Only!"
        DoCmd.Close acForm, "frmStart"
    End If
End Sub
I couldn't find anything helpful online about problem #1. I'm not overly concerned about 2 & 3.
 
gemma-the-husky

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 13:43
Joined
Sep 12, 2006
Messages
15,653
I do that actually - delete and reconnect, rather than refresh.

[notes
maybe the password didn't need to be in "" characters.
more likely this is still not quite correct
Tdf.Connect = ";DATABASE=" & NewPathname & ";Password=" & "password"
]

1. non updatable might be because you do not have write permission on the be folder.

2. not sure about the speed issue

3. in A2003, you can tools/macro/security set to low
in A2007/2010 you can do this, but you can easily set the database as trusted, and then you don't get the warnings,
 
O

OBBurton

Registered User.
Local time
Today, 05:43
Joined
Dec 26, 2013
Messages
77
Hi gemma-the-husky,
Thanks for responding.
I'm pretty sure the syntax for Tdf.Connect = ";DATABASE=" & NewPathname & ";Password=" & "password" is correct. The reasons I believe this are:
A) When I plug in the right password I get no errors and stepping through the code it moves through the line and crashes on the following line, Tdf.RefreshLink with an "Invalid Password" error. I think the refresh link is the problem child.
B) If I plug in the wrong password, I get an "Invalid password" error on that line.
C) If I change the syntax I get other errors on that line.

On your other notes:
1) I have read/write permissions on the folder, both as a homegroup member and as an individual. I have done everything I can find to do online. I'm really stumped. I'm sure glad this is a disposable copy. I think I'll burn this copy and start over. This time I'm going to set the AllowAdditions, AllowEdits and AllowDeletions properties of the form instead of using the acFormReadOnly method. That is where the trouble started.
2) I dunno too.
3) I got that fixed by making the folders trusted. Easy peasy.
 
Last edited:
O

OBBurton

Registered User.
Local time
Today, 05:43
Joined
Dec 26, 2013
Messages
77
Ahh! Sweet success!
I abandoned the FE I was experimenting with and started from a clean copy of the DB I am developing. I copied the code from the abandoned copy and changed the approach for making the form read-only as I indicated in my last post and it worked perfectly. I did have to replace the function for testing the existence of the table, but all in all it turned out very well. I still wonder why the acFormReadOnly method did what it did, but that is water under the bridge.
 
You must log in or register to reply here.

Similar threads

J
Check for presence of an SQL Server
2
Replies
20
Views
822
JohnPapa
J
W
Trouble Accessing Linked Tables in Access 2021 via OLEDB Connection: 'Could Not Find Installable ISAM' Error
Replies
6
Views
629
wardah
W
B
VBA problem relating to SQL linked table
Replies
6
Views
538
CJ_London
C
A
"Run-time error - 3219 Invalid Operation
2
Replies
25
Views
2,051
CJ_London
C
J
Add a table to a split database
Replies
5
Views
325
John Sh
J

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom