M
Mark
How can I programatically delete table relationships? Any
help would be greatly appreciated.
help would be greatly appreciated.
J
John Vinson
Sub KillAllRelations()
Dim db As DAO.Database
Dim rel As Relation
Dim inti As Integer
Set db = DBEngine(0)(0)
For inti = db.Relations.Count - 1 To 0 Step -1
Set rel = db.Relations(inti)
Debug.Print "Deleting relation "; rel.Name, rel.Table, _
rel.ForeignTable
db.Relations.Delete rel.Name
Next inti
End Sub
Adapt as needed...
T
Tim Ferguson
strSQL = "ALTER TABLE Airlines DROP CONSTRAINT fkDelivers;"
db.Exeucute strSQL, dbFailOnError
HTH
Tim F
B
Bas Cost Budde
Tim said:
Does that tackle non-enforcing relationships too? (what are they, anyway?)
T
Tim Ferguson
Umm: if I knew the answer to the second question, then I'd know the answer
to the first one. You'll have to get Bill Gates on that, since there is no
reference to "non enforcing relationships" in any database book I have ever
read.
B Wishes
Tim F
B
Bas Cost Budde
Tim said:
Upon further pondering I notice that 'constraint' more or less literally
means a limitation, whereas non-enforcing feels like optional. They are
contradictory.
I have been unable creating real relationships between queries and
tables, and used the simple lines at that time to indicate there is some
sort of link.
I usually store people and organisations in two tables, but use the
union for contacts; this union has a unique key and I want to use that
in many places as foreign key. But that is a little too sophisticated
for poor Jet, I suppose.
T
Tim Ferguson
No: the point is that there is no such thing as an unenforced relationship.
A relationship is a constraint: its one and only purpose is to control what
can be entered into a field, and to prevent illegal deletions or amendments
in the target table.
Anything else (that you can draw in the Access Relationships window) is
just a hint to the query designer, and has no place in the database schema.
It is a severe nuisance of Access's, that it regularly confuses Database
issues with User Interface ones. Another example is the mixing up of Unique
Indexes (a database decision) with non-unique ones (user interface). See
the table design window where there is a complete dog's breakfast of
DataType and Size, DefaultValue, Required, ValidationRule (all data) with
InputMask, Description, Format, ValidationRuleText etc (all UI). Of course
the crowning glories are Autofill and the poxy LookUp Field. And so on.
These are the kind of things that preserve Access's reputation as a toy
database, which is unfair because underneath all the froth is a very solid,
very flexible db engine. But the poorly thought out user issues are as
annoying to the literati as they are confusing to the beginner. The only
people they are good for is the marketing department.
There, got that off my chest (again...)
All the best
Tim F