[Webinar] Streamline your web hosting managementRegister Today

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 288
  • Last Modified:

Splitting Access database into multi databases

Splitting Access database that will increase accept concurrent users ?

For example I have one Access database include 30 tables if I distribute these tables on 3 Access databases and I make shortcut to all tables with orginal database and the users access tables from orginal database so this work will increase accept number of concurrent users than one database ?

1 Solution
Michael DyerSenior Systems Support AnalystCommented:
Microsoft says that the latest version of Access Database (2003) supports 255 concurrent users, but in practice you will see that after 15 to 20 simultaneous users connect, performance will degrade.  Splitting the tables into multiple linked databases will not help.  Better to bite the bullet and move to SQL.  Even the free version of SQL Express will give you a much better performance with multiple users.
Dale FyeCommented:

Splitting the data from the application is a good idea, and it is highly encouraged, especially for a multi-user database.  But splitting the data into multiple backends will not increase the number of concurrent users.

I have been known to split my data into multiple backends, where data that is applicable to all aspects of the application is in one data file, and data that is particular to other aspects (HR, finance, operations) resides in separate data tables, but the only reason to do this is if you have a lot of data, and merging it all into a single backend would approach the Access file size limit.  And if you are bumping up against that limit, it is probably time to look into SQL Server or SQL Server Express.
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"But splitting the data into multiple backends will not increase the number of concurrent users."
And ... you will not be able to create Relationships among tables across multiple db's.  Relationships can only be created among tables inside a give db.

And confirming ... no, it will not increase the number of concurrent users, which is a theoretical max of 255 for any single db.

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

egovernmentAuthor Commented:
Exactly I have the number of concurrent users is between 30 and 40 only and I will try to found a temporary solution can manage these users without any problems for one month because after that I will migration the data to sql server.
Luke ChungPresidentCommented:
I would question where the data exists for the limitations on the number of users being around 30. We've run many tests and never seen that kind of degredation in performance. I think it's a myth from Access 2.0 days.

A well designed Access database can support hundreds of users. Of course, what matters is the number of simulateneous users, and what they're doing.

If everyone is just viewing data or entering data into a table, that takes very little work and a large number of people can be supported. If they are all running massive reports and queries wiith data updates, that can still be done but performance would be an issue (on any technology).

If the back-end database is in SQL Server rather than an Access/Jet database, the number of users can be practically unlimited if each user has their own front-end copy of the Access application. Performance issues still apply based on what they are doing, and in many cases SQL Server is slower than Access.

All that saiid, any Access application that is distributed to others with shared data should be a split database design. Here are a few resources we've written:

Splitting Microsoft Access Databases to Improve Performance and Simplify Maintainability

Significantly Improve the Performance of Microsoft Access Databases with Linked Tables

We also offer a commercial product for enterprises, Total Access Startup, that helps with the distribution of databases to each user's desktop and launching the right version of Access.
http://www.fmsinc.com/microsoftaccess/VersionLauncher.asp. That may be overkill for your needs.

Hope this helps.
egovernmentAuthor Commented:
Hi LukeChung-FMS
Thank you for the answer

I describe to you our system how is working and I will wait you to give us a feedback

We split the access in two parts back-end (data) and front-end (other objects).

The data itself availabe on server but this server not in the same building but on other building and connect with wireless network with speed 27mbps and there between 30 to 45 concurrent user connect with the database through network.

The 2nd file include all objects like views/forms/reports/macros and modules availabe on each user client but each few days the database file was damage without know the resan.

The users do all operation on the data like view / modify / add / delete / search / print reports depenednt on user permession or level.

I analysis the data I found there 8 tables have a big number of records between 20000 to 80000 records and these tables the most using from users I suggest to split the main database file into 3 databases 2 of thems will include the 8 each file include 4 and the remain tables will be still on main database file.
Luke ChungPresidentCommented:
Disclaimer: I'm going to describe my personal experience deploying and supporting Access databases, and dealing with database corruption. It involves the use of some commercial products from my firm (FMS). For those who may be offended by this or consider it advertising, please don't be. There are free demos of the programs which may be sufficient to get through the questioners current challenges. This is what I consider our Best Practices. I welcome anyone else's suggestions.

If you are suffering from corruption, that needs to be addressed separately from the scalability and performance issues you raise in your original question.

First, I hope you have a disaster recovery plan in place. You may want to read my paper for what we consider best practices:Creating a Backup and Disaster Recovery Plan for Microsoft Access Database Applications http://www.fmsinc.com/microsoftaccess/taking-over/backup-disaster-recovery-plan.html

Second, Access/Jet databases need to be periodically compacted to minimize corruption and bloat, and for optimal performance. The back-end database with the data is what needs to be compacted. You can do that manually. We created a commercial program Total Visual Agent: http://www.fmsinc.com/microsoftaccess/Scheduler.html, that does it on a schedule with auditing and email notification if something goes wrong.

Third, if you are experiencing corruption after regular database maintenance, it's often caused by a suspect connection/user who disconnects in an improper manner. That can be very difficult to detect and replicate. We have a commercial product, Total Access Admin http://www.fmsinc.com/MicrosoftAccess/monitor.asp, that monitors the people going in and out of an Access database, logs that activity, and flags the people that exit improperly.  If it's happening with the same person, there may be a hardware or network problem causing the corruption.

Finally, it may be possible that the corruption and performance problems are due to the front-end application. Bad code and techniques, corrupt objects, and other issues may be causing crashes and problems that lead to corruption. We address this in a few ways:

1. We adopt, implement, and detect/fix deviations from best practices techniques: http://www.fmsinc.com/MicrosoftAccess/BestPractices.html

2. We implement global error handling that records crashes by users to text files so we have evidence of what failed. In addition to the procedure call stack, current procedure, error number and description, I also want the line number: http://www.fmsinc.com/free/NewTips/VBA/ErrorHandling/LineNumber.html

Hoep this helps.

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now