Refresh links automatically

[BruceM]

I have attempted to use the code at the MVP site for relinking tables at
startup. I placed the code in a standard module and called it from the Open
event of my startup form. However, when I tested by renaming the backend
database (the one to which the FE links), the code never had a chance to
run. Instead, a standard Access error message appeared: "Could not find
file '\\[Filename]'. The form's Open event never occurred. I chose the
Open event
because the code on the MVP site said to run the RefreshLinks function at db
startup. I took that to mean the first possible opportunity, which I had
thought was the startup form's Open event.

I modified the code (in the interest of full disclosure, I had help) because
I do not want to present the user with a dialog box for relinking. Most
users will have no idea what to do, so I am using a generic error message to
contact IT support. This means I did not add the API to open the standard
Windows File Open/Save dialog. I also eliminated the following function
from the Refresh Links code:

Function fGetMDBName(strIn As String) As String
'Calls GetOpenFileName dialog
Dim strFilter As String

strFilter = ahtAddFilterItem(strFilter, _
"Access Database(*.mdb;*.mda;*.mde;*.mdw) ", _
"*.mdb; *.mda; *.mde; *.mdw")
strFilter = ahtAddFilterItem(strFilter, _
"All Files (*.*)", _
"*.*")

fGetMDBName = ahtCommonFileOpenSave(Filter:=strFilter, _
OpenFile:=True, _
DialogTitle:=strIn, _
Flags:=ahtOFN_HIDEREADONLY)
End Function

Considering that when the link is not good, no code runs, I can't see that
leaving out code could affect anything. I could provide more details, but I
don't know which ones would be helpful. Does anybody have an idea as to
what I need to do differently?

 

[Klatuu]

The code you copied is to refresh the linked tables, not link to a different
back end. When you renamed the back end, it can't fine the file and errors
out. In this case, the error created would be what would happen if the back
end file can't be found. You would want to set your error handling up to
produce a more user friendly error when this happens.

 

[BruceM]

Thanks for replying. Yes, I would like a more user-friendly error message,
but since the "could not find file" error message occurs before the first
event on the startup form, I don't know how to intercept it. The intention
of the code modifications is in part to have the users notify IT if there is
a problem.
The original code works in tandem with the file Open/Save dialog to allow
the user to relink the tables if there is the problem with the automatic
refresh. I left out asking the users to relink in favor of telling the
users to notify somebody if there is a problem. In the original code, I
think I understand that if there is a problem refreshing the link the
Open/Save dialog appears. This must mean that in the code as posted the
user sees the custom error messages in the function rather than the
unfriendly Windows messages.
I gather that you are familiar with the code, but for convenience here is a
link to the web site
http://www.mvps.org/access/tables/tbl0009.htm

The code you copied is to refresh the linked tables, not link to a
different
back end. When you renamed the back end, it can't fine the file and errors
out. In this case, the error created would be what would happen if the
back
end file can't be found. You would want to set your error handling up to
produce a more user friendly error when this happens.
--
Dave Hargis, Microsoft Access MVP

I have attempted to use the code at the MVP site for relinking tables at
startup. I placed the code in a standard module and called it from the
Open
event of my startup form. However, when I tested by renaming the backend
database (the one to which the FE links), the code never had a chance to
run. Instead, a standard Access error message appeared: "Could not find
file '\\[Filename]'. The form's Open event never occurred. I chose the
Open event
because the code on the MVP site said to run the RefreshLinks function at
db
startup. I took that to mean the first possible opportunity, which I had
thought was the startup form's Open event.

I modified the code (in the interest of full disclosure, I had help)
because
I do not want to present the user with a dialog box for relinking. Most
users will have no idea what to do, so I am using a generic error message
to
contact IT support. This means I did not add the API to open the
standard
Windows File Open/Save dialog. I also eliminated the following function
from the Refresh Links code:

Function fGetMDBName(strIn As String) As String
'Calls GetOpenFileName dialog
Dim strFilter As String

strFilter = ahtAddFilterItem(strFilter, _
"Access Database(*.mdb;*.mda;*.mde;*.mdw) ", _
"*.mdb; *.mda; *.mde; *.mdw")
strFilter = ahtAddFilterItem(strFilter, _
"All Files (*.*)", _
"*.*")

fGetMDBName = ahtCommonFileOpenSave(Filter:=strFilter, _
OpenFile:=True, _
DialogTitle:=strIn, _
Flags:=ahtOFN_HIDEREADONLY)
End Function

Considering that when the link is not good, no code runs, I can't see
that
leaving out code could affect anything. I could provide more details,
but I
don't know which ones would be helpful. Does anybody have an idea as to
what I need to do differently?

 

[George Nicholson]

Does your startup form have a recordset or bound controls? That could cause
an error before the Open event fires. (Maybe a AutoExec macro?)

If that's the case, create a new "Please wait..." form that isn't data
bound, put the relink code there and designate it as your startup form. Once
relinked, open the desired form and close the startup.

Review: if your fe has linked tables, information about that link connection
is stored with each table in the fe. If/when you change the location or name
of your be you need to run relink code to change the link information
*before* anything in the app tries to use the "old" connection information
or you'll get a "file not found" error.

HTH,

Thanks for replying. Yes, I would like a more user-friendly error
message, but since the "could not find file" error message occurs before
the first event on the startup form, I don't know how to intercept it.
The intention of the code modifications is in part to have the users
notify IT if there is a problem.
The original code works in tandem with the file Open/Save dialog to allow
the user to relink the tables if there is the problem with the automatic
refresh. I left out asking the users to relink in favor of telling the
users to notify somebody if there is a problem. In the original code, I
think I understand that if there is a problem refreshing the link the
Open/Save dialog appears. This must mean that in the code as posted the
user sees the custom error messages in the function rather than the
unfriendly Windows messages.
I gather that you are familiar with the code, but for convenience here is
a link to the web site
http://www.mvps.org/access/tables/tbl0009.htm

The code you copied is to refresh the linked tables, not link to a
different
back end. When you renamed the back end, it can't fine the file and
errors
out. In this case, the error created would be what would happen if the
back
end file can't be found. You would want to set your error handling up to
produce a more user friendly error when this happens.
--
Dave Hargis, Microsoft Access MVP

I have attempted to use the code at the MVP site for relinking tables at
startup. I placed the code in a standard module and called it from the
Open
event of my startup form. However, when I tested by renaming the
backend
database (the one to which the FE links), the code never had a chance to
run. Instead, a standard Access error message appeared: "Could not
find
file '\\[Filename]'. The form's Open event never occurred. I chose the
Open event
because the code on the MVP site said to run the RefreshLinks function
at db
startup. I took that to mean the first possible opportunity, which I
had
thought was the startup form's Open event.

I modified the code (in the interest of full disclosure, I had help)
because
I do not want to present the user with a dialog box for relinking. Most
users will have no idea what to do, so I am using a generic error
message to
contact IT support. This means I did not add the API to open the
standard
Windows File Open/Save dialog. I also eliminated the following function
from the Refresh Links code:

Function fGetMDBName(strIn As String) As String
'Calls GetOpenFileName dialog
Dim strFilter As String

strFilter = ahtAddFilterItem(strFilter, _
"Access Database(*.mdb;*.mda;*.mde;*.mdw) ", _
"*.mdb; *.mda; *.mde; *.mdw")
strFilter = ahtAddFilterItem(strFilter, _
"All Files (*.*)", _
"*.*")

fGetMDBName = ahtCommonFileOpenSave(Filter:=strFilter, _
OpenFile:=True, _
DialogTitle:=strIn, _
Flags:=ahtOFN_HIDEREADONLY)
End Function

Considering that when the link is not good, no code runs, I can't see
that
leaving out code could affect anything. I could provide more details,
but I
don't know which ones would be helpful. Does anybody have an idea as to
what I need to do differently?

 

[BruceM]

That makes perfect sense now that you mention it. In fact, I'm just about
certain that is what is causing me grief. The startup form does indeed have
bound controls. I wonder why it didn't occur to me that could cause
problems of the sort I experienced. I will try your suggestion of using an
unbound startup form, but it will have to wait until tomorrow, as my day
here draws to a close. Thanks for the suggestion.

Does your startup form have a recordset or bound controls? That could
cause an error before the Open event fires. (Maybe a AutoExec macro?)

If that's the case, create a new "Please wait..." form that isn't data
bound, put the relink code there and designate it as your startup form.
Once relinked, open the desired form and close the startup.

Review: if your fe has linked tables, information about that link
connection is stored with each table in the fe. If/when you change the
location or name of your be you need to run relink code to change the link
information *before* anything in the app tries to use the "old" connection
information or you'll get a "file not found" error.

HTH,

Thanks for replying. Yes, I would like a more user-friendly error
message, but since the "could not find file" error message occurs before
the first event on the startup form, I don't know how to intercept it.
The intention of the code modifications is in part to have the users
notify IT if there is a problem.
The original code works in tandem with the file Open/Save dialog to allow
the user to relink the tables if there is the problem with the automatic
refresh. I left out asking the users to relink in favor of telling the
users to notify somebody if there is a problem. In the original code, I
think I understand that if there is a problem refreshing the link the
Open/Save dialog appears. This must mean that in the code as posted the
user sees the custom error messages in the function rather than the
unfriendly Windows messages.
I gather that you are familiar with the code, but for convenience here is
a link to the web site
http://www.mvps.org/access/tables/tbl0009.htm

The code you copied is to refresh the linked tables, not link to a
different
back end. When you renamed the back end, it can't fine the file and
errors
out. In this case, the error created would be what would happen if the
back
end file can't be found. You would want to set your error handling up to
produce a more user friendly error when this happens.
--
Dave Hargis, Microsoft Access MVP


:

I have attempted to use the code at the MVP site for relinking tables
at
startup. I placed the code in a standard module and called it from the
Open
event of my startup form. However, when I tested by renaming the
backend
database (the one to which the FE links), the code never had a chance
to
run. Instead, a standard Access error message appeared: "Could not
find
file '\\[Filename]'. The form's Open event never occurred. I chose
the
Open event
because the code on the MVP site said to run the RefreshLinks function
at db
startup. I took that to mean the first possible opportunity, which I
had
thought was the startup form's Open event.

I modified the code (in the interest of full disclosure, I had help)
because
I do not want to present the user with a dialog box for relinking.
Most
users will have no idea what to do, so I am using a generic error
message to
contact IT support. This means I did not add the API to open the
standard
Windows File Open/Save dialog. I also eliminated the following
function
from the Refresh Links code:

Function fGetMDBName(strIn As String) As String
'Calls GetOpenFileName dialog
Dim strFilter As String

strFilter = ahtAddFilterItem(strFilter, _
"Access Database(*.mdb;*.mda;*.mde;*.mdw) ", _
"*.mdb; *.mda; *.mde; *.mdw")
strFilter = ahtAddFilterItem(strFilter, _
"All Files (*.*)", _
"*.*")

fGetMDBName = ahtCommonFileOpenSave(Filter:=strFilter, _
OpenFile:=True, _
DialogTitle:=strIn, _
Flags:=ahtOFN_HIDEREADONLY)
End Function

Considering that when the link is not good, no code runs, I can't see
that
leaving out code could affect anything. I could provide more details,
but I
don't know which ones would be helpful. Does anybody have an idea as
to
what I need to do differently?

 

[Klatuu]

Intercepting the error should be done in the error handler. The code should
go in one of two places, depending on what your startup options are.
If you use an AutoExec macro, then you should make the code a function and
use the RunCode action to execute. If you use a startup form, it should be
in the Load event of the form opened at startup. Then just remove the code
that presents dialog. Here is a simpler way of doing the same thing. It will
present a correct error message and it returns True if the relink was
successful and False if not.

Public Function ReLinkBE() As Boolean
Dim tdf As TableDef
Dim strMsg As String
On Error GoTo ReLinkBE_Error

ReLinkBE = True
For Each tdf In CurrentDb.TableDefs
If Len(tdf.Connect) > 0 Then
tdf.RefreshLink
End If
Next tdf
Set tdf = Nothing

ReLinkBE_Exit:
On Error GoTo 0

Exit Function

ReLinkBE_Error:

Select Case Err.Number
Case 3011 'Bad Table Name
strMsg = "Table Not Found"
Case 3024 'File Name Not Found
strMsg = "Database File Name Not Found"
Case 3044 'Path Not found
strMsg = "Database Path Not Found"
Case Else
strMsg = "Error " & Err.Number & " (" & Err.Description & _
") in procedure ReLinkBE of Module modUtilities"
End Select
MsgBox strMsg, vbExclamation, "Call Support"
ReLinkBE = False

GoTo ReLinkBE_Exit
End Function

--
Dave Hargis, Microsoft Access MVP

Thanks for replying. Yes, I would like a more user-friendly error message,
but since the "could not find file" error message occurs before the first
event on the startup form, I don't know how to intercept it. The intention
of the code modifications is in part to have the users notify IT if there is
a problem.
The original code works in tandem with the file Open/Save dialog to allow
the user to relink the tables if there is the problem with the automatic
refresh. I left out asking the users to relink in favor of telling the
users to notify somebody if there is a problem. In the original code, I
think I understand that if there is a problem refreshing the link the
Open/Save dialog appears. This must mean that in the code as posted the
user sees the custom error messages in the function rather than the
unfriendly Windows messages.
I gather that you are familiar with the code, but for convenience here is a
link to the web site
http://www.mvps.org/access/tables/tbl0009.htm

The code you copied is to refresh the linked tables, not link to a
different
back end. When you renamed the back end, it can't fine the file and errors
out. In this case, the error created would be what would happen if the
back
end file can't be found. You would want to set your error handling up to
produce a more user friendly error when this happens.
--
Dave Hargis, Microsoft Access MVP

I have attempted to use the code at the MVP site for relinking tables at
startup. I placed the code in a standard module and called it from the
Open
event of my startup form. However, when I tested by renaming the backend
database (the one to which the FE links), the code never had a chance to
run. Instead, a standard Access error message appeared: "Could not find
file '\\[Filename]'. The form's Open event never occurred. I chose the
Open event
because the code on the MVP site said to run the RefreshLinks function at
db
startup. I took that to mean the first possible opportunity, which I had
thought was the startup form's Open event.

I modified the code (in the interest of full disclosure, I had help)
because
I do not want to present the user with a dialog box for relinking. Most
users will have no idea what to do, so I am using a generic error message
to
contact IT support. This means I did not add the API to open the
standard
Windows File Open/Save dialog. I also eliminated the following function
from the Refresh Links code:

Function fGetMDBName(strIn As String) As String
'Calls GetOpenFileName dialog
Dim strFilter As String

strFilter = ahtAddFilterItem(strFilter, _
"Access Database(*.mdb;*.mda;*.mde;*.mdw) ", _
"*.mdb; *.mda; *.mde; *.mdw")
strFilter = ahtAddFilterItem(strFilter, _
"All Files (*.*)", _
"*.*")

fGetMDBName = ahtCommonFileOpenSave(Filter:=strFilter, _
OpenFile:=True, _
DialogTitle:=strIn, _
Flags:=ahtOFN_HIDEREADONLY)
End Function

Considering that when the link is not good, no code runs, I can't see
that
leaving out code could affect anything. I could provide more details,
but I
don't know which ones would be helpful. Does anybody have an idea as to
what I need to do differently?