Create Relationship using VBA

[Steve Conway]

Hi All,



I am trying to create a new table(NtblPANotes) and define a one-to-many
relationship to an existing table(NtblPA) in a backend db through code. The
new table creation is no problem, but, I can't seem to get the relationship
set. The related field is called PID in both tables. The code executes with
no errors, the table is created, but when I go to view relationships there
are none between this new NtblPANotes and NtblPA. My VBA help file is
toasted and have tried several times to reinstall/repair it to no avail. So,
I'm hoping someone here can point me to where I have gone awry. Code sample
is below



Thanks for your help

Steve C



Dim dbBE As Database

Dim tdfNew As DAO.TableDef

Dim rtls As Relations

Dim rtl As Relation

Dim strPath As String



strPath = Me.txtPath



'Create connections

Set dbBE = OpenDatabase(strPath)

Set rtls = dbBE.Relations



'Create New table "NtblPANotes"

Set tdfNew = dbBE.CreateTableDef("NtblPANotes")

With tdfNew

'Create fields and append them to the new TableDef object.

.Fields.Append .CreateField("PID", dbText, 100)

.Fields.Append .CreateField("Notes", dbMemo)

End With

dbBE.TableDefs.Append tdfNew

dbBE.TableDefs.Refresh

'Create Relationship

With rtls

Set rtl = dbBE.CreateRelation(, "NtblPA", "NtblPANotes",
dbRelationUpdateCascade + dbRelationDeleteCascade)

rtl.Fields!PID.ForeignName = "PID"

.Append rtl

.Refresh

End With

 

[Allen Browne]

Create the relation.
Set its properties and attributes.
Create the field(s) in the new relation.
Append the fields to the relation.
Append the relation to newly made the Relations collection.

Something like this:

Set rtl = dbBE.CreateRelation("NtblPaNtblPaNotes")
With rtl
.Table = "NtblPA"
.ForeignTable = "NtblPANotes"
.Attriburtes = dbRelationUpdateCascade + dbRelationDeleteCascade

'Set up the fields of the relation
Set fld = .CreateField("PID")
fld.ForeignName = "PID"
.Fields.Append fld
End with
'Append the relation
db.Relations.Append rtl

 

[Steve Conway]

Allen,

Thanks for the direction. Your sequence of events was the piece that put the
puzzle together for me.

Steve C