Record Not Updateable: Linked Table to SQL Server 2k

[stephh]

Hi, all,

I'm read the thread on Lined MS Access table Not Updateable, which is really great!!!

I have an Access .mdb file with a linked table to SQL Server 2K. When trying to update/add record in Access, it shows: the recordset is not updateable.

I checked the Primary Key Constraint in both Access and SQL Server. There were No Primary Key. Therefore, I set the primary key in the table in SQL Server and also the corresponded table in Access.

However, The data are still not updateable through Access. Does anyone have any idea on how to make the data updatable through MS Access?

Thank you sooooo much~~~

Steph

 

[nealvenables]

Did you relink the table to Access? Access won't "see" schema changes like primary keys until the link is rebuilt.

 

[stephh]

Yes, I did relink the table sucessfully in Access using Linked Table Manager after setting Primary Key. However, the data is still not updateable.

One thing I'd like to mention is I take the backup of SQL Server database and restore it to a new Server. Then, relink the Access application to the new server. Data then is readable in Access from the new server, but not updateable....

I have total of 5 databases. 2 databases have Primary Key. These 2 are updateable through Access after moving to new server. 3 databases do Not have Primary Key. These 3 are Not updatable through Access after moving to new server.

I set the Primary Key in one of the 3 not updatable ones and refreshed the link, but it is still not updateable....

><

Steph

 

[cmmrfrds]

Does the SQL Server table have a unique index in addition to a primary key?

 

[nealvenables]

One last thing - when you refreshed your link to the server tables, did you DELETE the link in Access or did you use a utility like the Linked Table Manager to refresh it? Deleting then recreating a link is guaranteed to refresh Access' local schema.

Other than that, I'm out of ideas I'm afraid.

Good luck...

 

[stephh]

Thank you sooooo much!!!!!!

It works great!!!! It actually need Both PK and Unique Index to be in the database.

I was just kinda wondering how come SQL Server was able to recognize the records without PK and Unique Index when the origninal .mdb upsized to SQL Server DB.

Now, I'm trying to move the database over to a New machine using Backup/Restore. All of the suddent, SQL Server does not recognize those DBs without PK and Unique Index anymore.

But, Thank You for helping!!!!

Steph