"Tym" <no*****@ictis. net> wrote in message
news:40******** *****@169.100.1 00.5...
OK - daft question of the day time...
If I have database A which contains all the live data, and Database B
which contains linked tables to those is A (i.e. a front end) is there
a way of seeing if any tables have been addedd to A and if so,
programatically creating a linked table to the new tables?
Failing this, can I programatically delete all linked tables, then
link re-link to every table which appears in tableA?
Does that make sense??
Tym
Why would you want to do that?
Do you think users are deleting linked tables from the front end?
Are you adding tables to the back end which you want to be automatically
included as linked tables in the front end?
Normally, if there are back end changes, then there are associated front end
changes. E.g. you add a new table called tblCustomerNote s then typically
there will be new or amended forms and reports to display this data.
Getting the front end to simply generate a new linked table for this will
generally not be enough.
However, if you need some coding ideas, these functions may help:
Public Function GetBackEndPath( ) As String
On Error GoTo Err_Handler
' Assumes there is a single back-end .mdb
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Set dbs = CurrentDb
For Each tdf In dbs.TableDefs
If Len(tdf.Connect ) > 0 Then
GetBackEndPath = Mid$(tdf.Connec t, 11)
Exit For
End If
Next tdf
Exit_Handler:
On Error Resume Next
Set tdf = Nothing
Set dbs = Nothing
Exit Function
Err_Handler:
' No error message
Resume Exit_Handler
End Function
Public Function DeleteLinks() As Boolean
On Error GoTo Err_Handler
Dim dbs As DAO.Database
Dim lng As Long
Set dbs = CurrentDb
With dbs.TableDefs
For lng = .Count - 1 To 0 Step -1
If (.Item(lng).Att ributes And dbAttachedTable ) <> 0 Then
.Delete .Item(lng).Name
End If
Next lng
End With
DeleteLinks = True
Exit_Handler:
On Error Resume Next
Set dbs = Nothing
Exit Function
Err_Handler:
MsgBox Err.Description , vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler
End Function
Public Function CreateLinks(str BEPath) As Boolean
On Error GoTo Err_Handler
Dim dbsFE As DAO.Database
Dim dbsBE As DAO.Database
Dim wksJET As DAO.Workspace
Dim strTableName As String
Dim strConnect As String
Dim tdfBE As DAO.TableDef
Dim tdfFE As DAO.TableDef
Set wksJET = DBEngine.Worksp aces(0)
Set dbsBE = wksJET.OpenData base(strBEPath)
Set dbsFE = CurrentDb
For Each tdfBE In dbsBE.TableDefs
If Left$(tdfBE.Nam e, 4) <> "MSys" And _
Len(tdfBE.Conne ct) = 0 Then
strTableName = tdfBE.Name
strConnect = ";DATABASE= " & strBEPath
Set tdfFE = dbsFE.CreateTab leDef(strTableN ame)
tdfFE.Connect = strConnect
tdfFE.SourceTab leName = strTableName
dbsFE.TableDefs .Append tdfFE
Set tdfFE = Nothing
End If
Next tdfBE
CreateLinks = True
Exit_Handler:
On Error Resume Next
Set tdfFE = Nothing
Set tdfBE = Nothing
Set dbsFE = Nothing
dbsBE.Close
Set dbsBE = Nothing
Set wksJET = Nothing
Exit Function
Err_Handler:
MsgBox Err.Description , vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler
End Function
