db.CreateRelation problem

meadt

Registered User.
Local time
Today, 23:03
Joined
Oct 29, 2009
Messages
45
Am trying to create a relationship between two tables in VBA using:

Dim rel As Relation

Set rel = db.CreateRelation("AtoB", "[Data Conversion].Primary", "[Data Word Table].[Primary Key]")

Ive tried several varients, but cannot get it to work. At the moment when i run it, it comes up with:

Run-time error '424':
Object required

I'm completely stumped by this, I can't even find what a error 424 is let alone how to fix it!

Again, any help is greatly appreciated!
 
See if this example helps:
Code:
Function CreateRelationDAO()
    Dim db As DAO.Database
    Dim rel As DAO.Relation
    Dim fld As DAO.Field
    
    'Initialize
    Set db = CurrentDb()
    
    'Create a new relation.
    Set rel = db.CreateRelation("tblDaoContractortblDaoBooking")
    
    'Define its properties.
    With rel
        'Specify the primary table.
        .Table = "tblDaoContractor"
        'Specify the related table.
        .ForeignTable = "tblDaoBooking"
        'Specify attributes for cascading updates and deletes.
        .Attributes = dbRelationUpdateCascade + dbRelationDeleteCascade
        
        'Add the fields to the relation.
        'Field name in primary table.
        Set fld = .CreateField("ContractorID")
        'Field name in related table.
        fld.ForeignName = "ContractorID"
        'Append the field.
        .Fields.Append fld
        
        'Repeat for other fields if a multi-field relation.
    End With
    
    'Save the newly defined relation to the Relations collection.
    db.Relations.Append rel
    
    'Clean up
    Set fld = Nothing
    Set rel = Nothing
    Set db = Nothing
    Debug.Print "Relation created."
End Function
 
Great code...

by the way, how to import the relationships to new database? suppose if database is corrupted and somebody wants to create new database from the scratch importing all the tables structures and queries etc.
 
suppose if database is corrupted and somebody wants to create new database from the scratch importing all the tables structures and queries etc.
If it is truly corrupted, you would NOT want to copy anything but data! You would use the last good backup and then manually recover any new data if possible. I have never been able to automate in over 15+ years of repairing other's databases. Note: 99% of the time the corrupted databases are not split into a front end and back end.

... and queries etc.
:confused: why would there be anything but tables in the back end? :confused:

Are you splitting your database?
 
I am using the be and fe technique for all my applications, and gets daily automatic backups/compact/repair etc.

I seldom use query on be in special case if i need some results, my be has very complex relationships window.

last time one of my table's index key got corrupted and the record become chines (no compact repair) so I create a new database and import the data.

when I check the relationship window, i got shock...it was the jungle of relationships. I decided to put back the corrected table to the original database... :)
 
It is really great to here that you have your databases are split and that you do regular backups!

When you import all the tables and relationships into a new database, it has been my experience with ever version of Access form 2.0 to 2007 that you lose the customized layout changes in the relationship windows.

Because of this, I do not even change the layout in the back end at all.

TIP: I do it in the master front end! You can not change the relationships in the front end, but you can view them.
 
TIP: I do it in the master front end! You can not change the relationships in the front end, but you can view them.

How you do/brings the customized be relationships layout in the MASTER front end?
 
How you do/brings the customized be relationships layout in the MASTER front end?

As previously state, "I do not even change the layout in the back end at all." because " it has been my experience with ever version of Access form 2.0 to 2007 that you lose the customized layout changes in the relationship windows."

As far as I know, there is not any way to import the custom layout between database. Only guessing here, but it is probably because of the internal IDs for the objects change when importing.
 
it is probably because of the internal IDs for the objects change when importing.
you are absolutely right HiTech!
its because the objects IDs when you are importing or linking tables. the example is, if you re-name any link table in front end, it does not effect the data and links of original table in the back end database. Their internal objects ids do the jobs
 
Hi HiTech,

Thanks for your help, its nearly working, however i'm getting the error 'No unique index found for the referenced field of the primary table' on line:

db.Relations.Append rel

Any ideas?
 
Unfortunately, without seeing more of the code before this line, I can cot see the issue. Please post all the code for this task.
 
Hi guys,

Thanks for your help on this i had another look at the origional code you gave me, HiTech and played around with it until i got it to work.

Here it is:

Dim Db As DAO.Database
Dim Rel As DAO.Relation
Dim Fld As Field

Set Db = CurrentDb()
Set Rel = Db.CreateRelation("myRelationship", "Data Conversion", "Data Word Table")

Set Fld = Rel.CreateField("Primary")

Fld.ForeignName = "Primary"
Rel.Fields.Append Fld
Db.Relations.Append Rel
Db.Relations.Refresh

Set Db = Nothing

Thanks again,
 

Users who are viewing this thread

Back
Top Bottom