Create/Delete Relationships through VBA

[MarConEnt]

Hi,
I am creating an Access project and I want it to be as automated as possible for end users. On a certain step it has become necessary to create a one-to-many relationship: is this possible through VBA? I've looked everywhere...any help would be greatly appreciated.

 

[Remou]

Have you seen:
Create relationship by code
thread700-933677

 

[MarConEnt]

Hi,
No I hadn't...thanks!!

 

[MarConEnt]

Hi,
OK, I used this code and it produced no errors, but the relationship doesn't show under TOOLS | RELATIONSHIPS. Is it supposed to, and if not, how do I know it worked?

Thanks.

 

[Remou]

Hm, the relationship shows in the relationships window for me (Access 2000). Can you check the system table MSysRelationships, to see if your relationship shows there? If not, I fear there is some error, which means you should post your code.

 

[MarConEnt]

Hi Again,
Actually, I made a mistake: it was generating an error; I simply had it disabled. the code is as follows:

'Create Relationship for INS_CustomersOnly_Table - AP1DB2A_Table_Revised

Dim dbs As Database, rel As Relation, fld As Field

Set dbs = CurrentDb()
Set rel = dbs.CreateRelation("INS_AP1DB2A", "INS_CustomersOnly_Table", "AP1DB2A_Table_Revised")
Set fld = rel.CreateField("REC")
fld.ForeignName = "Reform_ID"
rel.Fields.Append fld
dbs.Relations.Append rel
dbs.Relations.Refresh
Set dbs = Nothing



The error generated is :

Run-time error '3201': You cannot add or change a record because a related record is required in table 'INS_Customers_Only_Table'


I am trying to create a One-to-Many relationship: the primary field is in the table 'Ins_CustomersOnly_Table' and it is called REC and is a primary key. The field I'm trying to ralate to is in the table 'AP1DB2A_Table_Revised" andis called "Reform_ID". It is not a primary key.

I am able to manually create this relationship, but it won't work with this code.

Thanks.

 

[Remou]

Hello again. I get the error you describe if the table AP1DB2A_Table_Revised contains a Reform_ID that does not exist as a REC in Ins_CustomersOnly_Tab. That is, I can create the relationship in the relationship window, but cannot run the code.

 

[MarConEnt]

Hi,

Is there a way around this? Do you have any idea why this may be happening? In the event that there is no way to create this relationship through code, would it be just as effective to create a join within a query instead for all of the queries that require this relationship?

Thanks!

 

[Remou]

The thing is, if you are missing REC codes from your main table (Ins_CustomersOnly_Tab), you cannot create a relationship using the above code. I would say that you need to add the missing REC codes to the main table, and so fix the problem, but perhaps I am missing some point?

 

[MarConEnt]

Hi,
I don't understand...what do you mean by REC codes? REC is a field (Primary Key) of the main table.

Thanks.

 

[Remou]

What I am trying to say is that you have data recorded in the Reform_ID field that are not recorded in the main table under REC. To illustrate:[tt]
Table: Ins_CustomersOnly_Tab AP1DB2A_Table_Revised
Field: REC Reform_ID
Sample Data: 123 123
456 123
567 ... missing 456
567[/tt]
If this is not the case, something else is causing the error you got.