ODBC table locking

[Ron Hinds]

Upsizing an Access app to Access 97 FE - SQL 2000 BE. Using ODBC Linked
Tables. The Row Source of a Combo Box on a Form is set to a Query. When I
open the form, Access places a lock on the tables that are in the Query
which makes up the Row Source for the Combo Box (I execute the sp_lock
stored procedure in Query Analyzer to determine this). This is preventing me
from updating one of the tables elsewhere in the code of the Form. First I
tried setting some properties on the underlying Query - set Recordset Locks
to No Locks and Recordset Type to Snapshot. No joy. Next I tried setting
those same properties at the Form level - again no luck. On the Advanced tab
of the Options Dialog Box in Access, I set the Default Record Locking to No
Locks, even though the Help stated that this was always the case with ODBC
linked tables. But it worked! Or so I thought. The next day when I tried it
again - the locks were back! Then I tried setting the Combo Box's Locked
property to Yes. Which renders the Combo Box useless 'cause you can't even
select an item from it when it is locked. But again it worked - then again
mysteriously stopped working! WTF is going on here? Does anyone know how to
override this locking behavior in Access? I'm hoping this isn't the case,
but it seems my only alternative is to rewrite my app so that all Combo
Boxes get their Row Source from a local Temp Table that I populate from the
original query. Please help!

 

[Mario]

What I do is using a little Access function Or a SQLServer
function or a SQLServer procedure that that gets all my
values and separates them with commas. I then shove this
value list into the combo...Works great!

 

[Armen Stein]

Upsizing an Access app to Access 97 FE - SQL 2000 BE. Using ODBC Linked
Tables. The Row Source of a Combo Box on a Form is set to a Query. When I
open the form, Access places a lock on the tables that are in the Query
which makes up the Row Source for the Combo Box (I execute the sp_lock
stored procedure in Query Analyzer to determine this). This is preventing me
from updating one of the tables elsewhere in the code of the Form. First I
tried setting some properties on the underlying Query - set Recordset Locks
to No Locks and Recordset Type to Snapshot. No joy. Next I tried setting
those same properties at the Form level - again no luck. On the Advanced tab
of the Options Dialog Box in Access, I set the Default Record Locking to No
Locks, even though the Help stated that this was always the case with ODBC
linked tables. But it worked! Or so I thought. The next day when I tried it
again - the locks were back! Then I tried setting the Combo Box's Locked
property to Yes. Which renders the Combo Box useless 'cause you can't even
select an item from it when it is locked. But again it worked - then again
mysteriously stopped working! WTF is going on here? Does anyone know how to
override this locking behavior in Access? I'm hoping this isn't the case,
but it seems my only alternative is to rewrite my app so that all Combo
Boxes get their Row Source from a local Temp Table that I populate from the
original query. Please help!

Hi Ron,

Couple of things:

1 - Does this happen on all your forms with comboboxes, or only this
one? Reason I ask is that it is unusual to attempt to update a table
that is currently being used in a combobox rowsource on the same form.
Are you sure that you need that table in the recordset of the form
itself? Usually you need only the foreign key from the main table, not
the lookup table.

2 - When using comboboxes where the BE is on SQL Server, a good approach
is to use a passthrough query. They're much faster and easier to
control. An added benefit is that you can specify (NOLOCK), which will
force SQL not to place any locks on the tables.

Hope this helps,

 

[Ron Hinds]

Hi Ron,

Couple of things:

1 - Does this happen on all your forms with comboboxes, or only this
one? Reason I ask is that it is unusual to attempt to update a table
that is currently being used in a combobox rowsource on the same form.
Are you sure that you need that table in the recordset of the form
itself? Usually you need only the foreign key from the main table, not
the lookup table.

So far I haven't run into it on any other forms, but I have a large app and
have just started this process. The table in question isn't used anywhere
else on the form. The form is an inventory receiving form; the table in
question contains the original PO data. When the form is loaded, the combo
box (in the Form header) is populated with the currently open PO's. The
recordset used in the Detail section is bound to the ReceivedParts Table.
When the user is finished adding parts, they click a button, RecvPO, that
then attempts, among other things, to update the PO table.

2 - When using comboboxes where the BE is on SQL Server, a good approach
is to use a passthrough query. They're much faster and easier to
control. An added benefit is that you can specify (NOLOCK), which will
force SQL not to place any locks on the tables.

Yes, I tried that yesterday after I posted this - i.e., changing the Query
Type to PassThrough. It works, sort of. The table no longer gets locked. But
I am unable to select anything from the combo box! The combo's Locked
property is set to No. It seems as though Access is treating the combo as
"read-only" - but isn't that the only way a combo works? Are there
"editable" combos?

 

[Albert D. Kallal]

All of the table locking stuff is set on the server side.

That odbc table might be a phone link half way across the world to tiny
little Casio watch with a database on a tall tower in the middle of the Gobi
desert. It might be powered by a solar cell.

On the other hand, the table might be on a massively parallel server farm
running the latest Oracle database. In both cases, it is simply a odbc table
to ms-access. Any talk about record locking is the problem of the server.

Perhaps the sever does not even support record locking. ODBC has no clue
about this information.

You need to setup, and look at the settings you are using on the server
side, as on the ms-access side, you have no control over what the settings
are on the server side, let alone even if those settings are available. It
would be nice to make a ODBC link to Amazon.dot com, and lock their entire
site. ODBC simply does not work that way.

ODBC has none of this info or control over the server. Settings are done on
the server side.

To be fair, there is some agreed upon protocols for ODBC and record locking,
but it really is a hit and miss event, so really, the settings are to be
done server side.

 

[Rick Brandt]

Upsizing an Access app to Access 97 FE - SQL 2000 BE. Using ODBC Linked
Tables. The Row Source of a Combo Box on a Form is set to a Query. When I
open the form, Access places a lock on the tables that are in the Query
which makes up the Row Source for the Combo Box (I execute the sp_lock
stored procedure in Query Analyzer to determine this). This is preventing me
from updating one of the tables elsewhere in the code of the Form. First I
tried setting some properties on the underlying Query - set Recordset Locks
to No Locks and Recordset Type to Snapshot. No joy. Next I tried setting
those same properties at the Form level - again no luck. On the Advanced tab
of the Options Dialog Box in Access, I set the Default Record Locking to No
Locks, even though the Help stated that this was always the case with ODBC
linked tables. But it worked! Or so I thought. The next day when I tried it
again - the locks were back! Then I tried setting the Combo Box's Locked
property to Yes. Which renders the Combo Box useless 'cause you can't even
select an item from it when it is locked. But again it worked - then again
mysteriously stopped working! WTF is going on here? Does anyone know how to
override this locking behavior in Access? I'm hoping this isn't the case,
but it seems my only alternative is to rewrite my app so that all Combo
Boxes get their Row Source from a local Temp Table that I populate from the
original query. Please help!

This will happen with ComboBoxes and ListBoxes that have a large number of rows.
The table will have a lock imposed until you scroll to the bottom of the list
one time at which point the lock is released. The few times I have used a data
table as a RowSource (instead of a lookup table) I have done what the previous
poster indicated which is switch to a Pass-Through and use the NoLocks option.

I don't see why that would have affected the editability of the ComboBox. I
certainly never saw that problem.

Another option might be to use code to force the fetching of all rows when the
form is opened so the lock will be released.

 

[Ron Hinds]

This will happen with ComboBoxes and ListBoxes that have a large number of rows.
The table will have a lock imposed until you scroll to the bottom of the list
one time at which point the lock is released. The few times I have used a data
table as a RowSource (instead of a lookup table) I have done what the previous
poster indicated which is switch to a Pass-Through and use the NoLocks

option.

Thanks to all responders! Looks like that is going to work for me.

I don't see why that would have affected the editability of the ComboBox. I
certainly never saw that problem.

I think it was just some weirdness that I see in Access from time to time
with a very large app - i.e., after making lots of changes, which balloons
the size of the app, suddenly Access seems to "forget" settings on a form or
the name of a table, etc., etc. Anyway, I deleted the form then re-imported
it from a previous backup, changed the query to PassThrough and all is well!

 

[Terje Martinsen]

This more or less gives an answer to a similar problem I am working on.
The only problem is that I am not able to create a passthrough query.
Following the example given in MS helptext, the query for the combobox
reverts back to the original after save?
What could be wrong?

Regards
Terje