0

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.

WiredTheories
  • 231
  • 7
  • 18
  • I don't think any one can help you with your problem with the information you give. Wich are the query with the problem? also it doens't seem like a good idea to migrate an access table to SQL Server, and the still access that information throug MSAccess... Why not connect your app directly to SQL Server 2012? You would have a better performace. MSAccess is usefull for small databases. Also you are putting a lot of layers between your app and the actual data. A lot of things can fail in between. – ericpap Nov 28 '14 at 15:40
  • MS Access is used as the FE. Regarding the code they all work fine. I have tested them in SQL n also through pass through queries from Accesa – WiredTheories Nov 28 '14 at 15:46
  • What do you mean with "is used as the FE"? – ericpap Nov 28 '14 at 15:48
  • 1
    You have to take in account that when you run a query over a linked table on access, not only access and SQL need to "understand" the query, but also the driver in the meadle (ODBC in this case). One solution is to create a view on SQL server with the query you want to run preproceced and then link this view on MSAccess. Still think it not a good idea to use MSAccess to query SQL Server, but can work. Consider change you Front End to something better than MSAccess... – ericpap Nov 28 '14 at 15:52
  • 2
    @ericpap SQL Server linked tables and views work very well indeed with MS Access. I am getting speeds to rival local servers with Amazon RDS. – Fionnuala Nov 28 '14 at 16:30
  • @ericpap , Thanks for the suggestion. I could solve the issue with creating them as views and then I replaced the actual query to now load the SQL View into the local table in MS Access. – WiredTheories Dec 10 '14 at 09:23

0 Answers0