Question one to one relationship (1 Viewer)

InFlight

User
Local time
Tomorrow, 00:53
Joined
Jun 11, 2015
Messages
130
Hi
i am using the following code to create a relationship. But it creates a one to many, and i need a one to one

Code:
Public Function CreateRelation(primaryTableName As String, primaryFieldName As String, foreignTableName As String, foreignFieldName As String, RelationshipName As String) As Boolean


    On Error GoTo ErrHandler


    Dim Dbs As DAO.Database
    Dim newRelation As DAO.Relation
    Dim relatingField As DAO.Field
    Dim relationUniqueName As String
    
    relationUniqueName = RelationshipName
    
    Set Dbs = OpenDatabase(strMainData)
    
    'Arguments for CreateRelation(): any unique name,
    'primary table, related table, attributes.
    Set newRelation = Dbs.CreateRelation(relationUniqueName, _
        primaryTableName, foreignTableName)
    'The field from the primary table.
    Set relatingField = newRelation.CreateField(primaryFieldName)
    'Matching field from the related table.
    relatingField.ForeignName = foreignFieldName
    'Add the field to the relation's Fields collection.
    newRelation.Fields.Append relatingField
    'Add the relation to the database.
    Dbs.Relations.Append newRelation
    
    Dbs.Close
    Set Dbs = Nothing
    
    CreateRelation = True

        
    Exit Function

ErrHandler:
    MsgBox Err.Description + " (" + relationUniqueName + ")"
    CreateRelation = False
End Function
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:53
Joined
Oct 29, 2018
Messages
21,358
Hi. Are you linking both primary keys in the tables?
 

InFlight

User
Local time
Tomorrow, 00:53
Joined
Jun 11, 2015
Messages
130
One table is called Exhibitors with a field called ExhibitorID, AutoNumber
another is called Finance with a field called Fin_ExhibitorID, Number
Both are primary keys
 
Last edited:

isladogs

MVP / VIP
Local time
Today, 11:53
Joined
Jan 14, 2017
Messages
18,186
Do you get a 1:1 if you create the relationship manually?
Suggest you try Allen Browne's approach.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:53
Joined
Feb 19, 2002
Messages
42,981
While I believe in writing code to make a process more efficient, I'm not sure that this is one of those cases. usually, you would write code that could "derive" its input based on some pattern of table/column names or by using a table.

This piece of code requires 5 arguments, none of which can be derived so how many calling procedures would you need?

Also, I have a preference for using DDL wherever I can since It can be stored as queries and you can write a code loop that runs a bunch of specifically named queries or runs queries with a certain pattern in their names.

Here's some examples of making the relationship after the fact using an ALTER or making it when the table is created (two variations) and deleting it.
Code:
ALTER TABLE Orders
ADD FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);
Code:
CREATE TABLE Orders (
    OrderID int NOT NULL PRIMARY KEY,
    OrderNumber int NOT NULL,
    PersonID int FOREIGN KEY REFERENCES Persons(PersonID)
);

or

CREATE TABLE Orders (
    OrderID int NOT NULL,
    OrderNumber int NOT NULL,
    PersonID int,
    PRIMARY KEY (OrderID),
    CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID)
    REFERENCES Persons(PersonID)
);
Code:
ALTER TABLE Orders
DROP CONSTRAINT FK_PersonOrder;

The only time I have ever actually automated this table type of process is when doing a conversion. Complex conversions typically take multiple tries to complete successfully and so automating them makes it easier to get them right and then be able to run them as one final step when implementing the new application.
 

Users who are viewing this thread

Top Bottom