Web Development

Ask something
Discussion Locked

Microsoft Access Database Front End

0 helpful
11 replies

by techsupportspecialist 1 view

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Hello everyone.

I have an issue with a Microsoft Access Runtime Database. Presently, we have an Access database that has been split into a front end and back end (Sales.mdb and Sales_be.mdb respectively). Both of these files currently reside on a mapped drive to which 20+ users have access. The Access Runtime shell has been installed on each user's workstation and shortcut to Sales.mdb (the front end) has been placed on each desktop.

With the large size of our database, users have been having performance issues. They work for a few minutes, performance gets slow, they get a runtime error and then we have to get everyone out. After they get out, we compact/repair both the front end and back back end. Then, everyone can go back in and work for awhile. It happens within a short period of time after.

I guess that what I really want to know is if there's anything I can do to stop these runtime errors. They are for no apparent reason. We compact/repair the database nightly and we even have a timer that will get users out of the system after 1 hour of inactivity.

I came up with an idea, and I want the opinion of an Access expert. Most of the time, the 'broken' database tend to be the front end. I have considered putting the front end on each user's workstation instead of having them share it on the server. A consultant that we met with a couple years ago said that it was better if both the front end and back end were in the same directory, but I'm starting to think the opposite may be true. If that's the case, I'm going to create a script that copies the newest version to there workstation everytime they log in. I want to know if that's likely to help though. Regardless, I would also like any other suggestions for minimizing the number of times this issue occurs.

Thank you very much.

11 replies

by dave the computer guy

Well one suggestion would be to convert your front-end to InfoPath. I'm not sure how many forms you have or how complex they are but it might be worth taking a look at. InfoPath is a form writing program that comes with Office 2003. You may have to go into the Add/Remove programs and modify your office install as it is not part of a typical installation but it is an option to install. I designed a few forms in it for Access and SQL and it took me about 4 hours to pick up on how it works and I'm no forms/database expert.

by akalinowski

hate to give a negative response, but everything i've read about ms access says that you dont want to have more than 10 users on it at anytime and SQL server is recomended for more users

i'd say time to switch, but i hav limited experience w/ MS Access, i've always gone directly to SQL

by garycondit

"I have considered putting the front end on each user's workstation instead of having them share it on the server"

Hi,
That is the whole point of having a front and back end!!! You are right and the consultant is not...

Put the back end on a network drive and the front on each users machine...

this may not solve your problem, but it is a good first step. (i.e. should be done anyway)

good luck :)

by sgt_shultz

anything in the event logs on the back end server. you do have it on a server, right? you are not just using a workstation?
what os are the clients.
did this ever work right?
post the exact error?

by jj8392

Your consultant was wrong - you need to run the frontend from the user's pc. You'll see an immediate speed increase. You really 'don't' have a backend (in that there is no backend server processing for you). You should go to sql whenever you can (mysql would even be better than access backend).

by dennpuno

The problem i see in this design is the refresh issues coz they all access one front end.I think Sales_be.mdb should be stored in the server while the Sales.mdb reside on their local machine. You might need to add a module in the front-end to open Link Manager.

by sturdygrinding

It is my understanding that Access was designed so that the back end of the database would be stored in one central location and the front ends would be handed out to users of the database. I created and manage the database at my company and that is how I have done it. Each user has a front end on their pc which is linked to the back end stored on a central pc which I'm actually using like a server. When you copy the front end on to the individual pc's, you may have to recreate the links to the tables in the back end. I don't have quite as many users as you do. (I only have 5 people using the company database for various reasons.) However, I haven't had any run time errors or other problems. I have read a few books on Microsoft Access as well as taken training classes and it is my understanding that this is the best way to mantain a multi-user database in Microsoft Access.

by sarahsdaughter

Your consultant was wrong. Create your script and let each user work off of their own copy. We work with a SQL Server back end for our data, an Access front end that resides on a file server and a script that copies the dB onto each users workstation when they open it. It has been working for several years now without a hitch and seems to overcome Access' dislike of multiple users.

It also makes it much easier to update and make changes to the dB because I can make the changes, export them to the server based front end and they are distributed to the users automatically the next time they open the dB. It also allows them to make temporary changes to database objects where necessary without those changes being translated to the "master copy".

This discussion is closed to new replies.