Establish table relationships in VBA

[Bill (Unique as my name)]

Is is possible to establish table relationships in VBA?

Thanks in advance.

 

[Allen Browne]

You can programmatically created relationships iwth CreateRelation().

Thie example creates a relationship between tblConContractor and tblBooking,
so one contractor can have multiple bookings, with cascading updates and
deletes. Use any name for the relation itself.

The only odd-looking bit is CreateField(). This is not creating fields in
the table; just in the relation.

Sub CreateRelationDAO()
Dim db As DAO.Database
Dim rel As DAO.Relation
Dim fld As DAO.Field

Set db = CurrentDb()

'Create a new relation.
Set rel = db.CreateRelation("tblContractortblBooking")

'Define its properties.
With rel
'Specify the primary table.
.Table = "tblContractor"
'Specify the related table.
.ForeignTable = "tblBooking"
'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 Sub

 

[Bill (Unique as my name)]

Thank you so very much, Allen.

Is it also possible to dissolve table relationships in VBA?

side note:

From 1997 to 2000, I managed the computer systems for the Catholic

Outreach in Grand Junction, Colorado as a volunteer. The information
from the database system put to rest the belief that a large majority
of homeless people in the area were dependent on the charities instead
of using them to rebuild their lives. The statistics convinced city
administrators to generously increase their funding for many programs.
I turned the job over to a homeless programmer who advanced the
computer services beyond anything anyone could have imagined. The
catholic church administers the program, but every chuch and charity in
the valley participates in the decision making, contributions and
staffing. It was a very rewarding experience for me to participate in
this truly ecumenical enterprise, and I plan to continue volunteering
after I retire in ten years. I mention this only because on one of
your web sites you asked that people who benefit from your services
render charitable donations to compassionate programs. Been there.
Done that. Will do it again. Thanks again for your generous
assistance.

You can programmatically created relationships iwth CreateRelation().

Thie example creates a relationship between tblConContractor and tblBooking,
so one contractor can have multiple bookings, with cascading updates and
deletes. Use any name for the relation itself.

The only odd-looking bit is CreateField(). This is not creating fields in
the table; just in the relation.

Sub CreateRelationDAO()
Dim db As DAO.Database
Dim rel As DAO.Relation
Dim fld As DAO.Field

snip

 

[Allen Browne]

Sure. See this link:
http://allenbrowne.com/DelRel.html

Unless you are creating some kind of utility, constantly creating and
destroying relations sounds like a strange way to solve whatever problem you
are working on.

 

[Bill (Unique as my name)]

I work in a strange set of circumstances.

I have the same database on two computers on two different networks
that do not see
each other. I've automated exporting the flat tables, but the four
joined tables are a
problem. Once I back up the database, I plan to break the
relationships, export the
tables to a flash drive, reestablish the relationships, break the
relationships on the
other computer, export the tables and then reestablish the
relationships, via VBA.

my life is horrible

 

[Steve Schapel]

Bill,

Sorry to hear about your life.

But as regards your database(s), there's probably a simpler approach. I
assume these troublesome tables are not the only tables in the database?
If the purpose is to completely replace the 4 tables in the second
database with the 4 tables being imported from the first database
(that's what I have understood), then I would be inclined to focus on
replacing the data rather than the tables themselves. After bringing
the 4 tables over to the other PC via the USB drive, this would then
involve running code in the second database something like this...

Dim dbs As DAO.Database
Dim FileToImportFrom As String
Dim strSQL As String
Set dbs = DBEngine(0)(0)
FileToImportFrom = "C:\YourFolder\ImportedDatabase.mdb"
strSQL = "DELETE * FROM FirstTable"
dbs.Execute strSQL, dbFailOnError
strSQL = "DELETE * FROM SecondTable"
dbs.Execute strSQL, dbFailOnError
strSQL = "DELETE * FROM ThirdTable"
dbs.Execute strSQL, dbFailOnError
strSQL = "DELETE * FROM FourthTable"
dbs.Execute strSQL, dbFailOnError
strSQL = "INSERT INTO FirstTable" & _
" SELECT * FROM FirstTable IN '" & FileToImportFrom & "'"
dbs.Execute strSQL, dbFailOnError
strSQL = "INSERT INTO SecondTable" & _
" SELECT * FROM SecondTable IN '" & FileToImportFrom & "'"
dbs.Execute strSQL, dbFailOnError
strSQL = "INSERT INTO ThirdTable" & _
" SELECT * FROM ThirdTable IN '" & FileToImportFrom & "'"
dbs.Execute strSQL, dbFailOnError
strSQL = "INSERT INTO FourthTable" & _
" SELECT * FROM FourthTable IN '" & FileToImportFrom & "'"
dbs.Execute strSQL, dbFailOnError
Set dbs = Nothing

Ok, it's a bit repetitive, but I reckon beats the idea of trying to
transfer tables and then build relationships. Just a thought.

 

[Bill (Unique as my name)]

Ah! My life is so wonderful!

Thank you, Steve.

Bill,

Sorry to hear about your life.

But as regards your database(s), there's probably a simpler approach. I
assume these troublesome tables are not the only tables in the database?
If the purpose is to completely replace the 4 tables in the second
database with the 4 tables being imported from the first database
(that's what I have understood), then I would be inclined to focus on
replacing the data rather than the tables themselves. After bringing
the 4 tables over to the other PC via the USB drive, this would then
involve running code in the second database something like this...

snip