locked table records on sql 2005 using access97 query

[Roger]

on sql 2005, I've got a view with select permission granted, the view
just "select * from table"
using odbc in access97, I linked this view and I create a query to
retrieve certain fields
the query properties are 'no locks' and dynaset record type

but just executing the query as a datasheet, locks some records in the
original sql server table
is this how it should work ? seems to me that it should not lock
anything

what if I use a snapshot record type, would that not lock records ?

[Roger]

On May 9, 8:05*am, Roger <[e-mail]:

on sql 2005, I've got a view with select permission granted, the view
just "select * from table"
using odbc in access97, I linked this view and I create a query to
retrieve certain fields
the query properties are 'no locks' and dynaset record type

but just executing the query as a datasheet, locks some records in the
original sql server table
is this how it should work ? seems to me that it should not lock
anything

what if I use a snapshot record type, would that not lock records ?

I moved the query to sql server, granted select permission, and now
I'm using this view as a linked table, same problem

if I just open the table (view) in access, and use the sql activity
monitor, it shows many page locks, and I not sure why since I'm not
scrolling through the records

[Rich P]

Hi Roger,

The problem you are having is with the ODBC connection. ODBC
connections are continuous, thus, you will have record locking issues,
contention issues, and all sorts of other issues because of this
continuous connection. The fix is to not have a continous connection.
This is achieved by pulling the data you want to read into the local mdb
into a local table. You could use an append query to append (Insert
Into) records from the view to your local table, but a more reliable
method would be to use ADO.

ODBC is old technology and it was pretty state of the art when
everything was small. But, as data processing operations have evolved,
the demands are exceeding what ODBC (and Access altogehter) was
originally designed for. The current-permanent fix for all this (in the
Microsoft environment) is to migrate to disconnected data processing of
.Net (for data processing operations that involve servers - especially
the newer ones like sql server 2005 and higher).

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Rick Brandt]

Roger wrote:

on sql 2005, I've got a view with select permission granted, the view
just "select * from table"
using odbc in access97, I linked this view and I create a query to
retrieve certain fields
the query properties are 'no locks' and dynaset record type

but just executing the query as a datasheet, locks some records in the
original sql server table
is this how it should work ? seems to me that it should not lock
anything

what if I use a snapshot record type, would that not lock records ?

Just don't open the link as a datasheet. Most other uses (forms, reports,
etc.) will not have this problem.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[Roger]

On May 13, 5:46*pm, "Rick Brandt" <[e-mail]:

Roger wrote:

on sql 2005, I've got a view with select permission granted, the view
just "select * from table"
using odbc in access97, I linked this view and I create a query to
retrieve certain fields
the query properties are 'no locks' and dynaset record type

but just executing the query as a datasheet, locks some records in the
original sql server table
is this how it should work ? seems to me that it should not lock
anything

what if I use a snapshot record type, would that not lock records ?

Just don't open the link as a datasheet. *Most other uses (forms, reports,
etc.) will not have this problem.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt * at * Hunter * dot * com

turns out the form has the problem too
but I found the fix in creating the sql view
instead of the view being
select * from table

I'm using
select * from table (nolock)

seems to work so far using either datasheet or a form