I am looking to delete a relationship that was created with code in an Access 2007 DB, using vb.net. The DB may have been upgraded from an older level.
So here is what has been done, I open the DB with ADO connection string, the db is encrypted and password protect, I also open in Mode =12, to get exclusive rights. This all works. I grant SELECT, DELETE, and ALTER permissions on MSysObjects, and MSysRelationships to admin user.
The issue is the relationship used is a GUID, it is stored in these System tables as only text, but it can not be deleted from the tables for which the relationship was made, ie.
ALTER TABLE [MyTable] DROP CONSTRAINT '{D86827C0-some-GUID-6A6449F1FB07}';
or
ALTER TABLE [MyTable2] DROP CONSTRAINT '{D86827C0-some-GUID-6A6449F1FB07}';
This fails becuase the GUID can not be found on the table, I have tried the tables on both sides of the relationship. I am able to get the szRelationship value, which is how I have it to apply to the above ALTER query, yet as stated, it is not found as a relationship name on either table. This led me to believe that the DB may have been migrated from older Access. The System Object tables show this relationship, so I know it exists and it appears in the Relationships diagram. It can also be deleted there manually. I have hundreds of DBs that needs this edit though.
So my alternate approach was to delete directly from the two system tables.
DELETE [MSysObjects], [MSysRelationships] FROM [MSysObjects]
INNER JOIN [MSysRelationships]
WHERE [MSysObjects].[Name] = [MSysRelationships].[szRelationship]
AND [MSysObjects].[Name] = '" & relationName & "';
But this returns a Syntax error on the From Clause. I have searched high and low for alternative statements to try and to debug this one, with not luck...This link provided some samples and a starting point for syntax.
