Relink split database where no UI

[Owl]

I have Microsoft Access 2007 and WindowsXP and I am looking for code to relink a split database that can be used in a Runtime environment (so I don"t have UI available to relink on the target computer and I don't have the same path on the source computer as on the target computer). I would like it to come up with no dialog boxes with questions, but if it has to, then not in the middle of the screen as I have a startup screen (with a timer - is that a problem?) and I want them to read the contact information on the contact screen and not have it hidden by dialog boxes.

Thank you for any help.

[Starmist]

Hi Owl,

I came across this code which I think is a standard piece of Microsoft code.

Run this module, which cheks to see if it can find records in a table in the backend that there will always be records in.

Public Function CheckLinks() As Boolean
' Check links to the DBS2001 database; returns True if links are OK.

Dim DBS As DAO.Database, rst As DAO.Recordset

Set DBS = CurrentDb
' Open linked table to see if connection information is correct.
On Error Resume Next
Set rst = DBS.OpenRecordset("your table name")
' If there's no error, return True.
If Err = 0 Then
CheckLinks = True
Else
CheckLinks = False
End If

End Function

Then in the autoexec macro, it runs Checklinks and if it's false, calls to run the following module:-

Public Function RelinkTables() As Boolean
' Tries to refresh the links to the database.
' Returns True if successful.
Dim strAccDir As String
Dim strSearchPath As String
Dim strFileName As String
Dim intError As Integer
Dim strError As String

Const conMaxTables = 8
Const conNonExistentTable = 3011
Const conNotNorthwind = 3078
Const conNwindNotFound = 3024
Const conAccessDenied = 3051
Const conReadOnlyDatabase = 3027
Const conAppTitle = "DBS"
' Get name of directory where MSAccess.exe is located.
strAccDir = "C:"
' Get the default sample database path.
If Dir(strAccDir & "Desktop\.") = "" Then
strSearchPath = strAccDir
Else
strSearchPath = strAccDir & "Desktop\"
End If
' Look for the database.
If (Dir(strSearchPath & "[enter the full name of your backend database here including the suffix]") <> "") Then
strFileName = strSearchPath & "[your database name]"
Else
' Can't find the database, so display the Open dialog box.
MsgBox "I can't find Data tables. " & vbCrLf _
& "You must make sure the database is " _
& "linked." & vbCrLf _
& "Click ok below then double click the file named [your database name]", vbExclamation
strFileName = FindNorthwind(strSearchPath)
If strFileName = "" Then
strError = "Sorry, you must locate [your database name] to open " & conAppTitle & "."
GoTo Exit_Failed
End If
End If
' Fix the links.
If RefreshLinks(strFileName) Then
RelinkTables = True
'DoCmd.OpenForm [Your splashscreen]
Exit Function
End If

' If it failed, display an error.
Select Case Err
Case conNonExistentTable, conNotNorthwind
strError = "File '" & strFileName & "' does not contain the required Data tables."
Case Err = conNwindNotFound
strError = "You can't run " & conAppTitle & " until you locate the Data database."
Case Err = conAccessDenied
strError = "Couldn't open " & strFileName & " because it is read-only or located on a read-only share."
Case Err = conReadOnlyDatabase
strError = "Can't relink tables because " & conAppTitle & " is read-only or is located on a read-only share."
Case Else
strError = Err.Description
End Select

Exit_Failed:
MsgBox strError, vbCritical
RelinkTables = False

End Function

Hope this helps!

[Owl]

Thanks, Starmist. I really appreciate this.

I actually need it to relink the FE to the BE if it isn’t linked, but just glancing at the code you gave me, it seems to me as though the code DOES do that too.

I actually did manage to find a module that works – well, it worked once I had restarted from an empty database and imported the module and the macro and form that went with it along with absolutely everything in my database.

The database originally came from 2 databases in previous versions of Access, both of which I edited ad infinitum over many years. Even after the amalgamation of the 2 databases, I still edited it extensively and over a period of time. It seems to me that some or other setting that isn’t in a default new 2007 database, was in some or other part of the previous database/s that was stopping the code working.

Thank you very much again Starmist. I will try this, and even if it doesn’t suit perfectly and/or I don't use it for this database, I will keep it in case it is exactly what I want in another database.

[Starmist]

No problem Owl. I hope you find it useful.