Specifying path to linked tables

G

Guest

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
 
A

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.)
 
G

Guest

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
 
G

Guest

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

Douglas J. Steele

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

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top