Split DB, unable to have multiple users access form at same time

[katmomo5]

Not really making sense to me. It must have been taken out of context because the link manger is irrelevant after the accde is published. In other words, set and forget...

The distinction is what June had already mentioned. Avoid using mapped drives if possible.

When you are linking a table in the development file or using the link manager, it is advisable to navigate to the back end file going the long way around the block. Go through the entire network by selecting the domain and then drilling down to the server and to the folder, etc.

This will create a path that is recognized as a the Universal Naming Convention (UNC) vs. using a mapped drive that will cerate a hurdle or two, at least. A linked table using UNC will work for any client that is logged into the same domain. (For the purists out there, I am trying to avoid getting too technical).



Bottom line, if you go to your original FE file of the accdb extension type and look at the link manager, you do not want to see drive letters in the path. D:\DataFolder\Datafile.accdb would not be desirable. but.... \\ServerName\FolderName\FileName.accdb is desirable or something like.. \\192.168.1.110\FolderName\FileName.accdb

Ok, sorry, the thread isn't updating right... Thank you - read my mind, and thank you for trying not to be too technical.

Yes, there are no drive letters, so we are using UNC.

[ItsMe]

The UNC pathing can be set with VBA code.

True, too much guesswork. OP mentioned they are unaware of any VBA existing within the DB. Seems to be exclusively macros and wizard. All the more curious as to why the lock error would show. I am unfamiliar with how a macro edits a recordset and unsure where to focus at this point.

Maybe more knowledge on the behavior. Having a few dozen users running a macro which, in turn, locks a record sounds like a design issue. This is what I believe is happening. Unless there is another behavior like.. Not a single user can get passed go.

[June7]

I don't think macros can edit a recordset. There is a SetValue method that can populate a textbox on form. So if that textbox is bound, the data will pass to table. Users (or code) trying to edit same record at same time should be rare.

I don't know anything about debugging macros, if it's even possible like in VBA.

[ItsMe]

Simply binding a form to an updatable recordset should not produce that error. I have seen macros not function because they think the form is in edit mode. However, the argument for edit mode is moot in the case of executable.

katmomo5, how are the tables getting updated/appended?

[katmomo5]

The tables are getting updated with data that is entered by the end user.

(One person at a time can open entry form and enter data.)

[ItsMe]

Can you provide an accdb version of the front end file? Something is not adding up. If there is nothing obvious in the FE file then the only thing left I can think of would be that the IT guy is uploading a single file to a shared folder and all of the users are creating concurrent connections to a single FE. After 20 or so concurrent users I would expect to see the error mentioned at the beginning of this thread.

[katmomo5]

UghDB.zip please be kind, there are some unused objects that i am scared to delete.

[ItsMe]

Your macros are trying to open objects that are not available in accde files. Let me see if I can come up with an easy solution for you.

[June7]

I have no problem opening the file. All the table links are to an accdb. The link looks odd. It has a forward slash, never seen that:

\\\/hachsfpc20\dept_share\ePIR\NR_admin\NursingReports _be.accdb

There is a little VBA concerning registry edits for trusted location.

There are 4 general macros and a bunch of embedded macros.

But without the backend, can't really do any testing. Debugging would be so easy with VBA, although apparently similar measures are possible with macros. Review http://support.microsoft.com/kb/89610

ItsMe - are you saying accde cannot run INSERT and DELETE queries? That's all I see happening in the general macros.

[katmomo5]

it's actually vhachsfpc20...

silly question, can i send a blank backend?

[June7]

Yes, but records would be helpful for testing. Just remove confidential info like Phone, Address, email, SSN

[ItsMe]

At first blush I thought it was the action queries being called to open causing the issues but, I may be wrong about that if there are full versions of Access at the workstations. Then I found a Save and Close control on frm24hrNsg.

So, erase the text "[Embedded Macro]" from the On Click event for the control named "Close".

Click the elipses(..) and select "Code Builder". Insert the following in your newly created event handler.

Docmd.Close

Save the design and test your file after publishing as accde. When I mentioned design changes are a moot point I did not consider calling a macro to ask for a design change. That is what the embedded macro is trying to do.

This would be a first step. You really should move all of your event handlers to VBA, including the action queries.

[June7]

Good catch, ItsMe. The Save and Close macro is set for the Prompt ("Do you want to save changes to form?"). This should be set to No - for all of the Save and Close buttons.

However, I have doubts this is cause of the original issue.

[ItsMe]

Yah but I did not see any other macros being called in that form and OP states this form is where the hiccup is. Still does not add up to the correct sum though....

[katmomo5]

At first blush I thought it was the action queries being called to open causing the issues but, I may be wrong about that if there are full versions of Access at the workstations. Then I found a Save and Close control on frm24hrNsg.

So, erase the text "[Embedded Macro]" from the On Click event for the control named "Close".

Click the elipses(..) and select "Code Builder". Insert the following in your newly created event handler.

Docmd.Close

Save the design and test your file after publishing as accde. When I mentioned design changes are a moot point I did not consider calling a macro to ask for a design change. That is what the embedded macro is trying to do.

This would be a first step. You really should move all of your event handlers to VBA, including the action queries.

K, where do I put that? This is what I see in the window...
Option Compare Database
________________________
Private Sub Command68_Click()
DoCmd.RunCommand acCmdUndo
DoCmd.Close
End Sub
_________________________
Private Sub Close_Click()
End Sub

When I click on the area below that second line, the drop down item does change from General to Close.