Hi,
Instead of linking with an autoexec macro I use a form to authentic
against the server and then link the tables. That way the server must be
there before linking begins.
Create a table in the FE called ODBCTables one column with the names of
the tables you wish to link.
Create your login form and under a command button call LinkTables
Note that in the following code; server, dbase, user, pass are all input
fields on the login form. Also make sure you specify the ODBC driver. In
the code below is a MySQL driver you will need to replace it with the
appropriate driver for the server you are connecting to.
Public Sub LinkTables()
On Error GoTo ErrTrp
Dim db As Database, rs As Recordset, tdf As TableDef
Dim dbODBC As Database, strConnect As String
Dim strMsg As String
Dim SrvrNm As String
Dim DbNm As String
Dim UsrNm As String
Dim Pswd As String
ServerName = Me.server
DatabaseName = Me.dbase
UserName = Me.user
Password = Me.pass
Call DelODBC
strConnect = "DRIVER={MySQL ODBC 3.51 Driver};DATABASE=" & _
DbNm & ";SERVER=" & SrvrNm & _
";Uid=" & UsrNm & _
";Pwd=" & Pswd & ";"
Set db = CurrentDb
Set rs = db.OpenRecordset("ODBCTables")
Set dbODBC = OpenDatabase("", False, False, strConnect)
Do While Not rs.EOF
Set tdf = db.CreateTableDef(rs![TableName], dbAttachSavePWD)
tdf.Connect = dbODBC.Connect
tdf.SourceTableName = dbODBC.TableDefs(rs![TableName]).Name
db.TableDefs.Append tdf
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
Set dbODBC = Nothing
Set db = Nothing
ExitTrp:
Exit Sub
ErrTrp:
If Err.Number = 94 Then
strMsg = "User name or Password is blank. Re-enter details or
contact the system administrator."
If MsgBox(strMsg, vbRetryCancel, "Oh Bother!! Locked out again") =
vbCancel Then
DoCmd.Quit
End If
GoTo ExitTrp
End If
End Sub
Public Sub DelODBC()
On Error GoTo ErrTrp
Dim db As Database, tdf As TableDef, i As Integer
Set db = CurrentDb
For i = db.TableDefs.Count - 1 To 0 Step -1
Set tdf = db.TableDefs(i)
If (tdf.Attributes And dbAttachedODBC) Then
dbs.TableDefs.Delete (tdf.Name)
End If
Next i
db.Close
Set db = Nothing
ExitTrp:
Exit Sub
ErrTrp:
Resume ExitTrp
End Sub
HTH,
Regards,
Nick.
