473,320 Members | 2,029 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Code for finding name of relationships

I need to delete some relationships in code. How do I know what the names
of those relationships are?
Nov 13 '05 #1
10 6955
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
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
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
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
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
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
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
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
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

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Bryan Olson | last post by:
The current Python standard library provides two cryptographic hash functions: MD5 and SHA-1 . The authors of MD5 originally stated: It is conjectured that it is computationally infeasible to...
4
by: Jenni | last post by:
Hopefully someone out there can help. I am currently trying to write some code to allow me to delete a table, then recreate it and re-establish the relationships. I seem to have hit a snag in the...
8
by: Steve Jorgensen | last post by:
Hi folks, I'm posting this message because it's an issue I come up against relatively often, but I can't find any writings on the subject, and I haven't been able to figure out even what key...
8
by: Steve Jorgensen | last post by:
Hi all, I was wondering if anyone has been able to find a way to read layout information and manipulate the layout in the Relationships window. I've tried a few different angles, but couldn't...
3
by: GGerard | last post by:
Hello Does anyone know if it is possible with Access 2000 to create relationships between tables using code? Thanks G.Gerard
45
by: salad | last post by:
I'm curious about your opinion on setting relationships. When I designed my first app in Access I'd go to Tools/Relationships and set the relationships. Over time I'd go into the window and see...
1
by: Kosmos | last post by:
Hi, I really need some help here because I am not a programmer but I took on the task because I'm an intern and wanted to rise up to the challenge...and all that crap...anyways over the past few days...
0
by: peridian | last post by:
Hi, I wanted a web page where I could post code to, and have it appear in coloured formatting based on the context of the code. Most of the techniques I have seen for this involve complex use...
2
by: Cindy | last post by:
At the risk of asking a stupid question - Is anyone familiar with either a query against the systables or maybe an outside tool that will provide a list of the most common joins that have been...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.