Specifying path to linked tables

[Baby Face Lee]

Hi everyone
I work from home and create various Access databases for my department.
Because I don't go into the office, it would be helpful if I could specify a
path (string) that the front-end database uses to link to the back-end
tables. When I'm developing and testing out the database here at home I
obviously have the back-end on my hard drive whereas it's on a server in the
office. At present, a colleague does the linking for me (via the usual
dialog box) but it would be nice if this wasn't necessary.

I've seen vb code that permits the database to *re-attach* to an existing
location but I'm not sure how to specify a path that will overwrite any
current links that exist. Can you help? Please spell it out for me if you
can as I'm feeling pretty sorry for myself today - I've got a raging
toothache and haven't slept much!!! :-(
Thanks guys!
(pathetic) Lee

 

[Allen Browne]

Microsoft's sample database, Solutions.mdb, contains a module you can use
for relinking. Download from:
http://msdn.microsoft.com/library/officedev/bapp2000/mdbdownload.htm
Essentially all you have to do is set the Connect property of each TableDef,
remembering to RefreshLinks. The more complex part of the solutions example
is the API call for the user to select the file.

If your work uses a mapped drive letter to connect to the database, you
could simulate that at home with the subst command (at thecommand line.)

 

[Baby Face Lee]

Thanks Allen, that's helpful. However, I'm obviously missing something here.
Below is the test path/code that's within the 'On Load' event of the form
that starts up when the database is started:

Dim strFileName As String

strFileName = "C:\Estimates\Jan06Est_DATA.mdb;"
Call RefreshLinks(strFileName)

Below is the RefreshLinks function:

Public Function RefreshLinks(strFileName As String) As Boolean

Dim dbs As Database
Dim tdf As TableDef

' Loop through all tables in the database.
Set dbs = CurrentDb
For Each tdf In dbs.TableDefs
' If the table has a connect string, it's a linked table.
If Len(tdf.Connect) > 0 Then
tdf.Connect = ";DATABASE=" & strFileName
Err = 0
On Error Resume Next
tdf.RefreshLink ' Relink the table.

If Err <> 0 Then
RefreshLinks = False
Exit Function
End If
End If
Next tdf

RefreshLinks = True ' Relinking complete.
End Function

So sorry to be dim but why doesn't this work? The path within the Linked
Tables Manager is different to the above path as I want to see if the code
will take precedence but it doesn't.

Thanks,

Lee

 

[Baby Face Lee]

oops, I notice I've typed a semi-colon in the path below but that's just a
typo in the message.

 

[Douglas J. Steele]

Actually, if you don't have that semi-colon, it's may be your problem: it's
necessary.

 

[Allen Browne]

First, startup form must be unbound. If it is a bound form, Access will
discover the RecordSource is unavailable before the relinking runs. Use the
Form_Open event, open the real startup form after the relinking, and cancel
the event (so the unbound form never loads).

Secondly, that code can fail if there are temp tables (deleted linked
tables). You can avoid that by testing if tdf.Name starts with a ~
character.

If you're still stuck, comment out the error handler line, such as:
'On Error Resume Next
Then when it fails, you can use the immediate window to find out where it's
up to, what the Err.Number is, and figure out what's going on.

 

[Baby Face Lee]

Thanks for your help with this. All is working OK now!!