linking tables in VB

[pvp]

I am running an application on Access 2000.

Is it possible to link to tables in another Access
database using VBasic dynamically, rather than using
the
file>get external tables>link tables
pull-down?

Thanks.

 

[John Nurick]

The VBA equivalent is DoCmd.TransferDatabase acLink...

 

[Al Borges]

This is the way I do it:

1) First, in a clickbutton OnClick event, I added the following code:

Private Sub Command41_Click()
DoCmd.SetWarnings False
On Error GoTo command41err
DoCmd.Hourglass True
Call RefreshTableLinks("c:\program files\zfilemds\ZfileHMOcc_be.mdb")
DoCmd.Hourglass False
DoCmd.SetWarnings True
Exit Sub
command41err:
MsgBox "There was an error in linking to the backend database on your hard
drive.", vbCritical
DoCmd.SetWarnings True
Exit Sub
End Sub

2) Set up this general module in the same form:

Public Function RefreshTableLinks(strDB As String) As Integer
Dim dbs As Database
Dim tblLinked As TableDef
'DoCmd.Hourglass (HourglassOn)
On Error GoTo RefreshTableLinks_Err
Set dbs = CurrentDb()
For Each tblLinked In dbs.TableDefs
If tblLinked.Connect <> "" Then
tblLinked.Connect = ";DATABASE=" & strDB
tblLinked.RefreshLink
End If
Next
RefreshTableLinks = True
MsgBox "All table links have been updated!", vbExclamation
'DoCmd.Hourglass (HourglassOff)
RefreshTableLinks_Exit:
Exit Function
RefreshTableLinks_Err:
MsgBox Error$
RefreshTableLinks = False
Resume RefreshTableLinks_Exit
End Function

There is also a routine that was published 3 months ago in Access VB Advisor
magazine detailing the linking of individual tables a la carte.

Regards,
Al