Code for finding name of relationships

Dixie

I need to delete some relationships in code. How do I know what the names
of those relationships are?

Nov 13 '05 #1

rkc

Dixie wrote:

I need to delete some relationships in code. How do I know what the names
of those relationships are?

Go to the Object Browser (View -> Object Browser) from within a code
module.

Select DAO from the libraries combo.

Type relation into the combo below it.

Click the binoculars.

Highlight Name in the 'Members of Relation' list.

Hit F1.

Read.

Look at the example.

Nov 13 '05 #2

Allen Browne

Loop through the Relations collection to find the relations on the table you
are interested in.

You can enumerate the relations like this:

Function ShowRel()
Dim db As DAO.Database
Dim rel As DAO.Relation
Dim fld As DAO.Field

Set db = CurrentDb()
For Each rel In db.Relations
Debug.Print rel.Name, rel.Table, rel.ForeignTable, rel.Attributes
For Each fld In rel.Fields
Debug.Print , fld.Name, fld.ForeignName
Next
Next
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Dixie" <di***@dogmail.com> wrote in message
news:43********@duster.adelaide.on.net...

I need to delete some relationships in code. How do I know what the names
of those relationships are?

Nov 13 '05 #3

Dixie

When I do that Allen, I get relationship names of this form.

{408335A5-52AC-4EC1-8ED3-A8629ED0C2FD} Stud Faculty 4352
ID ID

What does the 4352 stand for. I also get numbers like 256 and 2 in this
column.
How on earth am I going to delete them in code?

dixie

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:43***********************@per-qv1-newsreader-01.iinet.net.au...

Loop through the Relations collection to find the relations on the table
you are interested in.

You can enumerate the relations like this:

Function ShowRel()
Dim db As DAO.Database
Dim rel As DAO.Relation
Dim fld As DAO.Field

Set db = CurrentDb()
For Each rel In db.Relations
Debug.Print rel.Name, rel.Table, rel.ForeignTable, rel.Attributes
For Each fld In rel.Fields
Debug.Print , fld.Name, fld.ForeignName
Next
Next
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Dixie" <di***@dogmail.com> wrote in message
news:43********@duster.adelaide.on.net...

I need to delete some relationships in code. How do I know what the names
of those relationships are?

Nov 13 '05 #4

Allen Browne

That would be correct. Some of the relationship names created by Access will
be GUIDs. They may be hidden ones.

The 4th item on the Debug.Print line is the Attributes of the relation.
Place the cursor in the Attributes word and press F1 to get a listing of
what these numbers mean.

To delete the relations with code, use the Delete method of the Relations
collection. If you are trying to delete all relations in your database, this
code example shows how:
http://allenbrowne.com/DelRel.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Dixie" <di***@dogmail.com> wrote in message
news:43********@duster.adelaide.on.net...

When I do that Allen, I get relationship names of this form.

{408335A5-52AC-4EC1-8ED3-A8629ED0C2FD} Stud Faculty 4352
ID ID

What does the 4352 stand for. I also get numbers like 256 and 2 in this
column.
How on earth am I going to delete them in code?

dixie

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:43***********************@per-qv1-newsreader-01.iinet.net.au...

Loop through the Relations collection to find the relations on the table
you are interested in.

You can enumerate the relations like this:

Function ShowRel()
Dim db As DAO.Database
Dim rel As DAO.Relation
Dim fld As DAO.Field

Set db = CurrentDb()
For Each rel In db.Relations
Debug.Print rel.Name, rel.Table, rel.ForeignTable, rel.Attributes
For Each fld In rel.Fields
Debug.Print , fld.Name, fld.ForeignName
Next
Next
End Function
"Dixie" <di***@dogmail.com> wrote in message
news:43********@duster.adelaide.on.net...

I need to delete some relationships in code. How do I know what the
names of those relationships are?

Nov 13 '05 #5

Dixie

No, that is a bit too much. I don't want to delete all the relationships.
I have a table in an application that I need to totally replace for a new
version. It forms part of a relationship. I think I will need to delete
its relationships (there are two) before I can rename it, then replace it
with a new table and re-create the relationships with the new replacement
table.

I know I can delete relationships if I know what they are called, but it
appears that these GUID numbers are substituting for the relationship names.

Can you use these names as the name of the relationship?

dixie

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:43***********************@per-qv1-newsreader-01.iinet.net.au...

That would be correct. Some of the relationship names created by Access
will be GUIDs. They may be hidden ones.

The 4th item on the Debug.Print line is the Attributes of the relation.
Place the cursor in the Attributes word and press F1 to get a listing of
what these numbers mean.

To delete the relations with code, use the Delete method of the Relations
collection. If you are trying to delete all relations in your database,
this code example shows how:
http://allenbrowne.com/DelRel.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Dixie" <di***@dogmail.com> wrote in message
news:43********@duster.adelaide.on.net...

When I do that Allen, I get relationship names of this form.

{408335A5-52AC-4EC1-8ED3-A8629ED0C2FD} Stud Faculty 4352
ID ID

What does the 4352 stand for. I also get numbers like 256 and 2 in this
column.
How on earth am I going to delete them in code?

dixie

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:43***********************@per-qv1-newsreader-01.iinet.net.au...

Loop through the Relations collection to find the relations on the table
you are interested in.

You can enumerate the relations like this:

Function ShowRel()
Dim db As DAO.Database
Dim rel As DAO.Relation
Dim fld As DAO.Field

Set db = CurrentDb()
For Each rel In db.Relations
Debug.Print rel.Name, rel.Table, rel.ForeignTable, rel.Attributes
For Each fld In rel.Fields
Debug.Print , fld.Name, fld.ForeignName
Next
Next
End Function
"Dixie" <di***@dogmail.com> wrote in message
news:43********@duster.adelaide.on.net...
I need to delete some relationships in code. How do I know what the
names of those relationships are?

Nov 13 '05 #6

Allen Browne

In all probably, the GUID is the name of the relation.

When you create a relation with Referential Integrity, Access creates an
index named on the combination of the 2 table fields. But if that name is
already in use, it uses a GUID name.

So what you need to do is loop through the Relations collection, and locate
each Relation for your particluar table, and delete it.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Dixie" <di***@dogmail.com> wrote in message
news:43******@duster.adelaide.on.net...

No, that is a bit too much. I don't want to delete all the relationships.
I have a table in an application that I need to totally replace for a new
version. It forms part of a relationship. I think I will need to delete
its relationships (there are two) before I can rename it, then replace it
with a new table and re-create the relationships with the new replacement
table.

I know I can delete relationships if I know what they are called, but it
appears that these GUID numbers are substituting for the relationship
names.

Can you use these names as the name of the relationship?

dixie

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:43***********************@per-qv1-newsreader-01.iinet.net.au...

That would be correct. Some of the relationship names created by Access
will be GUIDs. They may be hidden ones.

The 4th item on the Debug.Print line is the Attributes of the relation.
Place the cursor in the Attributes word and press F1 to get a listing of
what these numbers mean.

To delete the relations with code, use the Delete method of the Relations
collection. If you are trying to delete all relations in your database,
this code example shows how:
http://allenbrowne.com/DelRel.html

"Dixie" <di***@dogmail.com> wrote in message
news:43********@duster.adelaide.on.net...

When I do that Allen, I get relationship names of this form.

{408335A5-52AC-4EC1-8ED3-A8629ED0C2FD} Stud Faculty 4352
ID ID

What does the 4352 stand for. I also get numbers like 256 and 2 in this
column.
How on earth am I going to delete them in code?

dixie

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:43***********************@per-qv1-newsreader-01.iinet.net.au...
Loop through the Relations collection to find the relations on the
table you are interested in.

You can enumerate the relations like this:

Function ShowRel()
Dim db As DAO.Database
Dim rel As DAO.Relation
Dim fld As DAO.Field

Set db = CurrentDb()
For Each rel In db.Relations
Debug.Print rel.Name, rel.Table, rel.ForeignTable,
rel.Attributes
For Each fld In rel.Fields
Debug.Print , fld.Name, fld.ForeignName
Next
Next
End Function
"Dixie" <di***@dogmail.com> wrote in message
news:43********@duster.adelaide.on.net...
>I need to delete some relationships in code. How do I know what the
>names of those relationships are?

Nov 13 '05 #7

Dixie

Allen, in your module supplied earlier in this set of messages:

Sub StandardProperties(strTableName As String)

There is a line which refuses to compile.

strCaption = ConvertMixedCase(fld.Name)

It stumbles on the ConvertMixedCase. What library are you using for that.
I have DAO3.6.

It doesn't really worry me, as I will probably not use Captions, but just
curious as to why it won't compile.

dixie
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:43**********************@per-qv1-newsreader-01.iinet.net.au...

In all probably, the GUID is the name of the relation.

When you create a relation with Referential Integrity, Access creates an
index named on the combination of the 2 table fields. But if that name is
already in use, it uses a GUID name.

So what you need to do is loop through the Relations collection, and
locate each Relation for your particluar table, and delete it.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Dixie" <di***@dogmail.com> wrote in message
news:43******@duster.adelaide.on.net...

No, that is a bit too much. I don't want to delete all the
relationships. I have a table in an application that I need to totally
replace for a new version. It forms part of a relationship. I think I
will need to delete its relationships (there are two) before I can rename
it, then replace it with a new table and re-create the relationships with
the new replacement table.

I know I can delete relationships if I know what they are called, but it
appears that these GUID numbers are substituting for the relationship
names.

Can you use these names as the name of the relationship?

dixie

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:43***********************@per-qv1-newsreader-01.iinet.net.au...

That would be correct. Some of the relationship names created by Access
will be GUIDs. They may be hidden ones.

The 4th item on the Debug.Print line is the Attributes of the relation.
Place the cursor in the Attributes word and press F1 to get a listing of
what these numbers mean.

To delete the relations with code, use the Delete method of the
Relations collection. If you are trying to delete all relations in your
database, this code example shows how:
http://allenbrowne.com/DelRel.html

"Dixie" <di***@dogmail.com> wrote in message
news:43********@duster.adelaide.on.net...
When I do that Allen, I get relationship names of this form.

{408335A5-52AC-4EC1-8ED3-A8629ED0C2FD} Stud Faculty 4352
ID ID

What does the 4352 stand for. I also get numbers like 256 and 2 in
this column.
How on earth am I going to delete them in code?

dixie

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:43***********************@per-qv1-newsreader-01.iinet.net.au...
> Loop through the Relations collection to find the relations on the
> table you are interested in.
>
> You can enumerate the relations like this:
>
> Function ShowRel()
> Dim db As DAO.Database
> Dim rel As DAO.Relation
> Dim fld As DAO.Field
>
> Set db = CurrentDb()
> For Each rel In db.Relations
> Debug.Print rel.Name, rel.Table, rel.ForeignTable,
> rel.Attributes
> For Each fld In rel.Fields
> Debug.Print , fld.Name, fld.ForeignName
> Next
> Next
> End Function
> "Dixie" <di***@dogmail.com> wrote in message
> news:43********@duster.adelaide.on.net...
>>I need to delete some relationships in code. How do I know what the
>>names of those relationships are?

Nov 13 '05 #8

Dixie

I used the code from your site to delete all relationships in a copy of the
database - works well :-) Then I reformed every relationship and the proper
names including both tables in the name were formed. I am not sure why the
current copy had all those GUIDs in the first place, but deleting them and
reforming them fixes that up. That may solve my problem. It just means I
have to have code to individually reform lots of relationships.

dixie

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:43**********************@per-qv1-newsreader-01.iinet.net.au...

In all probably, the GUID is the name of the relation.

When you create a relation with Referential Integrity, Access creates an
index named on the combination of the 2 table fields. But if that name is
already in use, it uses a GUID name.

So what you need to do is loop through the Relations collection, and
locate each Relation for your particluar table, and delete it.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Dixie" <di***@dogmail.com> wrote in message
news:43******@duster.adelaide.on.net...

No, that is a bit too much. I don't want to delete all the
relationships. I have a table in an application that I need to totally
replace for a new version. It forms part of a relationship. I think I
will need to delete its relationships (there are two) before I can rename
it, then replace it with a new table and re-create the relationships with
the new replacement table.

I know I can delete relationships if I know what they are called, but it
appears that these GUID numbers are substituting for the relationship
names.

Can you use these names as the name of the relationship?

dixie

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:43***********************@per-qv1-newsreader-01.iinet.net.au...

That would be correct. Some of the relationship names created by Access
will be GUIDs. They may be hidden ones.

The 4th item on the Debug.Print line is the Attributes of the relation.
Place the cursor in the Attributes word and press F1 to get a listing of
what these numbers mean.

To delete the relations with code, use the Delete method of the
Relations collection. If you are trying to delete all relations in your
database, this code example shows how:
http://allenbrowne.com/DelRel.html

"Dixie" <di***@dogmail.com> wrote in message
news:43********@duster.adelaide.on.net...
When I do that Allen, I get relationship names of this form.

{408335A5-52AC-4EC1-8ED3-A8629ED0C2FD} Stud Faculty 4352
ID ID

What does the 4352 stand for. I also get numbers like 256 and 2 in
this column.
How on earth am I going to delete them in code?

dixie

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:43***********************@per-qv1-newsreader-01.iinet.net.au...
> Loop through the Relations collection to find the relations on the
> table you are interested in.
>
> You can enumerate the relations like this:
>
> Function ShowRel()
> Dim db As DAO.Database
> Dim rel As DAO.Relation
> Dim fld As DAO.Field
>
> Set db = CurrentDb()
> For Each rel In db.Relations
> Debug.Print rel.Name, rel.Table, rel.ForeignTable,
> rel.Attributes
> For Each fld In rel.Fields
> Debug.Print , fld.Name, fld.ForeignName
> Next
> Next
> End Function
> "Dixie" <di***@dogmail.com> wrote in message
> news:43********@duster.adelaide.on.net...
>>I need to delete some relationships in code. How do I know what the
>>names of those relationships are?

Nov 13 '05 #9

rkc

Dixie wrote:

I used the code from your site to delete all relationships in a copy of the
database - works well :-) Then I reformed every relationship and the proper
names including both tables in the name were formed. I am not sure why the
current copy had all those GUIDs in the first place, but deleting them and
reforming them fixes that up. That may solve my problem. It just means I
have to have code to individually reform lots of relationships.

Why don't you take what you have learned and write a procedure that
takes a table name as a parameter and deletes any relationship it
is involved in? You have been shown everything you need to know.

Nov 13 '05 #10

Allen Browne

It won't compile because ConvertMixedCase() is not a built-in function:

Function ConvertMixedCase(ByVal strIn As String) As String
'Purpose: Convert mixed case name into a name with spaces.
'Argument: String to convert.
'Return: String converted by these rules:
' 1. One space before an upper case letter.
' 2. Replace underscores with spaces.
' 3. No spaces between continuing upper case.
'Example: "FirstName" or "First_Name" => "First Name".
Dim lngStart As Long 'Loop through string.
Dim strOut As String 'Output string.
Dim boolWasSpace As Boolean 'Last char. was a space.
Dim boolWasUpper As Boolean 'Last char. was upper case.

strIn = Trim$(strIn) 'Remove leading/trailing spaces.
boolWasUpper = True 'Initialize for no first space.

For lngStart = 1& To Len(strIn)
Select Case Asc(Mid(strIn, lngStart, 1&))
Case vbKeyA To vbKeyZ 'Upper case: insert a space.
If boolWasSpace Or boolWasUpper Then
strOut = strOut & Mid(strIn, lngStart, 1&)
Else
strOut = strOut & " " & Mid(strIn, lngStart, 1&)
End If
boolWasSpace = False
boolWasUpper = True

Case 95 'Underscore: replace with space.
If Not boolWasSpace Then
strOut = strOut & " "
End If
boolWasSpace = True
boolWasUpper = False

Case vbKeySpace 'Space: output and set flag.
If Not boolWasSpace Then
strOut = strOut & " "
End If
boolWasSpace = True
boolWasUpper = False

Case Else 'Any other char: output.
strOut = strOut & Mid(strIn, lngStart, 1&)
boolWasSpace = False
boolWasUpper = False
End Select
Next

ConvertMixedCase = strOut
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Dixie" <di***@dogmail.com> wrote in message
news:43********@duster.adelaide.on.net...

Allen, in your module supplied earlier in this set of messages:

Sub StandardProperties(strTableName As String)

There is a line which refuses to compile.

strCaption = ConvertMixedCase(fld.Name)

It stumbles on the ConvertMixedCase. What library are you using for that.
I have DAO3.6.

It doesn't really worry me, as I will probably not use Captions, but just
curious as to why it won't compile.

dixie

Nov 13 '05 #11