Programmaticly delete all relationships 1

[Jeroen1979]

Hello All,

Is there a way to programmaticly delete all relationships in an Access Database? I.E how can you do it with the northwind database using VBA??

Thanx for helping

 

[KenReay]

HI

Have not tried this, so do it on a COPY first

how about Docmd.RunSQL "DELETE * FROM MSysRelationships;"

remember if you have a FE/BE set up, the relationships are in the BE
Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Jeroen1979]

When I try it I get a Warning that I don't have the privileges to delete the data??? What can I do about that?

I want to make an application when you push on a button (or type in the right path to the database) it wil automaticly import tables from an other database. For so far I've done it. But when I push the button again It imports the tables again and gives them other names (e.g orders -> orders1) So I want to programmaticly delete the tables every time before I import the tables. But it says I can't do that because the tables are related to each other. So I want to delete all relationships first. Hope this will help you understand my problem.

Jeroen ten Dolle
(Dutch)

 

[KenReay]

Hi

OK, if it will not let you delete from the system table, try the relationships collection

(from help)

A Relations collection contains stored Relation objects of a Database object (Microsoft Jet databases only).

Remarks

You can use the Relation object to create new relationships and examine existing relationships in your database. To add a Relation object to the Relations collection, first create it with the CreateRelation method, and then append it to the Relations collection with the Append method. This will save the Relation object when you close the Database object. To remove a Relation object from the collection, use the Delete method.

To refer to a Relation object in a collection by its ordinal number or by its Name property setting, use any of the following syntax forms:

Relations(0)
Relations("name&quot
Relations![name]

To use this you will need to have DAO library referenced.

Then something like

Dim DB as DAO.Database
Dim i as Integer
Set db = CurrentDb()
Do until Db.Relations.Count = 0
For i = 0 to Db.RElations.Count - 1
db.Relations(i).Delete
NExt i
Loop
Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Jeroen1979]

Thanks Ken

I think I got it working,

If not I'll let you know

Cheers

Jeroen

 

[DonQuichote]

Jeroen,

You can give yourself rights to the system tables if you want to. Just try it.

Best regards