Link to home
Start Free TrialLog in
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
Avatar of harfang
harfang
Flag of Switzerland image

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°)
Avatar of AIN_Alemo
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.
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°)
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.
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°)
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
ASKER CERTIFIED SOLUTION
Avatar of harfang
harfang
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
All's well, thanks