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...