I need to delete some relationships in code. How do I know what the names
of those relationships are? 10 6955
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.
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?
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?
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?
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?
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?
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?
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?
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.
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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...
|
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...
|
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...
|
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
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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....
|
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
|
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...
| |