Re: Relink question

FWIW, what I do is store the full path(s) to the backend(s) in an INI file.
when the database opens, it checks that the Connect strings for the linked
tables match the correct backend in the INI file. If not (as happens when a
new version of the frontend has just been installed) it silently relinks all
the tables.

Only if there is a problem with relinking do I display a message. For some
applications I check whether the user is an Admin and, if so, let them
browse for the correct backend to fix the problem. Otherwise the user just
gets a message to seek assistance.
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

"BruceM" <bamoob@xxxxxxxxxxxxxxxx> wrote in message
news:eHZb6WlRJHA.4992@xxxxxxxxxxxxxxxxxxxxxxx
Thanks again. This is a bit of a digression, but here it is anyhow: I
built a database in which the Before Update event validates that all
required fields are filled in. If not, the record is undone. Before that
happens the user is presented with two message boxes, both with the safe
option (don't undo) highlighted. Several users have clicked through the
non-default choice on both message boxes without looking at either one,
then complained bitterly to me about how my database destroyed their work.
Some of these folks are brilliant in their fields, and make sound choices
on matters affecting the lives and safety of others, but I would not be
confident in their ability to relink a table. They need to call me or
somebody else in the know, if I can locate and train such a person.

I have discovered a version of the mvps code that a helper (computer
science major) devised a while ago. It uses fewer message boxes (for
instance, it doesn't ask if they want to refresh the table links, since
presumably anyone opening the database means to use the data). I think I
could also either present the users with more information about how to
relink, or direct them to further documentation on the network. With a
split database they're not likely to do any harm by relinking incorrectly,
so maybe I worry more than I need to.

Anyhow, thanks for the input.

"Graham Mandeno" <Graham.Mandeno@xxxxxxxxxxxxx> wrote in message
news:1F676185-CFF8-450F-A1D9-66A16D58CEBA@xxxxxxxxxxxxxxxx
Hi Bruce

Yes, the code you posted will raise an error if one of the tables is not
already correctly linked. I suppose you could then give the user the
option of doing nothing and asking for support, or attempting to find the
correct backend themselves.

One trap to watch for is that a user will often browse to the *frontend*
and choose that, instead of the backend. Of course, the frontend does
contain tables with all the correct names, so a link to these tables can
be created, but it will not work because it is circular! So always
check, after a user has selected a backend file, that it is not
CurrentDb.Name.
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

"BruceM" <bamoob@xxxxxxxxxxxxxxxx> wrote in message
news:eO5my3YRJHA.4504@xxxxxxxxxxxxxxxxxxxxxxx
OK, thanks for the information. I have seen code, such as at the mvps
web site, to refresh table links, but message boxes asking the user if
they want to refresh the links, etc. are out of the question. The only
thing I want is for a message box to appear if there is a problem. I am
still trying to sort out how to use the code without message boxes, at
least not routine ones that appear every time the application starts.
Users will simply click through such messages. I probably would do so
myself for messages that appear every time.
Will the code I posted at least generate a message if there is a
problem? It seems to me it will. Perhaps an error could cause an
otherwise invisible command button to appear, and the mvps refresh links
code could be run from the command button as needed.

"Graham Mandeno" <Graham.Mandeno@xxxxxxxxxxxxx> wrote in message
news:8F252999-5A6E-475F-9A92-E06784043514@xxxxxxxxxxxxxxxx
Hi Bruce

This code is not really doing anything useful at all. If the local
table is present, and the Connect string is valid, then the table is
already linked.

For relinking code to be useful, it needs to be able to fix problems
where the tables are *not* already correctly linked. This means it
must be able to:
1. read (from an INI file or somewhere) the correct location of the
backend database(s)
2. browse for the backend database(s) if they don't exist
3. check that the local tables are connected to the correct backends
4. relink them if they are not
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

"BruceM" <bamoob@xxxxxxxxxxxxxxxx> wrote in message
news:uOFWkbPRJHA.5080@xxxxxxxxxxxxxxxxxxxxxxx
I'm not sure this is where to post this question, but here it is
anyhow. Somebody who was working with me a while ago came up with the
following code to relink back end files. I don't know if it is
adapted from the code at the mvps web site, or what exactly. Tables
may be in more than one BE database. In particular, the Employee
table is used by a number of applications. I don't want to present
the user with any options, as most users wouldn't know what to do if
presented with something like the Linked Table Manager, or for that
matter if asked whether they want to relink the tables. Rather, I
want the code to run invisibly on startup. The BE files will not move,
and I have used UNC paths to the BE files.

I have used the code below, which I call in the startup form's Load
event, and have not had a problem with users not being able to get at
the data. Debug.Print lists the tables and the paths correctly. I
wonder, though, if I am leaving out something, as other code I have
seen for relinking the BE files seems to be rather more complex than
this.

Public Function RelinkBE() As Boolean

On Error GoTo ProcError

Dim tdf As TableDef
Dim strMsg As String, strCall As String

strCall = vbCrLf & "Contact tech support."

RelinkBE = True

For Each tdf In CurrentDb.TableDefs
If Len(tdf.Connect) > 0 Then
Debug.Print tdf.Name & " " & tdf.Connect
tdf.RefreshLink
End If
Next tdf
Set tdf = Nothing

ProcExit:
Exit Function

ProcError:
Select Case Err.Number
Case 3011 'Bad Table Name
strMsg = "Table Not Found." & strCall
Case 3024 'File Name Not Found
strMsg = "Database File Name Not Found." & strCall
Case 3044 'Path Not found
strMsg = "Database Path Not Found." & strCall
Case Else
strMsg = "Error " & Err.Number & " (" & Err.Description & _
") in function ReLinkBE of Module mdlRelink" &
strCall
End Select

MsgBox strMsg, vbExclamation, "Call Support"
RelinkBE = False

Resume ProcExit

End Function








.

Loading