Access database on a server

I want to create a contact manager database in Access. Is it possible to create a database in Access, copy it to the server, than install Access Runtime 2013 on each PC to open the database on the server? And just have one full copy of Access to create and modify the database.
Thanks
Dt
LVL 2
dronethoughtAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MacroShadowCommented:
Technically, yes but it isn't recommended. Access is not the optimal solution for a multi-user scenario. It is not trivial to set up and if not done properly, will cause a lot of issues. That is besides the inherit limitations such as a limit of 10 simultaneous users etc.
You can create the database in Sql Server or MySql and use access as the front end only.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Yes, you certainly can do that and it's not as bad as Macro Shadow makes it out to be

This:

" That is besides the inherit limitations such as a limit of 10 simultaneous users etc."

is just flat out false.  But if you are thinking of more than 30 or 40 users (assuming everyone is read/write), it would be best to use SQL Server as a data store rather than JET.

 Jim
1
Dale FyeCommented:
I strongly disagree with MacroShadow.

"It is not trivial to set up"
Disagree.  It is as simple as putting the data on the server.  Linking the tables from the server to your front end application (recommend using the full UNC path), and then deploying to your users.  Many of us professional Access developers create an application launcher which will copy a new copy of the front-end to each users computer whenever they attempt to launch your application.  This allows you to continue to edit and improve your application and makes it easy to ensure they always have the latest version.

"the inherit limitations such as a limit of 10 simultaneous users"
There is no such limit.  Access will work fine in a mulit-user environment, with the data on the server and runtime versions of Access on each users computer.  I have applications which have over 25 simultaneous users which don't have any problems.  As long as these users are working on a hard-wired LAN, you should have no problems with this configuration.  Do not attempt to use Access with an Access back-end over a Wi-Fi network.

I agree that SQL Server (there is a free Express edition) is a great alternative to Access as a backend, but there is a learning curve to using SQL Server, and simply converting your Access backend to SQL Server may actually slow down your application unless you migrate much of your application functionality to SQL Server via pass-thru queries, SQL Server views and stored procedures.
1
Creating Active Directory Users from a Text File

If your organization has a need to mass-create AD user accounts, watch this video to see how its done without the need for scripting or other unnecessary complexities.

MacroShadowCommented:
The fact that such top Experts in the field are so vehemently opposed to my answer made me do some research.

First of all, as mentioned by both Jim and Dale, I was mistaken, there is no theoretical 10 user limit in Access. Access theoretically supports up to 255 users. The 10 user limit is a limit in the non-server editions of Windows which is true for all applications (including Access) when using peer-to-peer networking. There will be a practical user limit based on the design of the database, the more professional the developer the higher that limit will be.
 
When writing "It is not trivial to set up" I meant that the database has to be designed in mind for a multi-user environment. More info on the matter can be found in this article by Tom Wickerath.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<When writing "It is not trivial to set up" I meant that the database has to be designed in mind for a multi-user environment.>>

 But this is true of any development effort, not just with Access.   I would concede that Access appears to make it look easy, and that's mainly why Access and JET end up with a bad reputation.

 Multi-user apps that run over a network are not easy, even with databases such as SQL Server.   Develop a few badly performing queries and you can even bring SQL Server to it's knees.   And all the other issues apply; indexing, concurrency, etc.

 Access does have some significant drawbacks for application development:

1. Can't create a standalone .EXE
2. Can't use many 3rd party controls
3. Can't do a true 3 tier design.
4. It's sensitive to it's environment (more so than a true .EXE)

But it's also got a lot of pluses too:
1. Easy design environment.
2. Built-in query and report designers.
3. Good all around coding language
4. Can connect to just about anything and do heterogeneous joins.

 So it's tough to beat as long as you can live with the drawbacks.   If I was developing a commercial app though, or something for hundreds or thousands of users, I'd be looking else where.

 As for the data  store, JET/ACE is the default and yes while technically it says 255 users, the practical limit is about 30-40.   The reason being is that all DB processing is done client side.  The server acts as nothing more than a file share.   Since there is no central process to perform a roll back, abnormal disconnects become a problem.   By the time you get to 30 or 40 users, it gets hard to maintain a stable situation.

  It's also the point where your really looking to get features such as on-line backups and roll forward capabilities, which JET/ACE lacks.   Also security.  if your looking for security, you need to look to something else right away.

 Performance though as most suggest is not the issue.  If the users are mostly read-only, you can go right up to 255 users without issue.

  Where you get into performance issues with JET/ACE is when you start to get into the millions of rows, but not with the number of users.

Jim.
1
PatHartmanCommented:
Macro,
With Access as a FE and SQL Server or some other "real" database engine as the BE, the concurrent user count is limited by the BE NOT the Access FE.  Every user has his own copy of the Access FE so no sharing occurs.

dt,
Make sure the application is properly split.  Only the BE (data tables) database resides on the server.  The FE (forms, reports, code, etc) is copied to each workstation and so it is never shared.  This split design supports 50+ users easily unless you have heavy data needs.  In that case, using SQL Server as the BE would be better since an Access FE, properly constructed can take advantage of SQL Server's ability to process queries on the server and return only requested records to the local workstation.
0
dronethoughtAuthor Commented:
Thank you all!!!!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.