A
Adrian Jansen
Access 2002
I had to do a similar thing to a recent query on relinking FE-BE to a
default location. Stating with Allen Brownes code, which works really well,
I added a bit more to the Form_open routine, to also try linking to a hard
coded default location, before falling into the file browse dialog. Thought
I would share it, since it seems a common problem:
Option Compare Database
Option Explicit
'Copyright: Allen Browne's Database and Training, 2002.
'Author: Allen J Browne, (e-mail address removed)
'Purpose: Check linked tables, and reconnect if necessary.
'Usage: Set this as startup-form. (It's always cancelled.)
'Note: Data available: open frmSwitchboard.
' Data not available: User asked to find back end.
' Quits if back end is not found.
'Version: Works in Access XP and later only (FileDialog).
'Name of the unique table that verifies user found data file.
Private Const conTestTable = "tblLogos"
Private Const conMod = "Form_fSplash"
'Added - A Jansen
'default server location - full pathname of backend
Private Const conDefault = "Full path to server"
Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_Form_Open
'Purpose: Reattach the tables if necessary.
'Note: This form never opens.
Dim bDataOk As Byte 'changed from Boolean
Dim sFile As String 'Fully qualified back end.
Dim sMsg As String 'MsgBox message.
Dim dbLocal As DAO.Database 'This front end.
Set dbLocal = DBEngine(0)(0)
If IsDataOk(dbLocal) Then 'Try opening the test table.
bDataOk = 1 'tables already linked
Else
'It didn't work. Try using the default server file location - Added A
Jansen
sFile = conDefault
If IsReconnected(dbLocal, sFile, sMsg) Then
bDataOk = 2 'tables relinked to default
Else
'try getting the user to reconnect
'Give user repeated chances.
Do
sMsg = ""
'Ask the user to locate the back end.
sFile = GetDataFile()
If sFile = vbNullString Then
sMsg = "No file chosen."
Else
'Try reconnecting to the user's file.
If IsReconnected(dbLocal, sFile, sMsg) Then
'It worked! Flag data as okay, and jump out of the loop.
bDataOk = 2
Exit Do
End If
End If
sMsg = sMsg & vbCrLf & vbCrLf & "Try again?"
If MsgBox(sMsg, vbRetryCancel + vbCritical, "Cannot continue")
<> vbRetry Then
'User gives up: Jump out of the loop without setting the
data okay flag.
Exit Do
End If
Loop
End If
End If
Exit_Form_Open:
Set dbLocal = Nothing
Select Case bDataOk
Case 1
'could open startup form here
Case 2
DoCmd.OpenForm "frmSettings" 'show changed links
Case Else
Application.Quit
End Select
Cancel = True
Exit Sub
Err_Form_Open:
MsgBox "Error " & Err.Number & " - " & Err.Description, vbCritical,
"Unable to access data."
Resume Exit_Form_Open
End Sub
Note that I also changed the bDataOK to a byte, so I can return various
values depending on what form of relink was needed.
--
Regards,
Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control
I had to do a similar thing to a recent query on relinking FE-BE to a
default location. Stating with Allen Brownes code, which works really well,
I added a bit more to the Form_open routine, to also try linking to a hard
coded default location, before falling into the file browse dialog. Thought
I would share it, since it seems a common problem:
Option Compare Database
Option Explicit
'Copyright: Allen Browne's Database and Training, 2002.
'Author: Allen J Browne, (e-mail address removed)
'Purpose: Check linked tables, and reconnect if necessary.
'Usage: Set this as startup-form. (It's always cancelled.)
'Note: Data available: open frmSwitchboard.
' Data not available: User asked to find back end.
' Quits if back end is not found.
'Version: Works in Access XP and later only (FileDialog).
'Name of the unique table that verifies user found data file.
Private Const conTestTable = "tblLogos"
Private Const conMod = "Form_fSplash"
'Added - A Jansen
'default server location - full pathname of backend
Private Const conDefault = "Full path to server"
Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_Form_Open
'Purpose: Reattach the tables if necessary.
'Note: This form never opens.
Dim bDataOk As Byte 'changed from Boolean
Dim sFile As String 'Fully qualified back end.
Dim sMsg As String 'MsgBox message.
Dim dbLocal As DAO.Database 'This front end.
Set dbLocal = DBEngine(0)(0)
If IsDataOk(dbLocal) Then 'Try opening the test table.
bDataOk = 1 'tables already linked
Else
'It didn't work. Try using the default server file location - Added A
Jansen
sFile = conDefault
If IsReconnected(dbLocal, sFile, sMsg) Then
bDataOk = 2 'tables relinked to default
Else
'try getting the user to reconnect
'Give user repeated chances.
Do
sMsg = ""
'Ask the user to locate the back end.
sFile = GetDataFile()
If sFile = vbNullString Then
sMsg = "No file chosen."
Else
'Try reconnecting to the user's file.
If IsReconnected(dbLocal, sFile, sMsg) Then
'It worked! Flag data as okay, and jump out of the loop.
bDataOk = 2
Exit Do
End If
End If
sMsg = sMsg & vbCrLf & vbCrLf & "Try again?"
If MsgBox(sMsg, vbRetryCancel + vbCritical, "Cannot continue")
<> vbRetry Then
'User gives up: Jump out of the loop without setting the
data okay flag.
Exit Do
End If
Loop
End If
End If
Exit_Form_Open:
Set dbLocal = Nothing
Select Case bDataOk
Case 1
'could open startup form here
Case 2
DoCmd.OpenForm "frmSettings" 'show changed links
Case Else
Application.Quit
End Select
Cancel = True
Exit Sub
Err_Form_Open:
MsgBox "Error " & Err.Number & " - " & Err.Description, vbCritical,
"Unable to access data."
Resume Exit_Form_Open
End Sub
Note that I also changed the bDataOK to a byte, so I can return various
values depending on what form of relink was needed.
--
Regards,
Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control