Greetings
I am assisting a developer with an Access application performance problem and an ODBC timeout. In a nutshell they have a combo box with a drop down that queries a lookup table on a SQL Server database over ODBC and returns 30,000+ records each time it is accessed. By using a stored procedure to view the locks on the tables in the DB we suspect that a locking issue or a dead lock occurs which is causing the timeout. Once the user receives an ODBC time out they have to exit the app to free up the locks. We are trying to develop a method where a limited result set is returned there by reducing the amount locking that takes place. For the time being we are trying to modify the query to add in the 'with (nolock)':
SELECT SSN ,Name ,BEMS FROM tblClients with (nolock) ORDER BY Name
However, Access does not support 'with (nolock)' in the SQL statement. Is there a way to get around this?