Requirement: Database is split due to size of some tables... As such, we need links to stay consistent depending on who opens the front-end. HUGE CAVEAT - the network location creates a situation where this data is far to slow to retrieve, update, and append. Therefore, the front and back end pieces must first be copied locally to the user's computer (performance). But, the links between the pieces will of course, break. I need to get the relinking to occur automatically, and then do so by looking in the same folder.
I had a similar snippet of code in an Access 97 database, which I cannot convert without a pre 2013 Access installed, which I don't, so not too worried about it.
HOWEVER, I did find the following article explaining how to use code to accomplish this:
https://www.microsoft.com/en-us/micr...access-tables/
KEEP READING: (Note: I took the liberty of finding/replacing all bad characters that paste natively from the above article. It has strange ', ", and - chars being used, which didnt' fit syntax.)
Code:
'—————————————————————————-' Procedure: RefreshTableLinks
' Purpose: Refresh table links to back-ends in the same folder as front end.
' Note: Linked Tables can be in more than one back-end.
' Return: Returns a zero-length string if all tables are relinked.
' Return: Or returns a string listing tables not relinked and errors.
'—————————————————————————-
Public Function RefreshTableLinks() As String
On Error GoTo ErrHandle
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim strCon As String
Dim strBackEnd As String
Dim strMsg As String
Dim intErrorCount As Integer
Set db = CurrentDb
'Loop through the TableDefs Collection.
For Each tdf In db.TableDefs
'Verify the table is a linked table.
If Left$(tdf.Connect, 10) = ";DATABASE=" Then
'Get the existing Connection String.
strCon = Nz(tdf.Connect, "")
'Get the name of the back-end database using String Functions.
strBackEnd = Right$(strCon, (Len(strCon) - (InStrRev(strCon, "") - 1)))
'Verify we have a value for the back-end
If Len(strBackEnd & "") > 0 Then
'Set a reference to the TableDef Object.
Set tdf = db.TableDefs(tdf.Name)
'Build the new Connection Property Value.
tdf.Connect = ";DATABASE=" & CurrentProject.Path & strBackEnd
'Refresh the table link.
tdf.RefreshLink
Else
'There was a problem getting the name of the back-end.
'Add the information to the message to notify the user.
intErrorCount = intErrorCount + 1
strMsg = strMsg & "Error getting back-end database name." & vbNewLine
strMsg = strMsg & "Table Name: " & tdf.Name & vbNewLine
strMsg = strMsg & "Connect = " & strCon & vbNewLine
End If
End If
Next tdf
ExitHere:
On Error Resume Next
If intErrorCount > 0 Then
strMsg = "There were errors refreshing the table links: " _
& vbNewLine & strMsg & "In Procedure RefreshTableLinks"
RefreshTableLinks = strMsg
End If
Set tdf = Nothing
Set db = Nothing
Exit Function
ErrHandle:
intErrorCount = intErrorCount + 1
strMsg = strMsg & "Error " & Err.Number & " " & Err.Description
strMsg = strMsg & vbNewLine & "Table Name: " & tdf.Name & vbNewLine
strMsg = strMsg & "Connect = " & strCon & vbNewLine
Resume ExitHere
End Function
The following is used for error reporting.
Code:
Dim strMsg As String
'Run the Procedure, getting any error messages.
strMsg = RefreshTableLinks()
'strMsg will be a zero-length string if there is no error message.
If Len(strMsg & "") = 0 Then
Debug.Print "All Tables were successfully relinked."
Else
'Notify the user of the errors.
MsgBox strMsg, vbCritical
End If
I am not seeing results for this so far. When removing the back-end database from the current folder, it does not error, nor does it seem to relink even when the correct file is placed back into the folder.
Please let me know if there is a better design - THANKS!