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

[katmomo5]

Hi,
I'm having a problem where multiple users cannot open the data entry form at the same time. I created a database, split it with the backend on a shared drive. I, then created an executable off of the front end to open the data entry form and distributed the executable to user desktops. We don't want the end users to see anything except for the data entry form. So, is having the executable file on each desktop the problem? And is it that I have to distribute the front-end?
Thank you so much for your help. This has all been a bit overwhelming and embarrassing to say the least.

[pbaldy]

What exactly do you mean by "created an executable"? You can not convert an Access form into an executable file; it will always run under Access.

In any case, in a normal split application you do put a copy of the front end on each desktop.

[katmomo5]

Hi Paul!!
By executable, I mean, I created a user interface form with a button to open the data entry form. Then I went to File, Save & Publish, Make ACCDE. It's the ACCDE that has been distributed to each desktop. So, is that why multiple users cannot open the form at the same time?

[katmomo5]

Thanks for your help, btw.

So, I emailed the accdb of the front-end to a couple of people. Had them save the file to their desktops. They are still unable to open the form at the same time.

[June7]

Do they get any error message? Never encountered this with split distributed frontend.

[katmomo5]

Hi June!

Yes, the errors have not been the same each time. One error said: “the database engine could not lock table 'tablename' because it
is already in use by another person or process”. Other errors have indicated which user/computer name had it open.

[katmomo5]

Since originally creating the form, I did have to make some changes to it and redistribute it to the end user computers. Would that mean that I would also need to re-link the tables to the backend? I didn't think I would need to because I did not make any changes to the tables, just the form itself. Well, I take that back. I did have to make a couple fields required, and disallow future dates. But, then again, what does changing field properties have to do with multiple users being unable to open the form.

I'm just confused.

[June7]

If the table links use drive letter pathing like: E:\folderpath\backend.accdb, then each user must have a drive E mapped in Windows.

If the table links use UNC pathing like \\servername\folderpath\backend.accdb, then drive mapping is not relevant.

Link path is a concern but that error message is not what I would expect.

Did you actually distribute the database, not just a shortcut?

[katmomo5]

Well, I emailed the accde to the information resource mgmt guy, and he remotely loaded the file to the shared folder located on all desktops. So, you dont think the problem is with the use of the accde, do you?

Thanks for trying to help me out, June. Really appreciate it.

[June7]

What do you mean by 'shared folder located on all desktops'? The frontend should not be in a shared folder. The file should not be shared.

View an Access frontend like any other application (Word, Excel, Internet Explorer, Windows). These are not 'shared'. Each workstation has their own copy installed, usually on C drive in Program Files.

In our case, each user has their copy at the C: root. This is because I have code that automatically copies frontend file to local workstation when there is an update and network permissions don't allow automatic copy into folders.

[ItsMe]

I avoid emailing Access files. If email is the only way then make sure to zip it down first.

Beyond that, I don't think I understand the process you went through to create your FE file. So.... I think of forms as objects inside of an Access Database. Just like a table or query is an object, so is a form. Front end files should have objects like forms and queries inside. They can also have tables (as long as said tables do not need to be shared).

In order for a front end file to function it should, somehow, link to the back end file. The back end file should contain all of the tables that are shared.

[katmomo5]

That completely makes sense. It's not really a shared folder. Sorry. It's a folder with all of the company's apps and there is a folder located on each computer all with the same apps.

Anytime I need to make a change to the user interface, I have to send it to the irm guy. Red tape we have to go through.

[katmomo5]

Hi itsme,
Thank you. I'll be sure to zip the file next time.
As far as the front end, I thought by creating a separate backend saved to the share, that would leave me with the original db but now with arrows on the tables to indicate they're linked to the backend. The db file with the linked tables is my front end. In the front end, I built a form with button to open end user data entry form. Then, I set this form to be displayed whenever the file is opened and removed ribbon and navigation pane from view. Saved & published the file as an accde with a different file name.

BUT I see now, how confusing I sound.

I shouldn't call the accde the front end, but as the accde does it not point to the backend? I am able to add data using the accde and see the data in the backend. Multiuser is my issue.

I was thinking June's post about the accde being in a shared folder was the problem, but it's not shared. Those are local folders on each computer

[ItsMe]

...“the database engine could not lock table 'tablename' because it
is already in use by another person or process”. Other errors have indicated which user/computer name had it open...

Considering only this error I would ask how you are updating records and what code is running behind your form.

[katmomo5]

Code is over my head how do I find out the code running behind it? I'm not able to work on the db at home, but once I get in to work I can check.

Also, does it matter that when the accde opens, the "click me" form opens fine without error. Once the user clicks the button, that is when the issue arises if another user has the form open. I don't know if that matters.

Also, after we click okay on that error, another pop up opens to stop all macros.