attached tables get locked

[Guest]

version: access 97

this database that i am troubleshooting is new to me, so this issue may be
because of something that i have overlooked. any help would be fabulous

i have an sql server 6.5 database as backend. i have an access 97
application installed on each users station. the access app uses attached
(linked) tables that correlate with the SQL server

the users have a particular issue with the access app: when they attempt to
open a linked table that has more than 100 records, access only displays the
first 100 records. in addition, access locks the entire table, so if other
users attempt to access the table, they cannot -- this only happens with
tables 100+ records; anything less, there is no locking issue

my question is three-fold: 1) why is this happening? 2) is there a locking
setting in either access or SQL server that needs to be changed -- i've
already made sure that the default record locking in access was set to No
Locks 3) if this is an access 97 issue, we were considering converting to
2003; could this possibly resolve the issue?

 

[[MVP] S.Clark]

Upgrading would net you the ability to use an ADP instead of an mdb. There
are ads and disads to doing so, but the option is there.

Check the properties of the linked table, there may be something to help
with the locking issue.

Try opening the data with a query instead of the table view. It may behave
better. Restrict to the least number of records as possible.

 

[Guest]

Upgrading would net you the ability to use an ADP instead of an mdb. There

are ads and disads to doing so, but the option is there.

I agree. However, that is not the route that my employer wants to take at
this moment.

Check the properties of the linked table, there may be something to help
with the locking issue.

I did this and there appears to be no properties in either SQL server or
Access that would create/solve this issue

Try opening the data with a query instead of the table view. It may behave
better. Restrict to the least number of records as possible.

Again, great idea -- however, even if I run queries instead, some of them
may still return more than 100 rows, which still locks the referenced table
-- trying to tweak the query to return as few rows as possible is ideal, but
some of our queries that do return many rows, can not be condensed any further