Programmatically link tables

[Guest]

Doe anyone know how to programmatically create a link between 2 tables and
also break a link between 2 tables?

Thank you,

Sarah

 

[Albert D. Kallal]

Doe anyone know how to programmatically create a link between 2 tables and
also break a link between 2 tables?

Thank you,

Sarah

You don't create a link between two tables with code, so, the above question
is VERY VERY VERY confusing.

Normally, when you speak of a linked table, you are tabling about creating a
link to ANOTHER mdb file that contains table. When you create this link, you
are then able to use that table in the mdb file. This concept of linking
tables via code HAS NOTHING TO DO WITH linking two tables together.

If you are in need to have two tables linked, then you would build a query
in the query builder, and not use code.

You might want to clear up exactly what you are trying to do here, but you
seem to be confusing the issue of a relational joins (link two tables), and
that of creating a link in one mdb database file that points to another
table in a different mdb file.

So, are you trying to create a link in one database file that looks to
another mdb file, or are actually talking about a relation join of data
between two tables in the same mdb file?

note that the concept of linked tables *usually* refers if you split your
database. You should read the following article of mine, as I explain what
this split database, and the concept of "linked" tables are here:

http://www.members.shaw.ca/AlbertKallal/Articles/split/index.htm

 

[Guest]

Thanks for the clarification Albert. I am talking about a relational join of
data
between two tables in the same mdb file. Is there any way of doing this in
code without physically going to the Relationships screen and making the join?

 

[Albert D. Kallal]

Thanks for the clarification Albert. I am talking about a relational join
of
data
between two tables in the same mdb file. Is there any way of doing this
in
code without physically going to the Relationships screen and making the
join?

You can create a relation in code, but I would have wonder why this is
needed?

I mean, you *can* write code to create a form, but 99.9999% of people use
the forms designer for this!

If you have a design that requites you to change relationships in code, then
I have to say that you have a design headed for disaster!!!
(this is reason for dismissal from my development team if this is to be done
for no reason at all)

In a production environment, needing code to create relationships is rather
difficult to maintain. However, it is possible that you are attempting to
update existing designs "in use", and thus need to use code in place of the
relationships design window (but, you better have one REALLY terrific reason
for doing this). So, now that I given you some harsh words on this, and you
no doubt justified your case, here is what the code looks like:

Dim nF As DAO.Field
Dim nT As DAO.TableDef
Dim nR As DAO.Relation
dim db As DAO.DataBase

Set db = currentdb()

' add relation

Set nT = db.TableDefs("tblGroupRemind")
Set nR = db.CreateRelation("ContactIDRI", "Contacts",
"tblGroupRemind", dbRelationDeleteCascade + dbRelationLeft)

nR.Fields.Append nR.CreateField("ContactID") ' parent table PK
nR.Fields("ContactID").ForeignName = "ContactID" ' child table FK
db.Relations.Append nR
db.Relations.Refresh

Set nT = Nothing
Set nR = Nothing
db.Close


So, the above creates a relation from the parent table "contacts" to a
child table tblgroupRemind. You will want to check out the help on
createrelation. Note how I made the join a left join (about 90% of my joins
are left joins), and also cascade deletes is set in the above..

 

[Guest]

Albert that's part what I'm looking for. I also need to know how to delete
the relational join. The reason why I'm doing this is because I have a
microsoft access database which has a front-end and a back-end(data). These
databases are currently being used by people outside of the company I work
for. I need to update everyones back-end with the following change:

Delete a relational join between 2 tables
Change the field type for one of the fields used in the join
Then re-set the relational join of the 2 tables

Because I am doing this for multiple users, I wanted to be able to set up a
routine in the front-end which would allow them to select their back-end data
file (they may have multiple data files) and the routine I wrote would fix
the data file.

 

[Marshall Barton]

In addition to Albert's "harsh words", you should be aware
that any table can be joined to any **related** table simply
by using them in a query with the desired type of Join. The
query then can be used as a virtual table in any other
query/form/report.

True relationships are intended for the purpose of enforcing
referential integrity. Enforcing referential integrity
makes no sense in a situations where the relationships are
changed on the fly in a running application. The only time
where this is the right thing to do is as part of a one time
installtion process where you must add a new related table
to an existing backend database. [Well, there are a couple
of other situations, but they are rather esoteric.]

 

[Guest]

Thanks for all your help and comments Albert. Greatly appreciated.

 

[Guest]

Thanks for your comments Marshall. Greatly appreciated.

In addition to Albert's "harsh words", you should be aware
that any table can be joined to any **related** table simply
by using them in a query with the desired type of Join. The
query then can be used as a virtual table in any other
query/form/report.

True relationships are intended for the purpose of enforcing
referential integrity. Enforcing referential integrity
makes no sense in a situations where the relationships are
changed on the fly in a running application. The only time
where this is the right thing to do is as part of a one time
installtion process where you must add a new related table
to an existing backend database. [Well, there are a couple
of other situations, but they are rather esoteric.]
--
Marsh
MVP [MS Access]

Thanks for the clarification Albert. I am talking about a relational join of
data
between two tables in the same mdb file. Is there any way of doing this in
code without physically going to the Relationships screen and making the join?

 

[Albert D. Kallal]

Good stuff....I have all kinds of code that updates the back end.

(that code snip I showed you was from a production application, and it
attaches to the back end...and modifies the structure...

Delete a relational join between 2 tables

For the above, you have to know the name of the relation.

Also, note that when you do this, your "db" reference can be set to the back
end. In other words, that example code I showed you will NOT work on linked
tables. You have to open the back directly in code.

So, in place of:

set db = currentdb

You can use:


Set db = opendatabase("path to back end db")

Typically, I have a function I use to get the path name to the back end:

Function strBackEnd() As String

Dim mytables As TableDef

Dim strTempBack As String
Dim strFullPath As String
strFullPath = ""

For Each mytables In CurrentDb.TableDefs
If Left(mytables.Connect, 10) = ";DATABASE=" Then
strFullPath = Mid(mytables.Connect, 11)
Exit For
End If
Next mytables

strBackEnd = strFullPath

End Function

' So, to delete a relationship...

You *have* to check the name of the relationship. It is *usually* the parent
table name + the child table name...

set db = openDatabase (strBackEnd)

db.Relations.Delete "tableContactsChildTableName"

db.relations.Refresh

db.close