Re: Relink question

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