I need to delete relationships using VBA so that I can delete the linked tables, then link different tables and re-create the relationship.
This is the code I use to create the relationship -
Sub CreateRelation(RelName As String, TabPrime As String, TabForeign As String, FldPrime As String, FldForeign As String)
-
-
Dim MyDb As DAO.Database
-
Dim Rel As DAO.Relation
-
Dim Fld As DAO.Field
-
-
On Error GoTo CreateRelationDAO_Err
-
-
'Initialize
-
Set MyDb = CurrentDb()
-
-
' Check if relationship already there
-
For Each Rel In MyDb.Relations
-
If Rel.Name = RelName Then
-
Exit Sub
-
End If
-
Next Rel
-
-
'Create a new relation.
-
Set Rel = MyDb.CreateRelation(RelName)
-
-
'Define its properties.
-
With Rel
-
'Specify the primary table.
-
.Table = TabPrime
-
'Specify the related table.
-
.ForeignTable = TabForeign
-
'Specify attributes for cascading updates and deletes.
-
.Attributes = 3
-
-
'Add the fields to the relation.
-
'Field name in primary table.
-
Set Fld = .CreateField(FldPrime)
-
'Field name in related table.
-
Fld.ForeignName = FldForeign
-
'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.
-
MyDb.Relations.Append Rel
-
-
CreateRelationDAO_Exit:
-
'Clean up
-
Set Fld = Nothing
-
Set Rel = Nothing
-
Set MyDb = Nothing
-
'Debug.Print "Relation created."
-
-
Exit Sub
-
-
CreateRelationDAO_Err:
-
el = Erl
-
en = Err.Number
-
ed = Err.Description
-
ctrlfnctnm = "GetDescription"
-
Call Form_Err(en, ed, ctrlfnctnm, el, errMsgStr)
-
Resume CreateRelationDAO_Exit
-
-
End Sub
-
Here is the relationship window, and there are 2 copies of TblJoinComputerBranchTblComputers
It would appear that if the tables are local, I get line 4 as expected. using the code -
CreateRelation "TblJoinComputerBranchTblComputers", "TblJoinComputerBranch", _
-
"TblComputers", "ComputerID", "ComputerID"
-
Assuming my FE is on Computer 1, TblComputers on Computer2 and TblJoinComputerBranch on Computer3 ... Don't ask
it would appear Access creates it's own name for the relationship as on line 1 (Difficult to see) but it is
{C40F1BCB-C7D2-4ED0-8DFF-EADBF5B7E583}
This code works to delete the relationship created with my name -
Function DeleteRelationship(RelName As String)
-
' DeleteRelationship ("TblJoinComputerBranchTblComputers")
-
On Error GoTo DeleteRelationship_Err
-
-
Dim MyDb As DAO.Database
-
Set MyDb = CurrentDb
-
MyDb.Relations.Delete RelName
-
Set MyDb = Nothing
-
-
DeleteRelationship_Exit:
-
Exit Function
-
-
DeleteRelationship_Err:
-
If Err = 3265 Then ' Relationship doesn't exist
-
Resume DeleteRelationship_Exit
-
Else
-
el = Erl
-
en = Err.Number
-
ed = Err.Description
-
ctrlfnctnm = "RElationship"
-
Call Form_Err(en, ed, ctrlfnctnm, el, errMsgStr)
-
Resume DeleteRelationship_Exit
-
End If
-
-
End Function
-
I appreciate the picture of the table is not very clear but the only difference between line 1 and line 4 is in the last column where on line 1 it is: - {C40F1BCB-C7D2-4ED0-8DFF-EADBF5B7E583}
and on Line 4 it is: - TblJoinComputerBranchTblComputers
My problem is deleting the
{C40F1BCB-C7D2-4ED0-8DFF-EADBF5B7E583} relationship.
How do I find the name or do I have to trawl through the MSysRelationships table to match the names of the 4 fields that I do know and delete them.
Sorry this post is so long winded.
Phil
- Tom, while deal with confusing Relationship Names at all? Why not simply pass the 2 Table Names involved in a Relationship to a Public Function, in any order, that will do the dirty work for you? The Function will return a Boolean Value indicating Success or Failure. As an example, I created a Code Segment that will DELETE the Relationship between the Orders and Shippers Tables in the Northwind Sample DB without knowing the Name of the Relationship.
- Function Definition:
-
Public Function fDELETERelationship(strTable1 As String, strTable2 As String) As Boolean
-
On Error GoTo Err_fDELETERelationship
-
Dim rel As DAO.Relation
-
-
For Each rel In CurrentDb.Relations
-
If (rel.Table = strTable1 And rel.ForeignTable = strTable2) Or _
-
(rel.Table = strTable2 And rel.ForeignTable = strTable1) Then
-
CurrentDb.Relations.Delete rel.Name
-
End If
-
Next
-
-
fDELETERelationship = True
-
-
Exit_fDELETERelationship:
-
Exit Function
-
-
Err_fDELETERelationship:
-
fDELETERelationship = False
-
MsgBox Err.Description, vbExclamation, "Error in fDELETERelationship()"
-
Resume Exit_fDELETERelationship
-
End Function
-
- Sample Function Call:
- If fDELETERelationship("Orders", "Shippers") Then
-
MsgBox "Relationship has been DELETED!"
-
Else
-
MsgBox "Relationship has NOT been DELETED!"
-
End If
-
- OR
- If fDELETERelationship("Shippers", "Orders") Then
-
MsgBox "Relationship has been DELETED!"
-
Else
-
MsgBox "Relationship has NOT been DELETED!"
-
End If
-
6 6483 - Tom, while deal with confusing Relationship Names at all? Why not simply pass the 2 Table Names involved in a Relationship to a Public Function, in any order, that will do the dirty work for you? The Function will return a Boolean Value indicating Success or Failure. As an example, I created a Code Segment that will DELETE the Relationship between the Orders and Shippers Tables in the Northwind Sample DB without knowing the Name of the Relationship.
- Function Definition:
-
Public Function fDELETERelationship(strTable1 As String, strTable2 As String) As Boolean
-
On Error GoTo Err_fDELETERelationship
-
Dim rel As DAO.Relation
-
-
For Each rel In CurrentDb.Relations
-
If (rel.Table = strTable1 And rel.ForeignTable = strTable2) Or _
-
(rel.Table = strTable2 And rel.ForeignTable = strTable1) Then
-
CurrentDb.Relations.Delete rel.Name
-
End If
-
Next
-
-
fDELETERelationship = True
-
-
Exit_fDELETERelationship:
-
Exit Function
-
-
Err_fDELETERelationship:
-
fDELETERelationship = False
-
MsgBox Err.Description, vbExclamation, "Error in fDELETERelationship()"
-
Resume Exit_fDELETERelationship
-
End Function
-
- Sample Function Call:
- If fDELETERelationship("Orders", "Shippers") Then
-
MsgBox "Relationship has been DELETED!"
-
Else
-
MsgBox "Relationship has NOT been DELETED!"
-
End If
-
- OR
- If fDELETERelationship("Shippers", "Orders") Then
-
MsgBox "Relationship has been DELETED!"
-
Else
-
MsgBox "Relationship has NOT been DELETED!"
-
End If
-
zmbd 5,501
Expert Mod 4TB
ADezii, very nice... I have read thru most if not all of the tabledef and relationship objects for something better and of course the SQL ALTER TABLE doesn't work on linked tables - IMHO, at this point in time, you have the only tool in the shop for the task at hand!
Thanks for the compliment, zmbd - glad it worked out for you, Phil. P.S. - Not really that critical, but I would probably set the Return Value of the Function = True and exit the For...Next Loop after - CurrentDb.Relations.Delete rel.Name
NeoPa 32,556
Expert Mod 16PB
Nice one ADezii. Long in the tooth but you've still got it :-)
@NeoPa:
Was wondering how you have been, hopefully all is well. Always nice to hear from an old friend (the old not meant literally!).
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Erick Bodine |
last post by:
I am trying to get a list of Storage Groups on an Exchange Server
(E2K) with an eye towards deleting/creating more. I am going off of
the Exchange SDK docs in msdn...
|
by: banon |
last post by:
I am trying to write code to create and delete a virtual directory with ASP 3.
If it is not possible, then how could I do the same with a physical folder.
any help is appreciated.
Thanks in...
|
by: Dixie |
last post by:
I need to delete some relationships in code. How do I know what the names
of those relationships are?
|
by: rsheridan |
last post by:
OK - My issue is that I am trying to build an app that will add and
delete printers. I know this can be thru WMI on Windows 2003 and XP
Operating systems. But Unfortantely I need this to work on...
|
by: Shaul Feldman |
last post by:
Hello,
I'm trying to find a way to add data (not working with DB) from client-side
to server side.
Let's say I have a form (page) that a user may add rows (in table) with
data.
the data is NOT...
|
by: bluez |
last post by:
I want to design a webpage where user can search the data from the database and list out the related records. Each of the record got a delete button which allow user to delete the record.
...
|
by: Yas |
last post by:
Hi everyone
I am trying to create a DELETE Trigger. I have 2 tables. Table1 and
Table2. Table 2 has all the same fields and records as Table1 + 1
extra column "date_removed"
I would like that...
|
by: Claire |
last post by:
Ive written a small string resource building utility that I send out to our
translators. I have a setup project for each language we support, which
picks
out a group of 12 english resx files plus...
|
by: tetsuo2030 |
last post by:
Greetings all,
(Access 2003)
The bosses want me to take an old version of a database designed with a single-user-at-a-time mentality and update it to allow multiple, simultaneous users. The...
|
by: Ajay Bhalala |
last post by:
I have one application in which I used one richtextbox and some buttons.
I have 1 button named "Delete".
I want to create delete command using the code.
When the user clicked the "Delete"...
|
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: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
| |