0

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.

WiredTheories
  • 231
  • 7
  • 18
  • I think it's either an issue with permissions (you seem to know that already) or an issue with you SQL (which you haven't posted) – Mark C. Nov 21 '14 at 16:19
  • Do your queries include the identity column(s) from the SQL Server table(s)? – Wayne G. Dunn Nov 21 '14 at 16:45
  • Do your SQL tables have Primary keys? I haven;t worked with Access in awhile, but it would not update SQl Server tables with no primary key in the past. And of course it is irresponsbile to create a table with no way to uniquely identify a record anyway. – HLGEM Nov 21 '14 at 20:40
  • I have checked the permission part and granted full access. – WiredTheories Nov 22 '14 at 13:22
  • I don't have any identity columns but there are ID columns generated as row num during SSIS when loading into SQL Server. – WiredTheories Nov 22 '14 at 13:27
  • @HLGEM @HLGEM Here are the SQL Statements I have several of them like the below , `UPDATE TABLE SET TABLE.COLUM=TABLE.COLUMN WHERE CONDITION **************************** INSERT INTO TABLE (COL1......) SELECT * FROM TABLE **************************** DELETE FROM TABLE INSERT INTO TABLE (COL1......) SELECT * FROM TABLE LEFT JOIN ON CONDITION LEFT JOIN CONTIDION WHERE CONDITION` – WiredTheories Nov 22 '14 at 13:28
  • 1
    You'll need to run the code and find the offending statement in the VBA debugger, then confirm what Table or Query it is trying to update. If it is trying to update a pass-through query then that's the cause of the error: Access pass-through queries that return records *always* produce a recordset that is not "updateable" (ref: [here](http://stackoverflow.com/q/18898032/2144390)). Instead, you will have to **(1)** create that same query as a View in SQL Server, then create an ODBC linked table in Access that points to the View, or **(2)** create a regular Access query using the linked tables. – Gord Thompson Nov 22 '14 at 14:59
  • @GordThompson option 2 cant be used. Since I migrated all tables to SQL Server. When I do that then Access will still load the tables and then execute the queries which was very very time consuming. I have kept the return record set to 0 and only linked the SQL table which will be used further. I also ran each query in sql and also tested via pass through from access. – WiredTheories Nov 22 '14 at 16:49
  • The fact remains that if you have converted a regular Access SELECT query into an ODBC pass-through SELECT query and your VBA code tries to update the recordset produced by that pass-through query it **will** fail. There is **no way** to make a pass-through SELECT query that returns an updateable recordset. You can use either of my options (option 2 *will* work, but yes, it might be slow), or you can change the VBA code to avoid trying to update the recordset from the pass-through query. – Gord Thompson Nov 22 '14 at 17:07

1 Answers1

0

@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.

WiredTheories
  • 231
  • 7
  • 18