DAO Update Deadlocks SQL Server 6.5, but not 6.0 
Author Message
 DAO Update Deadlocks SQL Server 6.5, but not 6.0

[ Note restricted followups ]

We have a Visual Basic (VB) application which queries an SQL Server
database, using Data Access Objects (DAO) referencing an Access table
attached to a view on the server and places the resulting hits in a
data-bound grid control.  Users can select a line from the grid and
update it; the update is actually made on one of the underlying tables
(accessed through a view, because of index limits in Access)**.  This
works with SQL Server 6.0, but deadlocks if there are a large number of
returns with the Beta of 6.5, handed out at presentations by Microsoft
last month.

If a query yields more hits that the DAO Data Control can bring back in
the first block transferred (normally 51 records), any attempt to
update a record chosen from the in the grid fails.  Unless, that is,
the user scrolls to very last record of the recordset (there could be
thousands of them) before attempting the update.  The reason appears
symptomatic of the data control putting database locks on until it has
finished reading all the records.   The failure is particularly {*filter*}
as it involves giving the user an hourglass for 60 seconds whilst ODBC
times out. During this time Win95 (for example) grinds to a halt and
wont switch tasks.

Obviously this is not wholly a Data Control issue as the VB application
can operate perfectly well with the 6.0 databases under the same
conditions.  In my view it is either a outright 6.5 'feature' or bug;
or some unfortunate interaction between 6.5 and Data Control.

What are your views on what's going on?  How do you suggest we solve
it?  Using SQL 'TOP' to reduce the number of returns does not seem
appropriate as it can give the user false info about the number of

Doing a last and first move slows the display in the normal query only

** The application is read mainly and query performance is the priority.

Sun, 08 Nov 1998 03:00:00 GMT  
 [ 1 post ] 

 Relevant Pages 

1. Get Username or Computername after lock (or deadlock) errors from MS SQL server (7 or 6.5)

2. VB 6.0 & SQl server 6.5

3. VB4 RDO Performance: SQL Server 6.0 vs 6.5

4. SQL Server 6.0 vs 6.5

5. VB 6.0 & SQL Server 6.5

6. DAO, RAD Problems with the SQL 6.5 Server

7. VB5: Updating recordset on MS SQL-Server 6.5

8. Error doing update with VB 3.0 and SQL Server 6.5

9. Update problem with VB6 and SQL Server 6.5

10. Error 10095 with SQL Server 6.5 and updates with cursors

11. VB 5.0 Enterprise Edition does not contain MS SQL Server 6.5 Developer Edition

12. Using RDO to do SQL updates on SQL Server 6.0


Powered by phpBB® Forum Software