Errors with Simultaneous Multiple Users

[Badnapp]

Good morning,

I have a simple access database that 26 clerks in my office use to enter their weekly tasks. The database is split into a front end and back end, with the back end stored on a shared drive that we all have access to. We have been testing the database for 3 weeks with 5 clerks and no issues. This week is the first week that all 26 have used it, and immediately we encountered a problem.

I think the problem is happening because one of the tables in the database is linked to an excel spreadsheet. When multiple users try to use a dropbox that is linked to this table simultaneously they get an error message. I saved the excel file as a shared file, but this did not correct the problem.

I made a copy of the database before I split it. I know I can fix this if I import the excel spreadsheet into a copy of the database, link this table to the dropboxes, split it again, and redistribute it.

Ideally, I would like to leave the front ends linked to this spreadsheet, because we update it regularly. If not, we will have to have somebody update that particular table in the database intermittently. Are there any other solutions to this problem that I'm not considering?

Thankyou

 

[bob fitz]

Does each user have a copy of the front end on their machine.

 

[Badnapp]

Yes, each user has a copy of the front end stored on the C:\ drive of their machine.

 

[David R]

What error message are they getting?

I frequently hit an error message (in Excel) if I have the sheet open as a linked table in Access (or ArcGIS, or...). But sometimes if you've got it open in Excel, they won't be able to open it in Access as well. It's tricky.

Do they really need to update the Excel page during the day? How 'live' do you need the Spreadsheet data? You could have it attempt to update the imported copy every X hours, or everytime somebody boots up the database, or whatever...

 

[Badnapp]

I think I left out some information that might be important. All of the clerks are spread out across the province in different offices. We all have access to the same shared drive (labelled J:\). The back end of the database is stored in this J:\ drive. A copy of the front end has been distributed to all 26 clerks.

There is a form in the front end that the clerks use to enter tasks. One of the dropboxes on the form is for who the task is "Assigned by." This table is populated by a link to an excel spreadsheet, which is stored in the same folder as the back end on the J:\ drive. When multiple users try to open this dropbox simultaneously, they get the message:

"The Microsoft Access Database engine cannot open or write to the file J:\Law Clerks - Weekly Reports\Reports Database\Assignedby.xls'. It is already opened exclusively by another user, or you need permission to write and view its data."

Usually if one of the users waits a couple of seconds, or minutes, they can then access the dropbox as usual.

 

[kevlray]

Unless the Excel file is set to be shared, then it will not let a second user open the file at the same time. We experimented with sharing an Excel file on our network (only four user) and we encountered a number of issues (probably due to the fact we are on a Novell network).

 

[Badnapp]

That's the funny thing Kevlray, I saved the excel file so that it is a shared file, and thus it should be able to be opened simultaneously by multiple users.

Still I am encountering this issue.

I am starting to think it might be too much trouble to leave this particular table linked. We only have to update the table 2 or 3 times a year, so I should just import it into the database and update it when the time arises.

 

[kevlray]

That would probably eliminate a headache. If it is only updated a few times a year. Just re-importing it probably would work well.

 

[David R]

I concur, if it's only going to be updated every so often, you will find your life much simpler with an imported version. There's probably code out there to check and see if it's been updated, and import it automatically on boot that day, but that may be more trouble than it's worth.

Heck, do you even NEED the Excel file for any other program? You could just keep it strictly in Access and update it there, unless it's one of those 'exported from another system' Excel files.

 

[Flecha Negra]

Hello Badnapp

You said «(probably due to the fact we are on a Novell network)» but... my feeling doesn't point on that direction because.. I also "live" on a Novell network and also have a Excel file shared among all users of my service and we don't have problems to open the file and update it.

Give it a try and talk to your administrators to remove any problem of rights on that specific drive J: Sometimes it's just a question of one missing Novell's Right .

 

[Badnapp]

Thanks everybody for your feedback.

I am of the opinion that it has something to do with the permissions/rights on the network. I can't have the excel file open simultaneously through two different users even though it is a shared file, and that seems odd to me.

However...

The excel spreadsheet only needs to be updated when a new judge is appointed, which happens once a month or couple of months. In that case, I think it would eliminate a lot of headaches if we just imported the spreadsheet so that it is a table in the access database and then I don't have to worry about any of this. At the end of the day I need a database that is simple and robust, I can't have one of the clerks not able to use the program at any given moment and I think the easiest solution is to import it.

At least for now that's what I'm thinking. I am meeting with one of the IT guys in our building today to ask about permissions on the J:\ drive and if that might be related to my excel issue, but we'll see.