Programatically delete relationships?

[greaseman]

I know you can manually delete table relationships in an .mdb, but I was wondering: Can you programatically delete table relationships? If it is possible, how would it be done?

Thanks in advance to all you nice folk who reply.

 

[Pat Hartman]

Here's an example from one of my databases of how to delete relationships and how to add them via VBA. This application needed an extensive conversion into a complicated database which obviously had to happen more than once in a testing environment so the whole thing became a nightmare until I decided to delete all the relationships so I didn't have to load the tables in the correct order. This database was from A97 so I added the DAO.'s when I pasted it here. Your indexes might have funny names if you let Access create them. If you're going to do this, you might want to start with creating the indexes so you can control the names.

Sub SubDelRelations()
Dim Myrel As DAO.Relation
Dim MyDB As DAO.Database

Set MyDB = DBEngine.Workspaces(0).Databases(0)

'Company -->> Company_Code
MyDB.Relations.Delete "RelComp_CompCode"

'Company -->> Company_Name
MyDB.Relations.Delete "RelComp_CompName"
….
End Sub

Sub SubMakeRelations()
Dim Myrel As DAO.Relation
Dim MyDB As DAO.Database
Dim MyFld As DAO.Field

Set MyDB = DBEngine.Workspaces(0).Databases(0)

'Company -->> Company_Code
Set Myrel = MyDB.CreateRelation("RelComp_CompCode")
Myrel.Table = "dbo_EC_Company"
Myrel.ForeignTable = "dbo_EC_Company_Code"
Myrel.Attributes = 0
Set MyFld = Myrel.CreateField("Company_ID")
MyFld.ForeignName = "Company_ID"
Myrel.Fields.Append MyFld
MyDB.Relations.Append Myrel

'Company -->> Company_Name
Set Myrel = MyDB.CreateRelation("RelComp_CompName")
Myrel.Table = "dbo_EC_Company"
Myrel.ForeignTable = "dbo_EC_Company_Name"
Myrel.Attributes = 0
Set MyFld = Myrel.CreateField("Company_ID")
MyFld.ForeignName = "Company_ID"
Myrel.Fields.Append MyFld
MyDB.Relations.Append Myrel
…..
End Sub

 

[greaseman]

Pat,

Thank you so very much!!! That was the code I was looking for!

Another question...... I have a project comprised of about 150 .mdb's. How would I, within a .mdb, cycle through those 150 .mdb's and in turn delete a certain relationship in each one? I only ask because I just don't want to have to place code into each one of the .mdb's to delete a relationship.

I think it would be more efficient if I was to be able to cycle through each database, deleting my desired relationship. Also, the relationships are no longer needed, so there is no prob there.... however I did set up a routine that goes through each .mdb, runninng queries, modules, etc... my only problem has been in using a Transfer command to put my modified tables back where they came. I get a message saying I can't do the transfer because the table in question is participating in a relationship.

Hope I explained myself satisfactorily and what I'm looking for.

Thank you again!!

 

[Pat Hartman]

I assume that changing the database that MyDB is set to should do what you want.

 

[greaseman]

Pat,

Thank you so very much. Sorry I didn't respond this last Friday. I appreciate your willingness to help others.

 

[greaseman]

Pat,

How do you determine or find the names of relationships between tables? I looked in the Relationships menu and could not find anything.

 

[Pat Hartman]

Since I had to both create and delete relationships, I deleted the relationships that I created with the relationships window and used my code to create them. That allowed me to name the relationships myself. If you only want to delete them, you can find their names in MSysRelationships.

 

[greaseman]

Thank you, Pat..... it's truly appreciated.

 

[greaseman]

Pat....one more time......

Can you do this type of thing to one .mdb from another .mdb?

I've got an .mdb with a module that cycles through the .mdb's in another folder, and everything is working with the exception of being able to delete my relationships.

What I want to do is to transfer one table from my big folder, run some queries against the table, then delete the relationships the table belongs to in my big folder, then transfer the updated table back to the big folder, and finally, recreate the relationships the table originally participated in.

Does that make sense, what I want to do?

Thanks for your input! With all the responses you've posted, how did you ever have toime to learn Access so well?

 

[Pat Hartman]

Did you try my suggestion from post #4?

What I want to do is to transfer one table from my big folder, run some queries against the table, then delete the relationships the table belongs to in my big folder, then transfer the updated table back to the big folder, and finally, recreate the relationships the table originally participated in.

Is there some reason that you don't want to update the table where it is? What happens if you get an RI error when you put the table back?

With all the responses you've posted, how did you ever have toime to learn Access so well?

In the beginning I learned a lot from the questions that others posted. If I didn't know the answer, I found it. When I first learned to program over 30 years, my instructor impressed on us the importance of being able to use reference materials to find answers. In those days they were always books. Now we also have help. Of course the last useful help system for Access was A97 but the MS team is working on it. I have become a master at finding the information I require.

 

[greaseman]

Hi, Pat....

I would love to run my "stuff" against the tables where they are, but am not sure how I would point my .mdb against the .mdb's in that other folder. I think that's where my headaches lie.

I was able to run my transfer database and then run my queries and modules against the table I pulled in, cycling through the table in each .mdb I wanted. However, due to the relatioships present in each .mdb in that other folder, was not able to do a transfer database command to put my modified table basck into its original .mdb - therein lies the problem I'm having.

If there's a way I can point my queries and modules to each of the .mdb's in that other folder, I'd be a happy programmer! Is there a way to do that?

And last but not least, so you've been programming for quite some time it seems. Sure is different nowadays than it was back then, I would guess.

Thanks for your replies and for your assistance!! Looking forward to it!

 

[Pat Hartman]

You can use the TransferDatabase to link to tables as well as importing and exporting them.

Sure is different nowadays than it was back then, I would guess.

You couldn't believe how different. When I was dating my first husband, he came to take me to lunch. On the way down to the cafeteria I needed to drop off the card deck that represented the program I was working on in the computer room so it could be compiled. On a good day, we might be able to turn around two compiles. Desk-checking was an important skill because you could waste half a day on a keypunch error. My husband-to-be, ever the gentleman, insisted on carrying the box and dropped the it on the way down the escalator. Punch cards covered two floors. Luckily I'm pretty neat about certain things and most of the cards had sequence numbers on them so I could run the deck through a sorter and manually place the odd punch cards. This marriage might not have happened if I had had to take an old listing and rekey the entire program.

As it happens, that might not have been a bad thing except that he is the father of my only child.

 

[greaseman]

Pat,

Thanks for your reply..... I too remember those days of way back when. I was a computer operator on those old big mainframes, with tape drives, octal sorting algorithms, and also the fun of punch card decks (shades of Florida's "hangiong chads" !)

I'll try linking to my desired tables and doing what I need to do..... thanks for the hints.

Take care, and have a great Monday.

 

[greaseman]

Hi again, Pat....

Guess what..... I did not explain myself as fully as I thought .... my mistake (apologies!!). The linking worked, however, I also need to add two fields to my talbe in question, and linking does not allow that. How might I go about doing the removal of relationships, run my queries, add a couple of fields to my desired table and thenre-establish my relationships??

I feel really like "Duhhh!" for not mentioning about my need to add fields to the table.

Thaks again for your input and assistance.... it's great learning as much as I have from this forum.

 

[greaseman]

Pat,

Please disregard my last posting.... I figured out what I had to do.

Thanks so much for your help on this issue. You saved me a lot of time and aspirin.