Repost: Locking File

[Chris]

Sorry about the repost but got no replys. Here it goes again. Thanks in
advance.

The Background:
12 computers on our network using access 97.
Have a UNIX based server (NAS) that holds our Access files.
Have time-clock system that spits out .BTR files
We import BTR's into Access 2.0
We view reports in Access 97 that reference the Access 2.0 data (originally
from the time-clock generated .BTR files)

The Problem:
Once in a while after working in the database (in Access 97) the Access 2.0
record locking file gets stuck and no one can get into that table
(obviously). It's always the Access 2.0 locking file. Sometimes I'm able to
delete the file and everything is fine, sometimes I need to reset the
server. This is happening about every other day. I took a look at the
locking file and it's different computers (at different times) locking and
not letting go after they are through with Access. What could possibly be
the problem. It always worked fine for years. Is it the server, Access, or
our workstations? Please help if you can.

The Alibi:
I am just a graphic designer. I did not design our company's database system
(the boss did). But because I know how to turn on a printer I guess I have
just become our Network Administrator (LOL). Which would not be a bad job if
I knew what the hell I was doing. Translation - please try to go easy on me.

Thanks for all your help!!!
-Chris

 

[Arvin Meyer]

Unfortunately Row-level locking didn't appear in Access until version 2000.
That would probably solve your problem. You may also need to change the
maximum record locks on the server to a much higher number. (This used to be
a big problem on Novell servers).

By default, Access will lock a page of data, which is 2KB. This can
encompass more than one record (row) and on some tables with very small
records, it can be a lot of records. One trick done by programmers before
ther was any row-level locking was to pad out the records by adding enough
hidden text boxes (and corresponding field in the underlying tables) to
ensure that each record would go over 1KB, thus making it impossible for
more than one record to be on any given page.

You might try that. First make several backup copies of your data. Add
several text fields (memo fields will not work) to your tables that lock up.
Run an update query to fill them with 255 characters each. Add a default
value that fills 255 characters in each field for new records. You do not
need to display the fields in any forms or queries.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Guest]

Hi there, I'm trying to do some research and found your tip below interesting. But, my problem is that all of my tables/forms have at least one memo field. Your note as well as another one I found say that Memo fields should be avoided. Unfortunately, we can't avoid it. But, we will have multiple users updating different records in the same table, and would like to avoid record locking problems. Any tips for this situation?

Thanks,
Clarissa

----- Arvin Meyer wrote: -----

Unfortunately Row-level locking didn't appear in Access until version 2000.
That would probably solve your problem. You may also need to change the
maximum record locks on the server to a much higher number. (This used to be
a big problem on Novell servers).

By default, Access will lock a page of data, which is 2KB. This can
encompass more than one record (row) and on some tables with very small
records, it can be a lot of records. One trick done by programmers before
ther was any row-level locking was to pad out the records by adding enough
hidden text boxes (and corresponding field in the underlying tables) to
ensure that each record would go over 1KB, thus making it impossible for
more than one record to be on any given page.

You might try that. First make several backup copies of your data. Add
several text fields (memo fields will not work) to your tables that lock up.
Run an update query to fill them with 255 characters each. Add a default
value that fills 255 characters in each field for new records. You do not
need to display the fields in any forms or queries.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access