Here goes :
Sample code of linking to a secured back end:
gsDBPath is a global variable holding the mdb file path.
Public Sub LinkTablesNew()
Dim sBackEndDB As Database
Dim ws As Workspace
Dim i As Integer
Dim iTblCount As Integer
Dim sTable As String
On Error GoTo EH
TestTablecount iTblCount ' a method to check if I 'havent already linked the tables=======
If Not iTblCount > 7 Then
DoCmd.Hourglass True
Forms![frmLogin]!lblLinking.Visible = True
Forms![frmLogin]!lblLinking.Caption = "Linking tables..."
Forms![frmLogin].Refresh
DoEvents
Set sBackEndDB = DBEngine(0).OpenDatabase(gsDBPath, False, False, "MS Access;PWD=nogo"

With sBackEndDB
For i = 0 To .TableDefs.Count - 1
sTable = .TableDefs(i).Name
If Left$(sTable, 4) <> "MSys" Then 'Don't link the System Tables
DoCmd.TransferDatabase acLink, "Microsoft Access", gsDBPath, acTable, sTable, sTable
End If
Next
End With
Set sBackEndDB = Nothing
DoCmd.Hourglass False
End If
Exit Sub
EH:
MsgBox Err.Number & " - " & Err.Description, vbExclamation, "Linking Tables Error"
Resume Next
End Sub