Multi User - Splitting db or not

[teachme]

My FE path looks like this. I am very confused.

 

[Minty]

On your distributed FE make sure that in the options you don't have it set to open exclusively... And similarly on the BE Database.

 

[teachme]

On your distributed FE make sure that in the options you don't have it set to open exclusively... And similarly on the BE Database.

I checked both FE and BE and they are both set to open 'Shared'

I just sent a new FE file to another coworker, I didnt map it and the BE was located in the same folder as before. It worked fine - but that was 1 user though. I will try sending a brand new file to multiple users again - already mapped out and see what happens.

 

[The_Doc_Man]

Looking at the message, I think the right half of the message (i.e. to the right of the "OR") is significant. Access cannot tell the difference between these two cases:

1. User does not have WRITE access or MODIFY access to the folder and its contents, so cannot create/open/modify the x.LDB file (regardless of who is in it now). It is not enough to give a user the rights to the .ACCDB file. Has to be to the folder.

2. Another user opened the database exclusively, causing the x.LDB file to be inaccessible due to a conflicting file lock. Here, the problem is a locking conflict, not a permissions error.

Both of these have in common that the lock file cannot currently be manipulated by the user, and if you cannot diddle with the lock file, Access won't allow you anything other than Read-Only access to the DB.

Your scenario of "3 users can get in, 1 cannot" speaks volumes to me of a security setup issue. Here is the "right" way to do this in a domain-based environment (and doing it will require the cooperation of the domain and system admins).

1. Create a domain-level group identifier. Give it a name consistent with your site conventions on group identifier names. (A rose by any other name...)

2. Give the GROUP all needed permissions to the folders in question. Usually, MODIFY does the trick nicely. Less than that would be a problem. FULL CONTROL is not required as it would be overkill for Access needs and further would be a bit risky since FULL CONTROL includes a user taking file ownership at will.

3. Make all of your users in that domain become members of the domain-level group. NEVER give individuals special access through their personal logins. Always have them access a shared resource via some group and get their rights from the group.

 

[The_Doc_Man]

As to remapping, I always build a string like "\\server\share\folder\database.mdb" (or ".accdb") and do a cut/paste into the Linked Table manager. That way you never inadvertently do a drive-letter mapping function. Then, if you used the group-level permissions, you should be good to go.

 

[teachme]

I think I may have right clicked on the locked file and opened it as a text file to see who is logged in and 'i think' it was after I did that when users started to get this error message. I will try again with a fresh file.

The folder permission is set to 'Full Control' for all users and they are all part of a 'user group' so I think we are good there.

Will report back shortly.

....and thank you all

 

[teachme]

Update:

I sent a brand new FE file to 5 users and it worked fine for everyone but I wasnt able to re-send the file and test with the other 2 users who were having issues earlier - they had already left for the day.

I will try again with them but i think the problem was that I probably corrupted the lock file but I am not 100% sure.

Also, while there were 5 users using the database, all forms were opening fairly quickly except for one form where I had 1 tab control with 7 different tabs and 7 different subforms. That was the only form which took about 4-5 seconds. But once that form is open it was quick to switch between tabs, saving data etc.

I have a testing session with 12 people logging to the database on Monday and I will let everyone know how it went.

Thank you

 

[gemma-the-husky]

a form with so many subforms is likely to take a few moments to open.

one thing you can do with this is not load the subforms until a user clicks on the tab. If they don't use that tab, you save some time, although the coding is a bit more complex.

good luck with everything.

 

[jdraw]

I'm not sure if you have seen these articles from FMS. They are a good reference.

http://www.fmsinc.com/microsoftaccess/Performance/LinkedDatabase.html
http://www.fmsinc.com/microsoftaccess/DatabaseSplitter/index.html
http://www.fmsinc.com/microsoftaccess/Performance.html

 

[teachme]

a form with so many subforms is likely to take a few moments to open.

one thing you can do with this is not load the subforms until a user clicks on the tab. If they don't use that tab, you save some time, although the coding is a bit more complex.

good luck with everything.

Thank you but having multiple tabs vs several forms made sense when I was building the db. If i knew about this possible issue at the beginning i would have done it differently.

 

[teachme]

I'm not sure if you have seen these articles from FMS. They are a good reference.

http://www.fmsinc.com/microsoftaccess/Performance/LinkedDatabase.html
http://www.fmsinc.com/microsoftaccess/DatabaseSplitter/index.html
http://www.fmsinc.com/microsoftaccess/Performance.html

Thanks for the links. I will go through these especially the method to keep a continuous link to the backend db.

 

[gemma-the-husky]

Thank you but having multiple tabs vs several forms made sense when I was building the db. If i knew about this possible issue at the beginning i would have done it differently.

you can leave the subforms you don't need blank.
remove the subform record source.

then all you need is to set it , if a user clicks that page of the tab. there is a click event for the page.

something like

subdetails!sourceobject = "underlyingform"
subdetails.requery

 

[teachme]

you can leave the subforms you don't need blank.
remove the subform record source.

then all you need is to set it , if a user clicks that page of the tab. there is a click event for the page.

something like

subdetails!sourceobject = "underlyingform"
subdetails.requery

Oh man, if you (or anyone else) can help me with this, I would greatly greatly appreciate that.

Can you provide me with with the full code? I am assuming that the above code goes into the on click event of each page of the tab control?

Attached is all the tabs I got.

You also mentioned in one of your earlier posts that I can choose to not have the sunforms loaded until they click the tab. I looked it up and wanted to try this code (below)
But if there is also a way to do an on click event which is much simpler that I want to do that. last night I almost thought of re-doing the whole thing and create 7 separate forms vs the tabs but I dont want to go that route.


http://www.pcreview.co.uk/threads/i...bforms-on-tabs-only-when-tab-clicked.1149237/

"When I design a complex for this way, my onChange event also clears the
SourceObject of the subform that is being hidden. Here's some sample code -
note that on each of the cases instead of using absolute page numbers I get
the page number by getting the pageindex of a named page. I do this because
the page index of a page changes if you reorder the pages but I tend to
leave the names alone once I've set them. This just reduces maintenance and
prevents weird errors when you reorder the pages but forget to adjust the
code."

'Module level variable
Private mIntCurTabPage As Integer

Private Sub TabCtl8_Change()
'Clear the SourceObject of subform on tabpage we're leaving
Select Case mIntCurTabPage
Case Me.TabCtl8.Pages("pgFirstPageName").PageIndex
Me.sfrmFirst.SourceObject = vbNullString
Case Me.TabCtl8.Pages("pgSecondPageName").PageIndex
Me.sfrmSecond.SourceObject = vbNullString
Case Me.TabCtl8.Pages("pgThirdPageName").PageIndex
Me.sfrmThird.SourceObject = vbNullString
Case Me.TabCtl8.Pages("pgFourthPageName").PageIndex
Me.sfrmFourth.SourceObject = vbNullString
End Select

Select Case Me.TabCtl8
Case Me.TabCtl8.Pages("pgFirstPageName").PageIndex
Me.sfrmFirst.SourceObject = "sfrmOrders"
Case Me.TabCtl8.Pages("pgSecondPageName").PageIndex
Me.sfrmSecond.SourceObject = "sfrmOrders2"
Case Me.TabCtl8.Pages("pgThirdPageName").PageIndex
Me.sfrmThird.SourceObject = "sfrmOrders3"
Case Me.TabCtl8.Pages("pgFourthPageName").PageIndex
Me.sfrmFourth.SourceObject = "sfrmOrders4"
End Select
mIntCurTabPage = Me.TabCtl8
End Sub

 

[gemma-the-husky]

I think you already have the idea.

However, there is a click event for each page in a tab control

2 slightly different techniques worth trying. I haven't specifically tested these, so it's just a generic idea. I may have the syntax slightly wrong.

technique 1. load the subform at run time

what I would try is to design a standard blank form of the correct size and put this on each page. The control (ie the blank subform) will have a different control name on each tab page.

So lets say on tab1, the subform is called subform1

In the click event for that page you would just say

 if subform1.sourceobject = "dummyform" then
     subform1.sourceobject = "real form name"
     subform1.requery 'may not need this
 end if

technique 2. Alternatively, you might be able to put the real form on the tab control, but delete the recordsource of the form (the query) until you need it.

in that case you would have

 if subform1!recordsource="" then
     subform1!recordsource = "correctsource"
     subform1.requery 'again, maybe not needed
 end if

either way there will be a delay when the subform loads, but not when you open the form in the first place.

 

[teachme]

Ok here is what I have done.

I have taken all subforms out of the tab control and shrunk the tab control to as small as possible.

I put an unbound subform just under the already shrunk tab control.

On the on change property of the tab control I have put the following code.

P.S I did that to two tabs to make sure it works but i will do it for all 7 tabs

What this does it when you click on 1st tab, it only loads the 1st form and when you click on the 2nd one it 'kills' the first and loads the 2nd form. This way I am not loading all 7 forms at the same time. Too bad I cant really test it and vpn is very slow.

Private Sub TabCtl64_Change()
Select Case Me.TabCtl64.Value

Case 0
'Child291 is the unbound subform under the tab control
Me.Child291.SourceObject = "sfProjectDetails"

Case 1
Me.Child291.SourceObject = "sfSummaryOfStatus"

End Select

End Sub

Also for the main form i have put the following code at on load event

Me.TabCtl64.Value = 0
TabCtl64_Change

Attached is what it looks like. There is a gap between the tab control and the sub forms below but I did that on purpose to show you what it looks like.

It works perfectly fine here but the true test would be when I use it on the network.

I am confused as to just go with what I currently have or change it as per the code below.

What do you guys think? Theoretically it should work right? I mean I know it will work but what I am trying to ask is it should be faster than loading all 7 subforms at the same time correct?

Thank you all

EDIT: adding image of the design view as well