How to Delete Specific Relationship

[JohnBates]

Hi,

I posted a similar question in Access Other Topics before I found this forum that is specifically related to tables & relationships...

In the Access Other Topics forum, someone has posted an FAQ on 'How to Delete All Relationships'.

I have a db that has several relationships, including 1 that apparently did not get deleted correctly/completely when I deleted the join lines.....

Does anyone have some code that will delete only certain relationships, but not the others?

Thanks for any ideas. John

 

[DonQuichote]

IF it shows up in the relations window, just right-click it and select remove...

 

[JohnBates]

As I said.. "I have a db that has several relationships, including 1 that apparently did not get deleted correctly/completely when I deleted the join lines....."

I did right-click ---> Delete on the join line

but apparently (though not visible now) the join still exists because a certain process is giving error "Cant delete table; relationship exists"

John

 

[UKRacer]

You may have tried this -

To make sure there are REALLY no relationships, click on "relationships" and "show all" from the menu bar in the relationships window. It is possible that you deleted a table from the relationships view without actually deleting the relationship first.

Just a thought...

Pete

 

[JohnBates]

Thanks UKracer,

Yes I have viewed the Show All window.

The 2 tables that I originally defined a relationship for are still there; but each table has no join lines going to/from these tables.

You made an interesting comment "It is possible that you deleted a table from the relationships view without actually deleting the relationship first."

Can you tell me how I could delete the table from the relationships window ?

I tried right-clicking on the table (in the Show All window), but there is no Delete option.

Thanks for taking the time to reply. I'm not too experienced with relationships.

John

 

[DonQuichote]

Do you use linked tables? In that case, the relations exist in the linked database as well as in the current one.

 

[JohnBates]

Thanks DonQuichote,

Yes 1 of the table is a Link table ... to a file on our AS400. But the table mentioned in the error message is the Access table that was (or is) linked to the AS400 file.

I'm thinking of doing a Repair on this database.

John

 

[UKRacer]

Good point Don!

Anyway - I would certainly try a compact/repair of the database (after taking the mandatory backup, or course!!)

If this doesn't work - try this:
Make a new database file, and import all your objects into it (queries, forms, etc etc).

Then import the tables, but before you click OK, click the Options button on the import dialog box. This will allow you to deselect the "import relationships" checkbox.

You should then have a duplicate of your database, but without the relationships. Obviously you'll have to put back in the relationships you want - but hopefully the one you don't want now won't exist!

And to answer your other question, to delete a table from the relationships view, simply select it and press the delete key. You won't delete any relationship set to it, and you won't delete the actual table - you'll just remove it from the rel. view window.
This is only really useful if you have dozens of tables, and the window gets a bit cramped - you can choose to view just the tables you are working on at the time - any other rels you have created will still exist - but won't be cluttering up the window.

Anyway - good luck (and don't forget those backups!!!)

Pete

 

[DonQuichote]

TIP: As a good clean up, you may try a decompile as well (see

http://www.mvps.org/access/bugs/bugs0008.htm

) if there is VBA code in your database. Be careful about that one, however. Sometimes class modules are not recognized as class modules after a decompile. (You can just create a new class module and paste the old then, but I'd like to warn you beforehand...)