MS ACCESS 97/SQL server 6.5: deadlock problems when accessing server tables

MS ACCESS 97/SQL server 6.5: deadlock problems when accessing server tables

Post by Bernd Rosen » Fri, 04 Dec 1998 04:00:00



Hi,

we are using ACCESS 97 as front end and SQL Server 6.5 as back end.
When more than one workstation is running our programm these
workstations are more and more often in a "deadlock" state, it seems
they are locking each other out. When this occurs, we are not able to
access the locked server table directly by ISQL/W, too.

Mostly, we access the tables by opening Dynasets by OpenRecordset,
some recordsets are kept globaly open. Actually, the "deadlcok" occurs
when one stattion tries to update a modified recodset to the data
source. My ACCESS doc tells me that ACCESS is not locking linked
tables at all, so _who_ does all this locking ???

Is there any good documentation which tells me exactly what happens
inside ACCESS and SQL Server when ACCESS accesses linked tables ?

Many thanks in advance for your help,

Cheers, Bernd

 
 
 

MS ACCESS 97/SQL server 6.5: deadlock problems when accessing server tables

Post by Scott Lichtenber » Fri, 04 Dec 1998 04:00:00


Bernd,

Access is holding locks on your tables.  This is a result of an Jet
"feature" which allows Access to display records before it finishes loading
an entire recordset.  You can test this by linking to a large table (at
least several hundred records), and then opening it via the datasheet.
Access will display approximately 100 records, but it will not display a
record count, since it has not finished reading all the records.

With the datasheet open, take a look at the locks on your table.  You will
find page locks.  Go back to your Access datasheet and click the MoveLast
button.  Then refresh your activity monitor.  The locks should be cleared.

Unfortunately, there is no easy way that I know to prevent Access from
doing this.  As far as I know, you have to force Access to finish loading a
recordset every time you open one.  You can do this by issuing a MoveLast
command against the recordset.  This also applies to the recordsets opened
by listboxes and comboboxes.  

Hope this helps,
Scott



Quote:> Hi,

> we are using ACCESS 97 as front end and SQL Server 6.5 as back end.
> When more than one workstation is running our programm these
> workstations are more and more often in a "deadlock" state, it seems
> they are locking each other out. When this occurs, we are not able to
> access the locked server table directly by ISQL/W, too.

> Mostly, we access the tables by opening Dynasets by OpenRecordset,
> some recordsets are kept globaly open. Actually, the "deadlcok" occurs
> when one stattion tries to update a modified recodset to the data
> source. My ACCESS doc tells me that ACCESS is not locking linked
> tables at all, so _who_ does all this locking ???

> Is there any good documentation which tells me exactly what happens
> inside ACCESS and SQL Server when ACCESS accesses linked tables ?

> Many thanks in advance for your help,

> Cheers, Bernd


 
 
 

1. MS ACCESS 97/Sql server 6.5: deadlock problems when accesing server tables

Hi,

we are using ACCESS 97 as front end and SQL Server 6.5 as back end.
When more than one workstation is running our programm these
workstations are more and more often in a "deadlock" state, it seems
they are locking each other out. When this occurs, we are not able to
access the locked server table directly by ISQL/W, too.

Mostly, we access the tables by opening Dynasets by OpenRecordset,
some recordsets are kept globaly open. Actually, the "deadlcok" occurs
when one stattion tries to update a modified recodset to the data
source. My ACCESS doc tells me that ACCESS is not locking linked
tables at all, so _who_ does all this locking ???

Is there any good documentation which tells me exactly what happens
inside ACCESS and SQL Server when ACCESS accesses linked tables ?

Many thanks in advance for your help,

Cheers, Bernd

2. ADO - How can i delete a table

3. SQL Server 6.5/ACCESS 97: deadlock problems when accesing server tables

4. Problem Starting SQL Enterprise Manager

5. MS-Access 97 to SQL Server to MS-Access 97

6. Subtotals in portals

7. Display SQL errors in Java program

8. MS Access 97 connect to SQL Server 6.5 Problem

9. How to migrate from MS Access 97 to MS SQL server 6.5

10. Connecting to MS SQL Server 6.5 via MS Access 97 and ODBC

11. SQL Server 6.5 and MS WIN95 with ACCESS 97

12. Porblems Exporting From MS-Access 97 To SQL Server 6.5