Few Linked tables are read only after migrating MS Access to SQL Server

Sameer Khanna
Sameer Khanna used Ask the Experts™
on
Hello , I migrate MS Access to sql server and linked the tables But Few linked table are read only but i can insert the records directly on the SQL server but not from the MS Access , Any ideas what could be the cause ?

To Add to this i have few pass through queries and after Doing google i understood pass through queries make base tables Read Only and few forums suggested i use with NOLOCK in the queries
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
BEcause they don't contain a primary key,

When migrating Access data to SQL Server, I always make certain that the tables have a primary key.  Then, once the tables are in SQL Server, I always add a TimeStamp field.  This is not a Date/Time field, this is a SQL Server field type which is used to prevent row write conflicts, and are sometimes called "RowVersion"

You definitely need the PK, and I strongly recommend the TimeStamp fields.
Distinguished Expert 2017

Commented:
Ditto what Dale said.

I can tell based on the fact that your tables didn't have primary keys that you didn't bother with Referential Integrity.  That is also very important also.  If you are not comfortable working with SSMS, go back to your Access BE and fix up the tables, add RI, and add any necessary indexes.  Then upsize again.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial