How small of a record is 'too small' for Access. 2

[SeeWard]

Hello,
I've been reading that Access has a very myopic view of records. It tends to view a many records in one sweep. (???) If I had a database that was set up so that multiple users could have read/write access, how large does the record need to be so that I don't encounter locked records/users?. How does one work around this issue. I have 5 or so users on one DB in which all have read/write access. The records are very small (10 fields would be pushing it). I keep running into errors that state the record cannot be updated. I checked to be sure that no has the DB open exclusively and that all have the db set to share...Do I need to recreate this in Excel or (???)
Thanks!
Sorry if this is posted in the wrong area

 

[BobRodes]

<It tends to view a many records in one sweep. (???) Access's page size is 4k. When you do record locking, it actually locks in 4k blocks. It can happen that one user is editing a record, and another user attempts to edit another record on the same page. In this case, you can run into this problem.

You might investigate optimistic locking. Also, I don't believe that Excel will solve this, although I can't say for sure. Not much expertise in Excel here.

HTH

Bob

 

[jsteph]

There was, in one of the Access Develooper's Handbooks, a very drawn-out scheme for doing what you want. It involved checking the raw-data length of every record before insert/update, and making sure that the length, including overhead, was exactly the page size.

In my opinion, the work involved in this and the potential problems brought on by the complexity of such a scheme is way past the feasibility point. You'd be money and time ahead to just buy a sql-server license and use that as a backend--if you really need to lock single records.

Most Access db's work fine with Optimistic locking, (as BobRodes had mentioned), so I'd look at that first.
--Jim

 

[SeeWard]

Thanks Bob! I am just curious as to why you recommend optimistic locking rather than pesimistic locking? The records in question are constantly accessed every few minutes...I didn't realize one could specify in Access the type of lock other than record level/table level. How would I go about this. Thanks so much. I really am grateful...

 

[BobRodes]

I'm much more a VB expert than an Access expert. As for optimistic locking, the only reason I suggested it is because it attempts to post a record when someone else is using it and allows you to trap the error that occurs. This gives you more flexibility.

To accomplish the locking, read up on ADO, assuming your version of Access is current enough to support it. If not, read up on DAO.

HTH

Bob

 

[SeeWard]

Thank you both Jim and Bob...this gives me much insight and direction! Many thanks!