Record locking issues - Second attempt at help. 2

[ad2]

I have a Access 2000 db on a shared drive.

The record lock settings are not restricting a second user from going to and editing a record another user has open and is currently editing.

The settings under Tools>Options>Advanced are:
Default Open Mode - Set to shared
Default Record Locking - Set to Edited Record
Open databases using record-level locking is checked.

The data entry form's Record Locks property is set to No Locks. I changed this to Edited Record, but then only one user can open the database.

I want to have multiple users (usually never more than 2 at a time, all internal with access to the shared drive)to have access to the database. I just want to lock a record that is currently being edited.

Any suggestions?

Thanks in advance.

 

[willir]

ad2

Make sure you are not caching your writes. This includes cache settings on the server and the client.

Richard

 

[ad2]

How/where would I look for these settings?

 

[willir]

What type of server is the datbase on?

 

[ad2]

I'll have to find out.

 

[ad2]

Willir,

Novell Netware 6.5 is the server

 

[willir]

With Novell, you use the Novell client to connect from your Windows workstatation to the Novell server. Make sure in the setup of the client that writes are not cached.

As many will atest, record locking at the record level, table level works in Access. What I think is happending is that the database or the database writes are being cached - stored in memory. If this happens, the from the persepctive of the server, the record is not being edited and is therefore not locked. This creates problems, especially if more than one person has the same record open. I have seen this problem corrupt messaging databases.

Why cache changes? The system uses "packets" when transmitting data. It is more effecient for a packet to be full of information (the buffer), than to only send partially full buffers of information. This can dramatically improve performance on the network. But it plays havoic on shared database applicaitons.

It is probably a good idea to this with your system admin, or desktop support person. (System admin should be aware of the record locking problem)

Right click on your network neighborhood or current "Local Area Connection", and select "Properties". Find your Novell client for Windows (it should be at the top); select it and click on the properties button. Click on the "Advance Setting" tab.

Things that affect the caching of information...
- burst mode
- file commit
- file caching
- max write burst size

Note your settings, and try changing some of the parameters. The first one woulbe be to turn off file caching (file cahcing is where the file is copied to the local drive for faster performance - great for large spreadsheets, lousey for databases.) Then test the setting.

How to test...
After making the change to the Novell client, you will most likely have to reboot your computer. Since you are testing record locking, you will need to setup several computers the same way. Have one computer edit a record. Then have another computer try to access the record. Note that this is just a beta test type of thing -- you can not emmulate the environment.

...Before moving on, one more thing. Servers and workstations read in "blocks" of data. For example, they would read a "page" at a time. They are not really aware that you, the end user are reading a specific record in a database. The record may be only part of a block (page), or more than a black (several pages). The record lock should be invoked when one user accesses their block(s) of data. The second user should get a record locked error even if they access another record that is part of the existing block of data. (This used to be a real problem with older systems - less so now). Why test with more than two computers -- just play with the record locking. If I were testing, I would probably have four computers accessing the database.

...Moving on. If the file commit parameter does not work, play with file caching, then decrease the max write burst size, and then if you still have the problem, turn burst mode off.

Hey, you got a star -- I think somebody must of like your original post.

Richard

 

[ad2]

Richard,

No, YOU get a star! Thanks for the information. I will get on this tomorrow.

 

[ad2]

Richard,

Locking at the record level still isn't working. We made the suggested changes to the "Local Area Connection" suggested. On 2 users computers.

The database settings under Tools>Options>Advanced are:
Default Open Mode - Set to shared
Default Record Locking - Set to Edited Record
Open databases using record-level locking is checked.

Before these changes were made, when you looked on the shared drive and another person has the db open, there were two files listed database.ldb and database.mdb.

When second user opened the database.mdb and tried to edit any record, they got this message upon saving:

“Can’t save design changes … because another user has the file open. To save … you must have exclusive access to the file.”

Now, a second user can’t open the database at all: This message pops up: “The database has been placed in a state by user ‘Admin’ on machine ‘User 1” that prevents it from being opened or locked

Should the Default Open Mode be set to Exclusive instead of Shared? Has the network administrator inadvertently locked a second user out of the db. Do settings on the Server itself have to be changed? ?????

 

[Keetso]

Hi.

Not sure if you've looked at this or if this is your exact problem but it might be worth taking a look.

http://support.microsoft.com/default.aspx?scid=kb;EN-US;248967

K

 

[ad2]

K,
I'll check with Network Admin.

Ad2

 

[willir]

When working on design, you should open the database in exclusive mode.

http://www.tek-tips.com/viewthread.cfm?SQID=816512&SPID=181&newpid=181&page=1

A tip....
Split your database into a front end and back end. The front end contains the forms, reports, and perhaps static tables. It is typical to place the front end on the desktop for improved performance.

The backend is located on the server and contains just the data.

The front end "application" accesses the back end by linking to the tables.

Chances are that you will make changes to the front end - forms, etc. Since you are working on a local copy, you don't have to duke it out with others. When changes are complete, you deploy the new front end to the end users. Changes made to the back end, to the schema, are much less likely to happen, but when they do, you should specifically open the database with exclusive access.

The database.ldb file is Access's way of tracking locks. This is probably is what is locking the file.

As I understand it, the first connected user sets the record locking state, etc.

There are some previous threads on the Admin problem

http://www.tek-tips.com/viewthread.cfm?SQID=288261&SPID=181&newpid=181&page=1

http://www.tek-tips.com/viewthread.cfm?SQID=620936&SPID=181&newpid=181&page=1

http://www.tek-tips.com/viewthread.cfm?SQID=632392&SPID=181&newpid=181&page=1

http://www.tek-tips.com/viewthread.cfm?SQID=634190&SPID=181&newpid=181&page=1

http://www.tek-tips.com/viewthread.cfm?SQID=410346&SPID=181&newpid=181&page=1

http://www.tek-tips.com/viewthread.cfm?SQID=73247&SPID=181&newpid=181&page=1

http://www.tek-tips.com/viewthread.cfm?spid=181&sqid=65145&newpid=181

A bit of reading, but it boils down to...
- there are known problems with Access 2000
- Front End / Back End is the way to go
- The LDB file should go away when all users are disconnected. A rare event in Novell, I have seen it in an NT environment more, but sometimes after a hard crash, the file is left in an "open" state even though no users are currently connected to the file. This prevents you from deleting the file. The only was is to reboot the device the file is on. Part of the boot up process to ensure files are "closed".

Talk to you later..

 

[Hap007]

Dear ad2,

Perhaps you are looking at the wrong item.

Are your users doing the updates using forms? I guess that they are.

First, I would just test to see if record locking is working correctly and the 'Cache' is not your problem.

Try this:
1)Have 2 users open the shared backend and have each user open the same table (just open the table)
2) Have one user start to edit a field on a row in the table.
3) Now, see if the second user can edit and change that same record.

This will show you if/how the record locking, in access, is working and will elminate any program/code/form settings.

If the record, for the second user is not locked, I would bet the house that you are not opening the same database!


Now, let's assume that the record locking works under this test.

Here is a second area to examine.
Some programmers will write code to read in a shared table's record and save it to a local table.
Then, when the changes are complete, the programmer has a 'Save' button that writes the data back to the original shared table.
If your program has been written in this way(note, this is a good programming method by the way), you will not see any record lock errors, unless both users press the save button at the exact same second. If this is the method used, you will need some additional code to handle locking.

Hope this Helps,
Hap...

By the way, here is your star Richard


Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Add-on Solutions
Access Consultants forum

http://www.tek-tips.com/groups/accessDevelopers