Deleting relationship of tables (1 Viewer)

Kundan

Registered User.
Local time
Today, 16:53
Joined
Mar 23, 2019
Messages
118
I wrote the following code for deleting tables:
Function rib()
On Error GoTo Err_Command4_Click

DoCmd.DeleteObject acTable, "REGISTER I M F"
DoCmd.DeleteObject acTable, "DEPARTMENT & ALLOCATION"
DoCmd.DeleteObject acTable, "CASH OFFICE"
Exit_Command4_Click:
Exit Function

Err_Command4_Click:
MsgBox Err.Description
Resume Exit_Command4_Click

End Function

But it failed because the tables are connected in relationship. How to delete reationship through code?
 

vba_php

Forum Troll
Local time
Today, 18:53
Joined
Oct 6, 2019
Messages
2,884
Kundan,

I'm not really sure. But the question is really, *why* are you deleting objects with code? That's actually not very normal. Why can't you just delete them by clicking on them in the database window and pressing *delete*?? If you want additional info on the command you are running in VBA, look here:

https://docs.microsoft.com/en-us/office/vba/api/access.docmd.deleteobject

<edit>
it seems as though Access *does* have a *relationships* collection associated with the database object in VBA. This forum post over on byte.com shows you how to delete a relationship with code:

https://bytes.com/topic/access/answers/967049-vba-create-delete-relationships
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 16:53
Joined
Oct 29, 2018
Messages
21,358
Hi Kundan. How were the relationships created in the first place? If you created them using code, then I guess it makes sense to delete them using code as well. But if they were created at design time using the UI, then I agree, why are you deleting these tables and their relationship using code.


To delete a relationship using code, you have two options: DDL or VBA. For example,
Code:
ALTER TABLE TableName DROP CONSTRAINT RelationshipName
or
Code:
CurrentDb.Relations.Delete "RelationshipName"
As you'll notice, you'll have to know the name of the relationshp you want to delete. If you created the relationship using code, you would have assigned that name, so you would know which one to delete by using code as well.
 

Kundan

Registered User.
Local time
Today, 16:53
Joined
Mar 23, 2019
Messages
118
Hi Kundan. How were the relationships created in the first place? If you created them using code, then I guess it makes sense to delete them using code as well. But if they were created at design time using the UI, then I agree, why are you deleting these tables and their relationship using code.


To delete a relationship using code, you have two options: DDL or VBA. For example,
Code:
ALTER TABLE TableName DROP CONSTRAINT RelationshipName
or
Code:
CurrentDb.Relations.Delete "RelationshipName"
As you'll notice, you'll have to know the name of the relationshp you want to delete. If you created the relationship using code, you would have assigned that name, so you would know which one to delete by using code as well.

Really I want to make a backup of a database which is on another PC on my N/w. Instead of copying all the objects of the database I want to import only the changed tables. But before importing I need to delete the old tables. I want to do all this through code.
 

vba_php

Forum Troll
Local time
Today, 18:53
Joined
Oct 6, 2019
Messages
2,884
Really I want to make a backup of a database which is on another PC on my N/w. Instead of copying all the objects of the database I want to import only the changed tables. But before importing I need to delete the old tables. I want to do all this through code.
I must admit Kundan, what you're doing seems a little bit strange. But, if you must proceed....how are you identifying which tables are *new* (which ones are the *changed* ones)?? dbGuy's resources will work just fine for what you need to do. A couple of things that come to my mind though, regarding what you want to do:

  1. I believe, when you delete a table object, regardless of the method used, some sort of memory of it stays inside Access just in case the person who deleted it wants to restore it. If that is true, and you have a lot of tables that you have to delete, doing what you want could possibly result in bloating the db file to a large size pretty quick. You should ask others here though as to whether this is accurate.
  2. I would tend to guess that, if you tried to delete a relationship between 2 tables that was on a *higher level*, with regard to the *parent-child* setup, *before* you delete the relationship that's on a *lower level* that's also related to that setup, you'd get an error during code execution. for instance, in this relational structure:
Code:
[U][B]table1 (top level)[/B][/U]

id (primary key) / field1 / field2
 
[U][B]table2 (mid level)[/B][/U]
id (primary key) / table1ID (foreign key from table1) / field1 / field2
 
[U][B]table3 (lowest level)[/B][/U]
id (primary key) / table2ID (foreign key from table2) / field1 / field2
.....you tried to delete the one-to-many relationship between table1 and table2 first. in doing so, you would technically be asking access to create what is called an *orphaned table*, which probably isn't an official term but I'm mentioning it because it's the same thing as the phenomenon as the orphan record concept. But again, you should clarify this with the experts around here.
 

Users who are viewing this thread

Top Bottom