Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
kevinc1081
Starting Member
1 Post |
 Posted - 2004-04-07 : 17:13:12
|
| Hello,I have an Access 2000 database that uses linked tables (SQL Server).Since making the switch from Access tables to linked SQL Server tables, the following problem is occurring:When on the search form, a user can do searches fine. However, after 4 or 5 searches, search results are no longer returned. If I immediately copy the sql statement into a Query, then the following error occurs:ODBC -- call failed[Microsoft][ODBC SQL Server Driver][SQL Server]ORDER BY items must appear in the select list if SELECT DISTINCT is specified.(#145)If the user exists out of Access and then goes back it, it works fine for a few more searches and then the problem occurs again. It is occurring on multiple machines.The query is as follows:SELECT DISTINCT Status,Students.LastName,Students.FirstName,Address,State,ZipCode,PPID,pkStudent AS Counter FROM Students LEFT JOIN (lnkAddresses LEFT JOIN Addresses ON lnkAddresses.lnkAddress=Addresses.pkAddress) ON lnkAddresses.lnkStudent=Students.pkStudent WHERE PrimaryAddress=TRUE AND PPID='test' ORDER BY Students.LastName, Students.FirstNameThanks for any insights.Kevin |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2004-04-20 : 12:24:37
|
| [code]SELECT DISTINCTStatus,Students.LastName,Students.FirstName,Address,State,ZipCode,PPID,pkStudent AS Counter FROM Students LEFT JOIN lnkAddresses ON lnkAddresses.lnkStudent=Students.pkStudentLEFT JOIN Addresses ON lnkAddresses.lnkAddress=Addresses.pkAddressWHERE PrimaryAddress=TRUE AND PPID='test' ORDER BY Students.LastName, Students.FirstName[/code]Try running the above sql instead... Access does this really annoying thing of putting () where they're not needed and also tries to nest it's joins... |
 |
|
|
|
|
|
|
|
Baffling Access "ODBC -- call failed" error msg