record lock question

[smk23]

I have a form with a listbox populated by a pass-through query. The
pass-through pulls perhaps a 1000 records.
The pass-through queries a single table and that entire table becomes locked
to edits when I open the form containing this listbox (not just the queried
records).

I can delete the listbox off the form and the table becomes editable again.

This is bizarre to me because a pass-through should pull a snapshot of the
records and disconnect from the table. Any clues as to what could be causing
this?

Sam

 

[Sylvain Lafontaine]

This is because the listbox doesn't retrieve all the records at once. If
you go to the end of the listbox, you will see the lock disappearing. In
your case, use A2007 or add the NOLOCK statement to your sql pass-through
query.

 

[smk23]

Thanks Sylvain!!
I am in A2003 so how do I add the NO Lock to a statement such as

SELECT a.* FROM Appointment a
WHERE a.DateAppointment > GetDate()

 

[Sylvain Lafontaine]

Any search with Google would have give you the answer:

SELECT a.* FROM Appointment a with (NOLOCK)
WHERE a.DateAppointment > GetDate()

or:

SELECT a.* FROM Appointment a (NOLOCK)
WHERE a.DateAppointment > GetDate()

The NOLOCK directive only to the table for which it's specified; so if you
have multiple tables, you must put multiple NOLOCK hint.

 

[smk23]

Thanks so much! My issue is finally resolved after months of trying to
diagnose, do work-arounds, etc. to resolve this. All I had at first was an
observation that one of my tables locked when I opened a particular form with
no clue why.

Grateful,
Sam