Unanswered: Access 2002 Pass-Through Queries w/SQL Server 2000
I am working on an Access application that uses SQL Server 2000 as the database and Access 2002 as the front end. When this application was in Access 2000, there were no problems. However, now when I go to open any pass-through report (with a stored procedure as the data source) in design mode, it takes forever to open, if it opens. Sometimes it just hang there for 20+ minutes then dies.
I have read about putting in my WHERE clause in the close event of the report something like WHERE 2=1.
I have tried opening the reports in Access 2000 and was able to open any of them in design mode with no problems at all.
In the instance where Access 2000 is installed, it is installed on Windows 98. The occurance of Access 2002 is running on Windows XP.
I have a few questions.
1) Is the problem from the version change in Access, or in the operating system change?
2) Is there any other way to fix this other than adding code to the Close event of the report?
I compared the ODBC settings on the 2 machines. The Windows 98 machine (the one that was not having any problems) was using SQL Server authentication in its ODBC setting. The Windows XP machine was using Windows NT authentication. I changed the Windows XP machine to use SQL Server authentication and now I can open up the reports normally (except it now asks me for a login and password, which i have to enter 3-4 times then it opens the report). I can make my changes, then when I hit save, it asks me for the login and password 3-4 times again, then returns me to the report menu.
Once I go back into the report, the change has been made.
Does the user have to have their ODBC setting as SQL Server Authentication?? They currently are using Windows NT authentication and they can run the application just fine.