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
Can someone please help.
DBToFix.zip
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.
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.
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 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
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°)
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.
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°)
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°)
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
All's well, thanks
MS Access;PWD=AIdb@1.0;DATABA
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°)