Question Locked subform/Record

[jmjbear1011]

Currently I have a database that is used for time tracking for activities of each employee. I have split the database, and the back-end is on a shared drive. If I have just a few users no issues -

But if there are more, it acts weird.. On the main form there are several subforms linking to the same table (not linked themselves). Sporadically it makes it where they can't edit the record on that one subform, but if they wait about 20 minutes it can be edited just fine (spoadic people as well). When that happens there are no issues with any of the other subforms. Appears to be at the record level. Almost as if the record is locking. Issue does not occur on initial entry, but just when they go back to edit that data.

What could be causing that?

 

[jdraw]

the back-end is on a shared drive

tell us about the front end.
What have you done so far to identify the "conditions" involved?
How are records selected for edit? Any user can edit any record? A user can only edit records he/she inserted? Any thing...

 

[jmjbear1011]

The front end: The employees log their time by hr. So there is an hour selection, hidden text box that detects the current date, their windows user id, and the activity that they are logging. All those are the criteria. So for that specific data there should be only one record for that hour, date, user and activity. There are other fields, but those are the criteria fields.

What is frustrating, is that it is never the same sub-form and it appears to work after a while..

So when they choose a different hour it re-queries the main & sub-forms accordingly. But the issue is primarily if they have stayed in that hour (a very few times for a previous hour) all of a sudden they can click in that text box, but they are unable to adjust it at all at that time, but if they wait a while it works fine again. But every other sub-form works fine. Each of the sub-forms create their own records on the same table.

To clarify the sub-forms are based on that criteria as a select query. Just that each sub-form has a different activity in the criteria that they are logging creating a different record per activity.

 

[jdraw]

Does each employee have their own copy of the front end on their PC?

 

[jmjbear1011]

Yes they do.

 

[Pat Hartman]

Check each user's permissions for the server directory where the back end is stored. It almost sounds like one user is causing the lock. If your forms/subforms show the record selector, is it showing a slash in a circle when the record is locked? That's the indicator that Access uses to give you a visual clue that someone else is editing the record.

 

[jmjbear1011]

The issue was that they are only able to see their owner records (as it auto detects the user id and uses that as criteria).. So they should be the only ones that have that one specific record locked. That is where I had the issue - Because it wasn't all the time, and was sporadic..

Do you think network related issues could be the cause? I think that I will just convert it to unbound to fix the issue.

 

[Pat Hartman]

Did you look at their network permissions as I suggested?
Do you have record selectors so you can see when another user has locked a record?

File systems use the concept of logical record and physical record (sometimes called a block). A logical record is a row in a table. A physical record is a group of adjacent records that are read and written as a block to make I/O more efficient. So, it is not logical records that are locked, it is physical records. If the record length is short, a block might contain dozens of records and so many records might actually be locked.

Going to an unbound form is unnecessary. You need to figure out what is causing the lock.

 

[jmjbear1011]

The only thing that I have been given is that they all have r/w access. Otherwise I haven't been given anything beyond that (as I don't manage the server that the network drive is on)..

Are there certain rights that would cause that? So I can have a more detailed conversation with them?

 

[jmjbear1011]

As far as the record selector, yes we do have them and it shows the typical locked symbol. If I look at the records that happen to be locked at the time, I get the same symbol that they get on the front end. - The reason that I asked about the network, to see if the bandwidth could be the issue... let me explain the overall structure.

There are 5 teams that make up our dept. There are seperate databases per team (FE and BE for each). There there is one databases that linked to them all for reporting purposes (no issues with that one and reports are ran sporadically - and it doesn't tie up those records, makes a temp tbl to report on).

So this issue happens on all of the teams databases, sporadically. As they are not all linked I was thinking it was the server. They all reside in the same directory. Should I try to move it to a different directory?

 

[Pat Hartman]

Access users also need permission to create and delete files for the directory that holds the BE. When the first user logs into the application, Access creates the .laccdb (or.ldb) file which it uses to manage concurrent users. If the first user does not have permission to create files, Access locks the BE. When the last user logs out of the database, Access deletes the lock, hence the need for delete permission.

 

[jmjbear1011]

aaww ok. Thanks for that. I got back to work today, and opened a ticket to check the rights for both.. they should be able to do anything including subfolders right?

 

[Pat Hartman]

No. They only need the CRUD permission for the specific folder that holds the BE. Your network admin should be able to set it up to allow them to do what Access requires without giving them free reign to create/delete files everywhere. If you explain accurately what Access does to control sharing, he should understand the problem.

 

[jmjbear1011]

I did. Thank you again.

 

[jmjbear1011]

Are there any other issues that it could be.. I gave a list of Users to the network admin, and he said that all those users have read/write/execute rights for that directory. There are a part of a permissions group that has that built in.

 

[Pat Hartman]

Read/Write/Execute doesn't give them Add/Delete permission.

 

[jmjbear1011]

I was told that they have "Modify" rights which they said was the same as read/write rights.

 

[jmjbear1011]

And add/delete is referring to the directory and not access it self?

 

[Pat Hartman]

They need to be able to ADD and DELETE FILES!!!!!!!!!!!!

Go back and reread post #11

 

[jmjbear1011]

I'm sorry to be such a pain. Since I am not getting clear answers from them should I just have each user test the add and delete ability?