petebob796
Registered User.
- Local time
- Today, 21:12
- Joined
- Jul 31, 2007
- Messages
- 27
I posted a while back about an extremely slow query when using access 2007 but the same query in access 2003 runs fine. I have since tracked the issue down to a select distinct statement. If I change it to just select the query runs in about 45 seconds which it does in access 2003.
I have tried moving the select distinct into a seperate query and grouping by every field as an alternative. Neither of which worked any quicker this is taking something like the order of 100 or 200 times longer than usual. Any help appreciated.
The data is coming from an sql server 2000 database someone suggested it might be the connection string which I currently have refreshed at startup using VBA code:
RegKey = "database=qlsdat" & vbCr _
& "Description=QLS ODBC" & vbCr _
& "Trusted_Connection=no" & vbCr _
& "Server=domino"
RegisterDatabase "QLS_Live_ODBC", "SQL Server", True, RegKey
Help appreciated I can't understand this problem surely from a coding point of view the query should perform a select and then remove duplicate records after so it shouldn't take much longer for a few thousand returned records.
I have tried moving the select distinct into a seperate query and grouping by every field as an alternative. Neither of which worked any quicker this is taking something like the order of 100 or 200 times longer than usual. Any help appreciated.
The data is coming from an sql server 2000 database someone suggested it might be the connection string which I currently have refreshed at startup using VBA code:
RegKey = "database=qlsdat" & vbCr _
& "Description=QLS ODBC" & vbCr _
& "Trusted_Connection=no" & vbCr _
& "Server=domino"
RegisterDatabase "QLS_Live_ODBC", "SQL Server", True, RegKey
Help appreciated I can't understand this problem surely from a coding point of view the query should perform a select and then remove duplicate records after so it shouldn't take much longer for a few thousand returned records.
