A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Lock problem with linked views in access 97



 
 
Thread Tools Display Modes
  #1  
Old July 1st, 2009, 01:40 PM posted to microsoft.public.access.tablesdbdesign
TiagoM
external usenet poster
 
Posts: 1
Default Lock problem with linked views in access 97

Hi,

I have an access 97 database on wich many users update and insert
information. Recently I link some views of a SQL Server 2005 database and I
have triggers of update and insert in these triggers, so the user can modify
information through the views.

The problem is: each time a user insert/update one record in a view all the
tables used in that view stay locked...even if the update or insert ends. I'm
missing something?


Thanks.
  #2  
Old July 1st, 2009, 02:49 PM posted to microsoft.public.access.tablesdbdesign
Stefan Hoffmann
external usenet poster
 
Posts: 991
Default Lock problem with linked views in access 97

hi,

TiagoM wrote:
I have an access 97 database on wich many users update and insert
information. Recently I link some views of a SQL Server 2005 database and I
have triggers of update and insert in these triggers, so the user can modify
information through the views.

Huh, you don't need triggers on views to update or insert data if these
views are key preserving.
You only need to create an artifical primary key on the linked table in
Access to do so.

The problem is: each time a user insert/update one record in a view all the
tables used in that view stay locked...even if the update or insert ends. I'm
missing something?

Sounds like lock escalation. In these case you need to trouble shoot it
using SQL Profiler to nail down the triggering SQL and use sp_who,
sp_who2 and sp_lock to get.



mfG
-- stefan --

  #3  
Old July 1st, 2009, 02:51 PM posted to microsoft.public.access.tablesdbdesign
Tom van Stiphout[_2_]
external usenet poster
 
Posts: 1,653
Default Lock problem with linked views in access 97

On Wed, 1 Jul 2009 05:40:01 -0700, TiagoM
wrote:

You may want to run SQL Server Profiler to see what's going on in more
detail. "select * from sys.dm_tran_locks" may help as well.

-Tom.
Microsoft Access MVP



Hi,

I have an access 97 database on wich many users update and insert
information. Recently I link some views of a SQL Server 2005 database and I
have triggers of update and insert in these triggers, so the user can modify
information through the views.

The problem is: each time a user insert/update one record in a view all the
tables used in that view stay locked...even if the update or insert ends. I'm
missing something?


Thanks.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:01 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.