Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004
    Posts
    4

    Unanswered: RefreshLink makes tables read only

    I use linked tables that are linked to a SQL server backend. Sometimes the server location changes, so I use the DBEngine.RegisterDatabase method to change the DSN information programmatically. I then use the CurrentDB.TableDefs().RefreshLink method to refresh the connection to the new server. After I do this however, the tables become read only. I can't update a recordset opened against the tables, nor change the data in the linked tables manually. Any ideas about what I am doing wrong? Here is the code:

    DBEngine.RegisterDatabase "Server Name", "SQL Server", True, "DSN Info"
    CurrentDB.TableDefs("Table Name").RefreshLink


    Thanks!

  2. #2
    Join Date
    Feb 2004
    Posts
    142
    The refresh link can drop the unique keys if they are not setup as an index on the server. To do updates, Access must have a unique key to determine the row to update. If you can, setup the proper indexes on the server so that Access can see them.

    To determine if this is the problem, delete the link and manually relink the table from scratch. If Access prompts you for a(n) unique key(s) then refresh link is going to be an issue. On some versions a compound primary key may also not be seen by Access as a unique key.

    The best solution, if you can, is to use the identity (SQL autonumber) for your primary key on server tables. It WILL save a lot of grief for many reasons in the long run.
    KC

  3. #3
    Join Date
    Mar 2004
    Posts
    4
    Originally posted by AZ KC
    The refresh link can drop the unique keys if they are not setup as an index on the server. To do updates, Access must have a unique key to determine the row to update. If you can, setup the proper indexes on the server so that Access can see them.

    To determine if this is the problem, delete the link and manually relink the table from scratch. If Access prompts you for a(n) unique key(s) then refresh link is going to be an issue. On some versions a compound primary key may also not be seen by Access as a unique key.

    The best solution, if you can, is to use the identity (SQL autonumber) for your primary key on server tables. It WILL save a lot of grief for many reasons in the long run.

    Thanks for taking the time to reply. Unfortunately, I don't control the database design and I have been unable to find a way programmatically to specify the fields that uniquely identify a record when using the CreateTableDef method (at least not in VB) and an ODBC source. If anyone knows a way I am open to suggestions!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •