locked
Update MSysRelationships in Access - Error RRS feed

  • Question

  • Hi,

    I need to migrate my database from access ....

    the problem is that the migrating tool im using tries to import the foreign keys... but it cant because the name for the relationships generated by Access is too long...

    So im trying to rename the MSysRelationships .. where the relationships name are....

    here the code

    Dim connection As New Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DataSource & ";Jet OLEDB:Database Password=" & Password)
    
    Dim c As New OleDb.OleDbCommand("Select * From MSysRelationships", connection)
          connection.Open()
    Dim r = c.ExecuteReader
    
    Dim fks As New Dictionary(Of String, String)
    While r.Read
      fks.Add(r("szRelationship"), fks.Count & "_" & _
                Left(r("szReferencedObject"), 7) & _
                Left(r("szObject").ToString, 7) & _
                Left(r("szColumn").ToString, 7) & _
                Left(r("szReferencedColumn").ToString, 7))
    End While
    For Each k In fks
      Dim u As New OleDb.OleDbCommand("Update MSysRelationships SET szRelationship = '" & k.Value _
                            & "' Where szRelationship = '" & k.Key & "'", connection)
      u.ExecuteNonQuery()
    Next
    

    but it throws the following error

     

    "Operation must use an updateable query"

     

    I have already configured the permissions to everyone (read/write) to MSys Tabels...

     

    What can i do now? is there another way to rename the access generated relationships?

    Wednesday, May 11, 2011 5:16 AM

Answers

All replies

  • For this kind of actions it is mostly easier not create one time programs, That is to much time consuming, Take the management tools from the database which you use.

    Often that is first removing the old relation and than setting the new relation.

    As it is Access you can easily create backup from your database.

     


    Success
    Cor
    Wednesday, May 11, 2011 5:29 AM
  • OK, thank you for your reply...

     

    I thought i could use a simple method like above to do a list of update queries.

    the problem is that access dont let me edit the rows of the MSys table MSysRelationships...

    so i tried to do the queries above....

     

    My mdb file has more than 80 tables and 208 Foreign Keys..... and now im trying to move to mysql.

     

    all management tools and migrating tools i use throw the same error... MySQL doesnt support name too long for indexes

    i could try to remove the relationship and add another...but the problem is that access will generate the foreign key name using the pattern "Referenced Table Name" & "Referecing Table name" and some of my table name are too long..resulting in a long foreign key name

     

    I dont know what to do anymore

    Wednesday, May 11, 2011 2:41 PM
  • Access system tables should never be updated directly, and I believe more recent versions of Access have locked them down. Instead you should be using Jet SQL DDL. There should be some examples in the below article:

    http://msdn.microsoft.com/en-us/library/aa140015(v=office.10).aspx#acintsql_ddlconst


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Wednesday, May 11, 2011 3:30 PM