I have migrated an 2010 Access DB tables to SQL Server 2012 and linked them to the Access FE. I have pass through queries with return record set to no. I use the final result table which are linked in the FE of access for reporting purpose.
Each reports has no. of queries which are SQL pass through queries, which are run when the report selection Mass run is done.
I have tested each query individually and they work fine.
Scenario 1 : If I keep the access local tables and use the linked source tables for loading the final table , it works fine but they take an enormous amount of time. This was one reason for migration.
Scenario 2: When I use the approach I mentioned in the description , I get a error stating "ODBC_failed" SQL Native Client 11.0 Each Group by expression must contain at least one column that is not an outer reference. I have gone through each query for checking the group by error. I have even removed the query referencing the group by , but I still get the same error.
I have been researching from almost a week , I tried a lot of suggestions and now I'm at the dead end, The error is not helping in any way. I would really appreciate if someone could suggest me with some tips.