Operation must use an updatable query Microsoft Access 2010 running on SQL Server 2012

234 views Asked by At

I have an Access 2010 FE , with linked tables on SQL Server 2012. I have several queries which are passed through used for the generation of reports.

After migration and recreation of the queries. When I run the reports it throws up the above error. I did check the permissions and also tried unchecking "Use simple file sharing (recommended). I have full access for the account which I'm using.

Here are some pass through queries example that I have in my DB,

 1.UPDATE TABLE SET TABLE.COLUM=TABLE.COLUMN WHERE CONDITION 

 2.INSERT INTO TABLE (COL1......) SELECT * FROM TABLE 

 3.DELETE FROM TABLE 
   INSERT INTO TABLE (COL1......) 

   SELECT * FROM TABLE LEFT JOIN ON CONDITION LEFT JOIN CONTIDION WHERE CONDITION

Could you please let me know what settings or changes I need make to correct the above issue.

Thanks in advance , I have moderate knowledge on SQL and MS Access any help is greatly appreciated.

1

There are 1 answers

0
WiredTheories On

@GordThompson I took your option 1 , started decoding all the queries that are being called in VBA code , I had all my tables linked from SQL Server with return records set to false. I was able to correct the error , it was a violation of data type where a column with int and float where updating a column with nvarchar , this happened due to not defining them explicitly in the SQL statement. Thanks for the tips.