Web Development

General discussion


Microsoft Access Database Front End

By TechSupportSpecialist ·
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.

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

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.

Collapse -

by akalinowski In reply to Microsoft Access Database ...

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

Collapse -

by akalinowski In reply to

almost forgot, there is a free version of ms sql server:

express edition, works great, database size maxes out @ 4GB but access maxes @ 2GB so i guess its not a big deal.

Collapse -

by cmiller5400 In reply to Microsoft Access Database ...

For 20 users I would sugguest that you convert your back end to SQL. If you can not afford to buy a SQL server, look at MSDE http://msdn.microsoft.com/SQL/2000/MSDE/default.aspx It is a dummed down version of SQL server. The only thing with MSDE is that the more users that connect to the database the slower the performance.

Collapse -

by cmiller5400 In reply to Microsoft Access Database ...

Here is a link about the performance issues. Should not be too noticable with 20 users. http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_sa2_0ciq.asp

Collapse -

by garycondit In reply to Microsoft Access Database ...

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

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 :)

Collapse -

by sgt_shultz In reply to Microsoft Access Database ...

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?

Collapse -

by JJ8392 In reply to Microsoft Access Database ...

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).

Collapse -

your idea is correct...

by dennpuno In reply to Microsoft Access Database ...

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.

Collapse -

Database Front Ends

by sturdygrinding In reply to Microsoft Access Database ...

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.

Related Discussions

Related Forums