locked
Syntax Error in From Clause delating MSystem table records, relationship issue RRS feed

  • Question

  • User-359936451 posted

    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.

    http://stackoverflow.com/questions/1233451/delete-from-two-tables-in-one-query

     

    Wednesday, July 24, 2013 8:11 AM

Answers

  • User-359936451 posted

    As it turns out the syntax error on my alter statement needed these [  ]....

     

    ALTER TABLE [MyTable] DROP CONSTRAINT [{D86827C0-some-GUID-6A6449F1FB07}];

     

    Works now.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, July 24, 2013 12:57 PM

All replies

  • User-1199946673 posted

    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}';

    I Don't see why this doesn't work. Are you sure that you're using the correct name? Perhaps 'D86827C0-some-GUID-6A6449F1FB07' instead of '{D86827C0-some-GUID-6A6449F1FB07}'?

    Use

    OleDbConnection.GetOleDbSchemaTable(Data.OleDb.OleDbSchemaGuid.Foreign_Keys, New String() {Nothing})


    to retrieve the name (Column FK_Name)

     

    Wednesday, July 24, 2013 10:48 AM
  • User-359936451 posted

    As it turns out the syntax error on my alter statement needed these [  ]....

     

    ALTER TABLE [MyTable] DROP CONSTRAINT [{D86827C0-some-GUID-6A6449F1FB07}];

     

    Works now.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, July 24, 2013 12:57 PM