Why the table is locked ?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Trung - 13 Aug 2005 09:06 GMT

Dear all,

I have Access Font End to SQL server Back end.

In an Access form, I use combobox, based on a stored procedure, which based
on 1 table (SELECT LocationID, LocationDescription FROM Locations).

When I open the form and check in the SQL server Enterprise Manager, go to
Current Activities/Lock/Object, I see that the table from what the combobox
record sourse come (Table Locations) have many locks. The lock type is TAB
(entire table) or PAG, status: GRANT, Owner: Xact., Lock Mode : S (Shared)

It is suprised me because:

- Data come from the stored procedure, meaning data is read only, so it much
be no lock.

- Combobox recordsource is read-only, meaning no lock.

Because those locks consume resource, I wondering it is normal or there is
some way to release the lock ?

Thanks for advice

Signature

Ngo Quang Trung

Reply to this Message

Sue Hoegemeier - 15 Aug 2005 01:43 GMT

A stored procedure doesn't mean you will never have any
locks. Having a data source using a stored procedure doesn't
mean you won't have any locks, much less shared locks, even
when it's read only.
The locking behavior will really depend on how you are
writing your code, designing your application and those are
the details we don't know anything about.
You'd probably want to post the code you are using for your
combo box in one of the Access newsgroups and ask over
there. Try posting in:
microsoft.public.access.modulesdaovba.ado

-Sue

>Dear all,
>
[quoted text clipped - 19 lines]
>
>Thanks for advice

Reply to this Message

Trung - 15 Aug 2005 02:02 GMT

Thanks Sue

I realized my problem. It has locks because the combobox I have has 25,000
rows.
The connection to SQL server, as my observation, stays open until the form
is closed. Access seems to me do not retrieve all the records, but only first
200, and keep the connection, wait until user scroll and retireve more
records as User needs. During this task, it lock the table with shared lock.

I tried with small table (100 rows) then there is no lock.

I now have to reduce the number of row in this combo box, or move the table
to local access table and retrieve from there.

Thanks for your comment

Signature

Ngo Quang Trung

> A stored procedure doesn't mean you will never have any
> locks. Having a data source using a stored procedure doesn't
[quoted text clipped - 33 lines]
> >
> >Thanks for advice

Reply to this Message