the way i did it was to manually link the table once, and save a link
Specification. then i added the following code to a standard module in my
database, as

Public Function isConnect(ByVal strDBType As String, strLinkedTable As
String, _
   strFilePath As String, strSourceTable As String, strPW As String) As
Long

   On Error GoTo Err_Connect
   DoCmd.TransferText

   Dim tdfLinked As TableDef

   isConnect = -1

   Set tdfLinked = CurrentDb.CreateTableDef(strLinkedTable)
   tdfLinked.Connect = strDBType & ";DATABASE=" & strFilePath & ";PWD=" &
strPW
   tdfLinked.SourceTableName = strSourceTable
   CurrentDb.TableDefs.Append tdfLinked

End_Connect:
   Exit Function

Err_Connect:
   Select Case err.Number
       Case 2495  ' the TransferText error that "initializes"
                           ' the saved link specification.
           Resume Next
       Case 3024  ' file doesn't exist
           isConnect = err.Number
           Resume End_Connect
       Case 3012  ' table is already connected
           Resume Next
       Case 3043  ' disk or network error
           isConnect = err.Number
           Resume End_Connect
       Case Else
           MsgBox err.Number & "  -  " & err.Description
           Resume End_Connect
   End Select

End Function

then i called the code from a form in my database, as

       lng = isConnect("", "LinkedTableName", _
           "\\RootFolder\Subfolder\MyBEdb.mdb", _
           "SourceTableName", "mypassword")
       If lng > 0 Then
           MsgBox "Connection error " & lng & ". Contact the programmer,
please."
       End If

hopefully this gives you a starting point, anyway.

hth