Create constraint on linked table

[doco]

I need to dynamically drop and create a foreign key restraint against a linked table. When attempting this I get

run-time error #'3613'
Cannot create a relationship on linked ODBC tables.

Yet I can easily create the same relationship on that linked table using the wizard.

Is there a way to drop and create relationships against linked tables using VBA/SQL?

This creates the error:

sqlCreateRelKey = "ALTER TABLE item_depreciation_input "
    sqlCreateRelKey = sqlCreateRelKey & "ADD CONSTRAINT rel_code_sched "
    sqlCreateRelKey = sqlCreateRelKey & "FOREIGN KEY ([pprop_category_cd]) REFERENCES code_table ([id]) "
    DoCmd.RunSQL sqlCreateRelKey

TIA

 

[georgedwilkinson]

You cannot change constraints on a linked table. The relationship you create in the relationship window is not a real constraint (at least on linked tables).

 

[doco]

Thanks.

Well maybe I am not asking the right question then. How may I replicate the pseudo constraint imposed by the wizard using VBA/SQL?

 

[georgedwilkinson]

I didn't desert you, just took a little while to do a little research.

I'm not finding a whole lot that is helpful. Possibly if you mess around with the MSysRelationships table (for which regular users have no permissions)? Please make sure you make backups first!

I'll keep looking and let you know if anything else comes up.

 

[LPurvis]

Hi

What wizard are you referring to?
The Relationship dialog when adding relationships in the Relationship Diagram?

As George indicated - they're not constraints as such. i.e. there is no constraint upon the tables you're free to enter anything you like - Referential Integrity is not (and cannot) be enforced.

The reason your code is failing is that, using SQL DDL, there is no way to create a relationship (foreign Key constraint) without enforcing referential integrity. The syntax doesn't support it.

You can use DAO though:

Sub AddRelationship(strTable As String, strFTable As String, strfield As String, strFField As String, strRelName As String, Optional intAttribute As DAO.RelationAttributeEnum = 2)
    
    Dim db As Database
    Dim Rel As DAO.Relation
    
    Set db = CurrentDb
    Set Rel = db.CreateRelation(strRelName, strTable, strFTable, dbRelationDontEnforce)
    
    With Rel
        .Fields.Append .CreateField(strfield)
        .Fields(strfield).ForeignName = strFField
        .Attributes = intAttribute
    End With
    
    db.Relations.Append Rel
    
End Sub

You need to choose the appropriate option for the attributes parameter though - in this case dbRelationDontEnforce. (You'll get prompted for it when typing the function).

For example something like:
AddRelationship "dbo_tblClients", "dbo_tblOrders", "ClientID", "ClientID", "Rel_blClients_tblOrders", dbRelationDontEnforce

But, to reiterate, that still does not give you any constraint on your data. (i.e. RI on the keys involved).
That must be defined in the target database.

Cheers.

 

[doco]

Yes! Indeed!. That worked marvelously. Thanks.


By the way, the 'wizard' I was referring to is the Relationships Window.