473,320 Members | 2,146 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Access SQL query incompatability (nolock)

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?

Nov 12 '05 #1
4 18715
Robert wrote:
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?

Don't know. I'd increase the timeout value.

At the same time....30K records for a combo? The word "Ridiculous"
comes to mind.

Can you run the query from the database window? If so, I'd use a form
to search for the records. I created a generic form for my app where I
can search on any column on any table and filter based on the open
arguments the way I want.

I suppose you can use a 30K row combo (it will grow most likely), but if
I were a user I wouldn't want to.

My $.02

Nov 12 '05 #2

"Salad" <oi*@vinegar.com> wrote in message
news:F0******************@newsread1.news.pas.earth link.net...
Robert wrote:
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?

Don't know. I'd increase the timeout value.

At the same time....30K records for a combo? The word "Ridiculous"
comes to mind.

Can you run the query from the database window? If so, I'd use a form
to search for the records. I created a generic form for my app where I
can search on any column on any table and filter based on the open
arguments the way I want.

I suppose you can use a 30K row combo (it will grow most likely), but if
I were a user I wouldn't want to.

My $.02


We don't want to increase the time out. We want to reduce the result set
with filters as we are running into dead lock issues. However, until we can
rewrite the query associated with the combo box and test it we would like to
prevent locking while the pull down is bringing data over the wire. I think
we will have to use a pass through query to resolve the SQL compatability
problem as Access doesn't support 'with (nolock)'. Thanx for your
suggestions just the same.
Nov 12 '05 #3
You have a couple choices, but the quickest is to make sure that the query is
using the DISTINCT keyword in its SELECT statment:

SELECT DISTINCT blah...

Access insists on getting locks on tables that it requests w/o DISTINCT.

The other option is to bind the combo box to a pass-through query that calls a
stored procedure. This is the best-performing alternative.

Then again, it's a REALLY BAD programming practice to bind a 30,000 record set
to a combon box...you're much better off with some othere search mechansim
instead, using a 2-step process.
Nov 12 '05 #4

"Robert" <st*******@boeing.com> wrote in message
news:Hw*******@news.boeing.com...
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?


You could create a stored procedure on the server that uses the no lock
hint, (alternatively use SET TRANSACION ISOLATION LEVEL READ UNCOMMITTED.
Then call this procedure using a passthrough query, or else fill the combo
by hand with a callback function.

Tere's another method that is quite a bit of work, but may be worth it.
Create a temporary, local jet database, open a forward-only, read-only
recordset and dump the data into the local database. Then you can use this
for doing all your filtering, sorting etc. This works very well for
read-only data that you need to access frequently, such as are found in
combo and list boxes.
Nov 12 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

10
by: Thomas R. Hummel | last post by:
I have a stored procedure that suddenly started performing horribly. The query plan didn't look right to me, so I copy/pasted the code and ran it (it's a single SELECT statement). That ran pretty...
4
by: Robert | last post by:
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...
8
by: Roy Padgett | last post by:
I have a combo box where users select the customer name and can either go to the customer's info or open a list of the customer's orders. The RowSource for the combo box was a simple pass-through...
0
by: Jeff Martin | last post by:
Hello, I haven't posted here in a long time, but the last time I got excellent advice. Hope someone can help again because I'm really stumped... I have been having trouble with the following...
10
by: ysfinks | last post by:
I compared view query plan with query plan if I run the same statement from view definition and get different results. View plan is more expensive and runs longer. View contains 4 inner joins,...
9
by: Frawls | last post by:
Hi I Am am having problems with a stored Procedure that i wrote. Basically whats happening is that the Stored procedure Runs fine when i EXECUTE it in SQL Query analyzer. But when i debug...
0
by: djflow | last post by:
Hi! II was wondering if you can help me with SQL query.. Below 7 separated select query works fine(only when they are retrieved separately) But I want to combined them together and so that i...
8
by: mainframetech | last post by:
We are having a problem with Access 2002 as opposed to Access 2000. A multi-file query was set up under 2000 and tested good. Access is connected through a link to an Oracle 9.1 database. The...
1
by: mrobinsc | last post by:
** This SQL statement returns 4 rows SELECT COUNT(*) G.ACTIVITY_ID G.RESOURCE_TYPE G.RESOURCE_CATEGORY G.RESOURCE_SUB_CAT G.ANALYSIS_TYPE G.PROJECT_ID
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.