Hi Ladi
Yes, you can do this with code that runs when your database opens. The
following function will link (or relink) a table:
Public Function LinkTable(LocalTable As String, _
SourceDatabase As String, _
Optional ByVal SourceTableName As String, _
Optional ForceRefresh As Boolean = False) As Long
Dim db As DAO.Database, tbl As DAO.TableDef
Dim sConnect As String, ErrSts As Long
On Error GoTo ProcErr
If Len(SourceTableName) = 0 Then SourceTableName = LocalTable
Set db = DBEngine(0)(0)
If SourceDatabase = db.Name Then
Err.Raise vbObjectError + 1, , "Linked table must be in a different
database"
End If
sConnect = ";DATABASE=" & SourceDatabase
On Error Resume Next
Set tbl = db(LocalTable)
On Error GoTo ProcErr
If Not tbl Is Nothing Then
With tbl
If (.Attributes And (dbAttachedTable Or dbAttachedODBC)) = 0 Then
Err.Raise vbObjectError + 2, , "Local table exists with this name"
End If
If ForceRefresh _
Or (.SourceTableName <> SourceTableName) _
Or (.Connect <> sConnect) Then
db.TableDefs.Delete LocalTable
Else
GoTo ProcEnd
End If
End With
End If
Set tbl = db.CreateTableDef(LocalTable)
tbl.SourceTableName = SourceTableName
tbl.Connect = sConnect
db.TableDefs.Append tbl
ProcEnd:
LinkTable = ErrSts
Exit Function
ProcErr:
With Err
ErrSts = .Number
MsgBox "Error linking table '" & SourceTableName & vbCrLf & .Description
End With
Resume ProcEnd
End Function
It is more robust than some other methods you could use, like
TransferDatabase.
(Note that you need a reference to DAO. or the db and tdf variables need to
be declared "As Object")
The path where your mdb resides is returned by CurrentProject.Path, so you
could use it like this:
Call LinkTable( "MyTable"; CurrentProject.Path & "\MyDb.mdb" )
That will always look for the back end data file in the same folder as the
front end.
Or, if you want to move it independently, you could store the path to the
back end either as a registry setting, of in an INI file.
--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand
Ladi said:
Dear friends,
Becouse the system is based on different files on the same folder, I'm
wondering to know wether is the possibility to link tables on start up so
the folder can be moved or renamed and the Access application can always
find the access data file. Please note that I use binding on forms to
tables (no variables).
Thanks
Ladi
