Slow query in Access 2007

[petebob796]

Any ideas why a particular query would run very slow in access 2007 when it runs fine on 2003. This is running on a terminal services environment and the database connects to a ms sql 2000 server. I will try it outside terminal services when I can but I can't understand the problem. Im not talking about slightly slower its something like a factor of 100 or 200 slower (I had to limit the dataset just to get a return within a day).

The database was an access 2000 mdb for compatibility across computers but I have tried converting it with out any luck.

 

[Moniker]

Taking a stab at this one. It's probably something to do with the connection between Access 2007 and SQL Server 2000. The driver is not mature, and chances are high that Microsoft concentrated on making it good with the 2005 version of SQL Server. You may want to see if there are alternate drivers as that may correct the performance issue. You also may want to check the driver's options between Access 2003 and Access 2007. Sometimes, the "enhancements" and default settings are not what you want.

 

[petebob796]

I have tracked the problem down to a select distinct in the final query I posted a new thread as I wanted a new title to show the more accurate problem.

I tried the select distinct as an outer query which was still just as slow and grouping on all fields which was also slow.

 

[rsmonkey]

tbh this problem can be a number of things... but if its only one particular query then its quite obvious that its something to do with the query rather than connection problem. Could I just ask why your using access 2007? i know its shiny and new but i find access 2003 so finely tuned now that i stick with the concept 'if it aint broke dont fixt it'.

However post your db and let ppl take a look...

 

[petebob796]

It's not my decision on the upgrade to access 2007 this is at a college on a terminal server environment which most staff are on, although I have a local machine as well rather than a thin client.

It's not a problem with the query as far as I can tell and i am reasonably experienced and have tracked it to the select distinct statement, as for posting the db can't do that its on ms sql server and massive and confidential this is a student management system we query. Just waiting for my shiny new computer so I can grab a copy of access 2007 and try it to confirm it's not a terminal server issue.

 

[brian]

Trust me. Access 2003 and Access 2007 do not coexist well on the same machine. Try to use a computer that has never had 2003 installed, alternatively format and reload.