Another User Has Exclusive Access

[ARW17]

I can't get multiple users in a new database. When I've written previous databases, I split them and made multiple front ends and had no issues. I've been working for weeks on this and can't figure it out.

What I've Tried:
1. The database is split and the BE is in a separate folder from the front end.
2. I've made multiple .accdb and .accde front ends, but the error is in both.
3. We have 3 users. They all have Client Settings>Advanced>Default Open Mode>Shared AND Client Settings>Advanced>Default Record Locking>Edited Record.
4. We're all working in full versions of MS Access 2013.
5. We all have permissions to access the files where the front and back ends are stored.

We can't get our work done if only one person is in it at a time. I need serious help. Anyone have additional suggestions? The last db I worked on, most folks had the reader and they used .accde files. Does this have something to do with it? Also, should I be seeing a lock symbol on my .accde files when they're not open?

TYIA

 

[Micron]

It seems like there are 2 specific forms that won't work with 2 users at a time.

This is a different thing altogether, and is more or less what I was getting at in my first post. Something about the process (specific forms or interactions with the db or code behind a form) initiated by concurrent users could be the cause. Or your record locking settings are in play. Look at Options > Client Settings > Advanced > 'Default record locking' and ensure it's not All Records. Try No Locking as an experiment and dial back from there if you're not comfortable with that setting. You can research locking levels if you need more info.

Advanced is where you'll see the option for how to share the db, which should be set properly for the be if not already done. I don't see where you clarified the questions around whether or not everyone is sharing the same fe. Thus I don't know if a shared fe is part of the problem, or if record locking in that single fe could be in play. Please try to address all questions so that we have as much info as possible, and forgive me if I missed where you answered that.

 

[ARW17]

We've tried a shared FE and individual FEs with the same result.

We have Default Record locking set to No Locks on both the FE & BE.

It won't let 2 users select from the Name combo box. The code on that box is:

Private Sub cboName_AfterUpdate()


If IsNull(cboName) = True Or IsNull(cboWorkType) = True Then
WorkReviewSub.Visible = False
End If


If IsNull(cboName) = False And IsNull(cboWorkType) = False Then
WorkReviewSub.Visible = True
End If


Me.WorkReviewSub.Requery


End Sub

The On Load code is:

Private Sub Form_Load()


cboName = ""
cboWorkType = ""


Me.WorkReviewSub.Requery


WorkReviewSub.Visible = False


End Sub

 

[xenou]

Not sure. It's possible that the workreviewsub uses some kind of recordset that is locked (independently of other settings). You should look close at this form, especially if you are using a lot of code to source your form data.

Also btw your after update does not handle the case where (isnull(cboName) = false and isnull(cboWorkType) = true, in which case workreviewsub visibility remains whatever it happens to be)

 

[Micron]

I was going to provide a list of checks, but after a moment of thought, this provides a clue

It won't let 2 users select from the Name combo box

This combo should not be bound to anything. Is it? Typically, combos are used in a form/subform or split form setups to provide a list of values whose selection will control recordsets in forms. You don't bind them for 2 reasons that I can think of:
1) can cause record locks on the underlying rowsource especially if it's a query (perhaps a table too, but I'm not sure)
2) choosing a new value for the express purpose of controlling said recordset will change the value in the underlying field.

I'm not saying never bind a combo, just beware of it's limitations and behaviour. If that's not the cause, then I'll come back to the list of checks later.

 

[xenou]

There's nothing wrong with binding a combobox. You could easily have the list of items come a lookup table, but the actual item chosen bound to a new record in a destination table. However, not sure how this combo box is being used but just because two users can't select from it doesn't confirm that it is bound to anything.

 

[Micron]

There's nothing wrong with binding a combobox. You could easily have the list of items come a lookup table, but the actual item chosen bound to a new record in a destination table. However, not sure how this combo box is being used but just because two users can't select from it doesn't confirm that it is bound to anything.

did I say it was wrong to do so? I think I will back off rather than be challenged for merely suggesting possibilities; especially when an opinion that I know I'm not alone in is dismissed outright because you don't agree. I am unsubscribing so I'm afraid I won't see your reply.

 

[stumac]

Can you share a screenshot of the data tab of the property sheet for cboName?

I have never had a problem with a bound combo box - in-fact probably about 90% of mine are bound on shared databases with 15-20 users. It's probably the best way of controlling data entry in a properly normalised database.

 

[ARW17]

Ok, so I've drilled down a little further.

I have a query that is using the values of the Name and Work Type Combo boxes as criteria.

It won't let another user on another copy of the FE also use that query with different criteria at the same time. I don't mean "same exact time". I just mean that if I have options selected for Name and Work Type, no one else can run the query with different parameters. This is where the problem originates I think. Any suggestions?

Should it be set up as a master/child relationship instead?

 

[ARW17]

Can you share a screenshot of the data tab of the property sheet for cboName?

I'm sorry I don't know how to get the screenshot to post.

 

[ARW17]

Referring to my previous post, the query is dependent on a union query. Is that not allowed?

Even in the master/child set-up, it says that it can't run the union query when someone else is already using it.