R
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!
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!