programmatically changing the attributes of a relation

[Stephen J. Levine]

How do I change a relationship in visual basic so that
referential integrity is enabled?

I have tried doing it as a relation, attempting to set the
attributes to 0. I have also tried to do it as an object,
setting the property "attributes" to 0.

Either way, I get the error message Runtime Operation
3219, Invalid operation.

My code is:

Option Compare Database
Option Explicit
Dim RmteDb As Database, Relship As Relation, i As Integer,
LocalDB As Database
Dim RelRst As Recordset, RelshipObj As Object

Function update_relationship()

Set RmteDb = OpenDatabase(CurrentProject.Path & "\OBI
EMTS Devl database.mdb")
Set LocalDB = CurrentDb
Set RelRst = LocalDB.OpenRecordset("relationships
query", dbOpenDynaset)

RelRst.MoveFirst
Do While Not RelRst.EOF
If RelRst!ReloldAttributes <> 0 Then
Set RelshipObj = RmteDb.Relations(RelRst!
RelName)
RelshipObj.Properties("Attributes") = 0
End If
Debug.Print Relship.Name, Relship.Table,
Relship.ForeignTable
Debug.Print Relship.Attributes
Debug.Print Relship.Properties.Count
Loop

Close
Debug.Print "Completed"
End Function

The line that fails is "RelshipObj.Properties
("Attributes") = 0"

I hope I do not have to remove and recreate the
relationship.

sjl

 

[Allen Browne]

I don't believe you can set the attributes after the relation exists.

Not too difficult to Delete and CreateRelation.

 

[TC]

I don't have Access here to check, so this is only a guess: Are you sure
that 0 is the correct value? There are named constants for the Attributes
property. Check them out in online help. Also check that the Attributes
property >can be< updated after the relation object has been appended to the
relevant collection. (If not, you'll have to use the "delete & recreate"
method.)

If that doesn't help, here's what I'd do. Rejig the code to work with a
local relation (just to make things simpler). Check that you can change that
relation >manually<. If so, change it back, then check that you can change
it thru code. Then try again with a remote relation.

Maybe the table data is preventing the change. Maybe you would see that if
you tried to change the relation manually.

HTH,
TC

 

[TC]

You asked this yesterday, & got several answers.

TC