ah, yes, well.....
I hit the same snag. had to make a 'firewall', and copy essential linked-table data to a local table.
This code let me do it behind the scenes.
Public Sub CloseTables()
Dim myTable As AccessObject
With CurrentData
For Each myTable In .AllTables
If myTable.IsLoaded Then
DoCmd.Close acTable, myTable.Name, acSaveYes
End If
Next
End With
End Sub
Function DeleteTable(myTable As Table) As Boolean
Dim myFlag As Boolean
myFlag = True
On Error GoTo DeleteTable_Err
DoCmd.Close acTable, myTable, acSaveYes
DoCmd.DeleteObject acTable = acDefault, myTable
DeleteTable = myFlag
Exit Function
DeleteTable_Err:
If Err = 7874 Then
Resume Next
Else
MsgBox Error, Err
myFlag = False
End If
End Function
Sub DelRel(PriTable As String)
Dim dB As DAO.Database
Dim rel As DAO.Relation
Dim myFlag As Boolean
Set dB = CurrentDb()
Do
myFlag = True
For Each rel In dB.Relations
If rel.Table = PriTable Then
'Debug.Print vbLf & "delete " & rel.Name
dB.Relations.Delete rel.Name
myFlag = False
End If
Next
Loop Until myFlag = True
End Sub
Public Sub SetRelationship(primTable As String, secTable As String, primField As String, secField As String)
'set relationship between two tables
' one to many:
' with Referential integrity (on primary key field?)
' cascade update and delete are disabled
Dim dB As DAO.Database
Dim rel As DAO.Relation
Dim fld As DAO.Field
Dim myName As String
Set dB = CurrentDb()
myName = primTable & secTable
Set rel = dB.CreateRelation(myName) 'Create a new relation.
'Define its properties.
With rel
.Table = primTable 'Specify the primary table.
.ForeignTable = secTable 'Specify the related table.
'Specify attributes for cascading updates and deletes.
'.Attributes = dbRelationUpdateCascade + dbRelationDeleteCascade
'Add the fields to the relation.
Set fld = .CreateField(primField) 'Field name in primary table.
fld.ForeignName = secField 'Field name in related table.
.Fields.Append fld 'Append the field.
End With
'Save the newly defined relation to the Relations collection.
dB.Relations.Append rel
End Sub
Public Function UpdateSalesStaffTable()
'close all open tables
Call TableManagement.CloseTables
'delete relationships
Call TableManagement.DelRel("tblSalesStaff")
'refresh table
Call TableManagement.UpdateTable
'Reset relationships
Call TableManagement.SetRelationship("tblSalesStaff", "WeeklyKPIs", "StaffID", "StaffName")
Call TableManagement.SetRelationship("tblSalesStaff", "TeamMembers", "StaffID", "TeamMember")
Call TableManagement.SetRelationship("tblSalesStaff", "MonthlyKPIs", "StaffID", "StaffID")
End Function
Public Sub UpdateTable()
'make local, stand-alone table of sales staff
Dim myDB As DAO.Database
Dim mySQL As String
Set myDB = CurrentDb()
'create SQL string
mySQL = "SELECT Staff.ID AS StaffID, Staff.FirstName, Staff.Surname, " _
& "[Firstname] & "" "" & [Surname] AS cName, Offices.Location, Organisations.OrgCode " _
& "INTO tblSalesStaff " _
& "FROM Organisations INNER JOIN (Offices INNER JOIN Staff ON Offices.ID = Staff.Office) " _
& "ON (Organisations.ID = Staff.Org) AND (Organisations.ID = Offices.lOrg) " _
& "WHERE (((Staff.HasSalesReporting)=True));"
'run update
If DeleteTable("tblSalesStaff") = False Then
'delete failed
MsgBox ("Warning: Update tblSalesStaff failed")
Exit Sub
End If
myDB.Execute mySQL
'set primary key column
myDB.Execute "CREATE INDEX NewIndex ON tblSalesStaff(StaffID) With PRIMARY"
End Sub