Avatar of AIN_Alemo
AIN_Alemo
 asked on

Link tables code gone wrong

Attached is the database (DBtoFix) that i want to link to a backend database (DB_betoFix) the code works well to link database backend tables but still comes up with an error that it cant link all tables.
Can someone please help.
DBToFix.zip
Microsoft Access

Avatar of undefined
Last Comment
AIN_Alemo

8/22/2022 - Mon
harfang

The code you are using isn't very good. Among other things, it doesn't expect any other connection string besides those starting with ";DATABASE=". Your connection strings look like this (path trimmed down):

MS Access;PWD=AIdb@1.0;DATABASE=C:\Users\...\Sale\churchDB_be.accdb

Notice the data before the “;DATABASE” parameter... Since the connection strings do not start with a semi-colon, the code breaks. The strange thing is that your back-end doesn't seem to be password protected; perhaps this is left over from an older version?

In any case, run the integrated linked table manager once (right-click any linked table and choose the assistant from the menu), reconnect your tables, and the code will now work. Alternatively, delete all linked tables and re-link them. Once the mention of the password is gone, Dev Ashish's code will work.

Cheers!
(°v°)
AIN_Alemo

ASKER
The backend version i am using does have the same password. I think i must have removed it before uploading.
Is there anything i should do to modify the code?
As it does link the tables its just that it comes up with an error that they are not linked.
harfang

It makes sense to remove passwords for a database you are uploading, but in this case it obfuscated the problem... I looked at Dev Ashish's code but I'm not going to debug it. It's mostly obsolete (especially all the WinAPI calls) and the method used to store table names and connections strings is flawed. Note that I have used many samples and learned a lot from The Access Web, and that I still often recommend their solutions.

I have a more modern version under construction (it's on my list of articles to write), and I made sure that passwords are handled correctly. If the password from the BE is removed, the function will detect it and continue without further prompt. If a password has been added or changed, it will prompt for it.

'-------------------------------------------------------------------------------
' Relink tables, handling password-protected back-end databases
' Author:   Markus G Fischer — harfang — http://www.e-e.com/M_94825.html
' Date:     2012-07-02
' Question: http://www.experts-exchange.com/Q_27777091.html
'-------------------------------------------------------------------------------

Option Compare Database
Option Explicit

Const MAIN_TABLE = "tblPerson"   ' one of the linked tables...

Private Function CheckPassword( _
    pstrDatabase As String, _
    ByRef pstrPassword As String _
    ) As Byte
'
' Validates or obtains the password of a database
'
' arguments:
'
'   pstrDatabase    full path of the database
'   pstrPassword    in fact a portion of the connection string:
'                   format: "MS Access;PWD=secret" or ""
'
' returns:
'
'   0   failure or cancel
'   1   initial password is OK
'   2   password changed
'
    Dim db As DAO.Database
    Dim strPrompt As String
    Dim intState As Integer
    
On Error Resume Next
    
    strPrompt = "Database password:"
    intState = 1   ' try initial password
    Do
        Set db = OpenDatabase(pstrDatabase, False, False, pstrPassword)
        If Err.Number = 3031 Then
            ' wrong password, retry
            Err.Clear
        ElseIf Err.Number Then
            ' other error (e.g. file not available)
            MsgBox Err.Description
            Exit Function
        Else
            ' we are good
            Exit Do
        End If
        intState = 2   ' try new password
        If Len(pstrPassword) Then
            ' try without one
            pstrPassword = ""
        Else
            pstrPassword = InputBox(strPrompt)
            If Len(pstrPassword) = 0 Then Exit Function
            pstrPassword = "MS Access;PWD=" & pstrPassword
            strPrompt = "Database password:" & vbNewLine _
                & vbNewLine _
                & "(please try again)"
        End If
    Loop
    CheckPassword = intState
    
End Function

Sub RelinkTables()
'
' Relinks all linked tables from the current or a new back-end database.
'
' Note that all tables are expected to be in the same database, this doesn't
' handle links to several back-end files.
' Password-protected databases are handled gracefully.
'
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim strConnArray() As String
    Dim strPasswordInfo As String
    Dim strCurrentBE As String
    Dim fExistsBE As Boolean
    Dim fRefreshOnly As Boolean
    Dim strMsg As String
    
On Error GoTo Problem
    
    ' get current BE location from MAIN_TABLE
    Set db = CurrentDb
    strConnArray = Split(db(MAIN_TABLE).Connect, ";")
    strCurrentBE = Mid(strConnArray(UBound(strConnArray)), 10)
    fExistsBE = Len(Dir(strCurrentBE))
    
    If strConnArray(0) = "MS Access" Then
        ' connection string with password:
        Debug.Assert strConnArray(1) Like "PWD=*"
        ReDim Preserve strConnArray(1)
        strPasswordInfo = Join(strConnArray, ";")
    End If
    
    ' confirm refresh only for existing BE
    If fExistsBE Then
        strMsg = "Current back-end database:" & vbNewLine _
            & strCurrentBE & vbNewLine _
            & vbNewLine _
            & "Select a new file?"
        Select Case MsgBox(strMsg, vbYesNoCancel + vbDefaultButton2)
            Case vbCancel:  Exit Sub
            Case vbNo:      fRefreshOnly = True
        End Select
    End If
    
    If Not fRefreshOnly Then
        ' obtain new back-end location
        With Application.FileDialog(3)   ' msoFileDialogFilePicker
            .AllowMultiSelect = False
            .Title = "Browse for BE database"
            .ButtonName = "Link"
            With .Filters
                .Clear
                .Add "Access", "*.accdb"
                .Add "Other Access", "*.accd?;*.md?"
                .Add "All", "*.*"
            End With
            
            If fExistsBE Then
                ' show current BE
                .InitialFileName = strCurrentBE
            Else
                ' start in FE folder
                .InitialFileName = Left(db.Name, InStrRev(db.Name, "\"))
            End If
            
            If Not .Show Then Exit Sub
            strCurrentBE = .SelectedItems(1)
        End With
    End If
    
    ' deal with password-protected databases
    Select Case CheckPassword(strCurrentBE, strPasswordInfo)
        Case 0:     Exit Sub   ' failure
        Case 1:     ' current password OK
        Case 2:     fRefreshOnly = False   ' new password
    End Select
    
    ' ACTUAL RELINK OCCURS HERE
    
    ' loop through tables
    For Each tdf In db.TableDefs
        If tdf.Attributes And dbSystemObject Then
            ' don't touch those
        ElseIf Len(tdf.Connect) = 0 Then
            ' not a linked table
        Else
            SysCmd acSysCmdSetStatus, tdf.Name
            If Not fRefreshOnly Then _
                tdf.Connect = strPasswordInfo & ";DATABASE=" & strCurrentBE
            tdf.RefreshLink
        End If
    Next tdf
    SysCmd acSysCmdSetStatus, "done!"
    
    MsgBox "Done!"
    
Done:
    SysCmd acSysCmdClearStatus
    Exit Sub
    
Problem:
    ' basic error handling
    Select Case Err.Number
        
        Case 3011   ' table not found in BE
            strMsg = "Table " & tdf.Name & " not found!" & vbNewLine _
                & "Link not refreshed for this table" & vbNewLine _
                & vbNewLine _
                & "Continue?"
            If MsgBox(strMsg, vbOKCancel) = vbCancel Then
                Resume Done
            Else
                Resume Next
            End If
        
        Case Else
            MsgBox Err.Description
            Resume Done
        
    End Select
    
End Sub

Open in new window

Note: the reference to the “Microsoft Office ??.? Object Library” is added when you use the FileDialog object, or rather the constant msoFileDialogFilePicker. I used its value (3) instead, in case this reference hasn't been added.

This module basically replaces most of your modFunctions module (I think it contains some other functions besides what's needed for fRefreshLinks). Try to remove it or to comment it out entirely to see what you need to keep.

Good luck!
(°v°)
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
AIN_Alemo

ASKER
This works great however:
1. i need to know where to enter the password so users may not be asked for it.
2. Can i see a progress of tables being linked, the system just seems to stop responding until link is complete.
harfang

1. The password is stored in the connection string of the table chosen as MAIN_TABLE (tblPerson) in the module. As long as the password doesn't change, the user can switch between databases. Please try it: copy your back-end to another location and link to it.

2. There is a progress indicator in the status bar. You can create something more fancy, naturally. Replace line 156 with an update to a progress form, or something like that.

(°v°)
AIN_Alemo

ASKER
Well im not too good at fancy coding... as you have been of such great help can u give me few lines of code as the progress bar, as i use a code to hide the access window so the status bar is not visible to the user
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
harfang

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
AIN_Alemo

ASKER
All's well, thanks