VB6 - Create Relationship in Access Using DAO/Access Object Model

I didnt see anything on creating an Access Relationship when
I searched, so here is my code to create a relationship.

It also creates a new blank Access database, Add 2 new tables,
creates new fields, set primary keys, and then its creates a
relationship between the two tables based on the primary keys.

Works on Access versions 2000 +.
I don't have Access 97 anymore so un-tested on 97.

VB Code:

Option Explicit
'Add reference to MS Access xx.0 Object Library
'Add reference to DAO 3.x Object Library
Private oApp As Access.Application
 
Private Sub Form_Load()
    
    Dim oRel As DAO.Relation
    Dim oDB As DAO.Database
    Dim oTable1 As DAO.TableDef
    Dim oTable2 As DAO.TableDef
    Dim oIndex As DAO.Index
    
    'Create new blank access database
    Set oApp = New Access.Application
    oApp.NewCurrentDatabase App.Path & "\MyDB2.mdb"
    Set oDB = oApp.CurrentDb
    oApp.Visible = True
    
    'Create first table (Table1)
    Set oTable1 = oDB.CreateTableDef("Table1")
    With oTable1
        .Fields.Append .CreateField("Field1", dbInteger)
        .Fields.Append .CreateField("Field2", dbText)
        .Fields.Append .CreateField("Field3", dbText)
        .Fields.Append .CreateField("Field4", dbText)
    End With
    oDB.TableDefs.Append oTable1
    
    'Create an index on Table1
    Set oIndex = oTable1.CreateIndex
    With oIndex
        .Name = "Field1Index"
        .Fields.Append .CreateField("Field1")
        .Primary = True
    End With
    oTable1.Indexes.Append oIndex
    
    'Create second table (Table2)
    Set oTable2 = oDB.CreateTableDef("Table2")
    With oTable2
        .Fields.Append .CreateField("Field1", dbInteger)
        .Fields.Append .CreateField("Field2", dbText)
        .Fields.Append .CreateField("Field3", dbText)
        .Fields.Append .CreateField("Field4", dbText)
    End With
    oDB.TableDefs.Append oTable2
    
    'Create an index on Table2
    Set oIndex = Nothing
    Set oIndex = oTable2.CreateIndex
    With oIndex
        .Name = "Field1Index"
        .Fields.Append .CreateField("Field1")
        .Primary = True
    End With
    oTable2.Indexes.Append oIndex
    
    'Create relationship between table1 and table2
    Set oRel = oApp.CurrentDb.CreateRelation("MyRelationship", "Table1", "Table2", dbRelationLeft Or dbRelationUpdateCascade Or dbRelationDeleteCascade)
    oRel.Fields.Append oRel.CreateField("Field1")
    oRel.Fields("Field1").ForeignName = "Field1"
    oApp.CurrentDb.Relations.Append oRel
 
    MsgBox "Done!"
    Unload Me
    
End Sub

hello rob,


hello ro b it was nice joining u. can u help me that how can i get refrence to ms access xx.o lib I am using vb 6.0 and I an reading ur code and also side by side creating for my rememberance with the help of ur code. i wanted to know the dll required for the ms access lib . i will be obliged if u helpp me than u very much in advance for ur support.



yours new mate
hemal

You can add the references (if you have Access installed on your system) by going to
Project > References... > and selecting "MS Access xx.0 Object Library" and "MS DAO 3.x Object Library"

They should show in the references list automatically. If you need to browse for them then the files are:
?:\Program Files\Microsoft Office\Officexx\MSACC.OLB
?:\Program Files\Common Files\Microsoft Shared\DAO\daoxx0.dll

Many thanks for u code. I tried to run it in access 2000 but when i click on the button i get this error:

run-tim eror'424':

object reuired

-------------------------------------------------------
And when i debug it it points to this line :

oApp.NewCurrentDatabase App.Path & "\MyDB2.mdb"


I be happy if u help me how to fix this error. I have a button on my form that i click on it it should run this code.Furthermore, should i make any table to store the reletionships ? Thank u and looking forward to your reply.
--------------------------------------------------------

This is the code :

Option Compare Database


'Add reference to MS Access xx.0 Object Library
'Add reference to DAO 3.x Object Library
Private oApp As Access.Application



Private Sub Command0_Click()
Dim oRel As DAO.Relation
Dim oDB As DAO.Database
Dim oTable1 As DAO.TableDef
Dim oTable2 As DAO.TableDef
Dim oIndex As DAO.Index

'Create new blank access database
Set oApp = New Access.Application
oApp.NewCurrentDatabase App.Path & "\MyDB2.mdb"
Set oDB = oApp.CurrentDb
oApp.Visible = True

'Create first table (Table1)
Set oTable1 = oDB.CreateTableDef("Table1")
With oTable1
.Fields.Append .CreateField("Field1", dbInteger)
.Fields.Append .CreateField("Field2", dbText)
.Fields.Append .CreateField("Field3", dbText)
.Fields.Append .CreateField("Field4", dbText)
End With
oDB.TableDefs.Append oTable1

'Create an index on Table1
Set oIndex = oTable1.CreateIndex
With oIndex
.Name = "Field1Index"
.Fields.Append .CreateField("Field1")
.Primary = True
End With
oTable1.Indexes.Append oIndex

'Create second table (Table2)
Set oTable2 = oDB.CreateTableDef("Table2")
With oTable2
.Fields.Append .CreateField("Field1", dbInteger)
.Fields.Append .CreateField("Field2", dbText)
.Fields.Append .CreateField("Field3", dbText)
.Fields.Append .CreateField("Field4", dbText)
End With
oDB.TableDefs.Append oTable2

'Create an index on Table2
Set oIndex = Nothing
Set oIndex = oTable2.CreateIndex
With oIndex
.Name = "Field1Index"
.Fields.Append .CreateField("Field1")
.Primary = True
End With
oTable2.Indexes.Append oIndex

'Create relationship between table1 and table2
Set oRel = oApp.CurrentDb.CreateRelation("MyRelationship", "Table1", "Table2", dbRelationLeft Or dbRelationUpdateCascade Or dbRelationDeleteCascade)
oRel.Fields.Append oRel.CreateField("Field1")
oRel.Fields("Field1").ForeignName = "Field1"
oApp.CurrentDb.Relations.Append oRel

MsgBox "Done!"
Unload Me

End Sub

I replied in your thread in the VBA forum ;)

Hi There

I know this is quite an old thread but it does pretty much exactly what I want to do.
I would like to create new tables but to load an existing database and create the relationship to an existing table.
The way I have tried this is to "OpenDatabase" but it doesn't seem to like that.
It would be great if you had any suggestions.

thanks

There is a CreateDatabase method too. Its a child method of the DBEngine object.

Thanks for that, I will give it a try