MS Access - Limit to # of shared users

November 23, 2009 7:04 PM

Is there a limit to the # of users who can share a Microsoft Access database across a network?

I've been working in Filemaker for so long that it's been a while since I designed in Access. I'm now working for a public school that has no money, but all its machines have Access. The school *really* needs a database, so I'm considering building one using expertise from my previous life in the business world.

Several questions:

- Assuming all 100 teachers in our school has Access on the PC in their classroom, could all 100 access the same Access database simultaneously without problems? I know that Filemaker has a user limit of 250, but that requires Filemaker Server (a separate / more expensive software) be installed on at least the server itself. Does Access have similar limitations?

- Does Access support a web-based module, that teachers not in the building / not on the school network could access? If so, is it worth a damn?

- Is Access easier to design in now than it was years ago (when I switched to Filemaker and was thrilled how much simpler it was)?

Thanks in advance for any help,
Wade

posted by cg1 to computers & internet (12 comments total)

Please don't use access. Use an actual RDBMS - there are many of them that are free, including postgres, mysql, and there is a version of SQLServer that's free also, but limited to about 1 gig of db storage.

If you must use access, it is possible for more than one person to use it, I don't know what the limitation is there. You need to make sure it doesn't get opened in "exclusive" mode.
posted by RustyBrooks at 7:15 PM on November 23, 2009 [1 favorite]

BTW you can use access as a front to many "real" databases, I believe.
posted by RustyBrooks at 7:21 PM on November 23, 2009

What version of Access are you talking about? That will determine the number of simultaneous connections it supports out of the box.

Historically Access had a lot of problems with simultaneous connections, though it's not as bad as it once was (i.e. a recipe for data corruption). Still, have a good back up strategy.

It's still programmed in VBA, which is basically VB6. The development process is going to be pretty much like you remember. Access is one of the least changed parts of Office, even since Office 95 or so.

Access used to have a web front-end feature called Data Access Pages. It was discontinued in Access 2007. Microsoft now pushes Access integration with SharePoint Services on Windows Server.

If you have the programming chops for it, it may make more sense to use a web front-end to a proper database.
posted by jedicus at 7:22 PM on November 23, 2009

Are the users reading or reading and writing?

If writing, 10 users is a nightmare. If the database is read-only, 100 users might work.

As others have suggested, you need to think about this as a multi-user application. Concurrent access will cause you all sorts of headaches, so think through the data flow very carefully. Pay particular attention to race conditions and deadlocks.

A real RDBMS on the backend {SQL Server, MySQL, Oracle, DB2, whatever} will help, but you still need to think about the concurrency issues.
posted by b1tr0t at 7:56 PM on November 23, 2009

Rule of thumb is 50 or less. You can probably squeeze out more if its mostly a read-only app. Wikipedia:

Database solutions created entirely in Microsoft Access are well suited for individual and workgroup use across a network. The number of simultaneous users that can be supported depends on the amount of data, the tasks being performed, level of use, and application design. Generally accepted limits are solutions with 1 GB or less of data (Access supports up to 2 GB) and 50 or fewer simultaneous users.

Also, you will need to host this on a server OS like Windows 2003, not XP or Vista/7. A home OS is crippled to only allow 10 or 15 users on its file share.
posted by damn dirty ape at 8:19 PM on November 23, 2009

I asked my database-programming boyfriend. He says that 100 users trying to get in at once would make it so slow that it would be impossible. Access is a desktop application really not made to be used in this way. If you need to do it cheap use MySQL.
posted by shelleycat at 9:09 PM on November 23, 2009

Along with a good team, I created a read/write Access 2003 database with something like 250 user accounts, and seeing 15 simultaneous users was the upper limit of typical use. It could be touchy, but it was definitely a useful tool at that deployment level.

I had a colleague assert with a straight face that Access was his choice for anything up to 200 simultaneous users. I never got a chance to see if he was serious or not.
posted by NortonDC at 9:16 PM on November 23, 2009

Access is not comparable to filemaker in this situation. As you point out, filemaker server is needed for the higher concurrent user levels. Anything more than about 30 concurrent users (depending upon requirements) is likely to be unacceptably slow, and even that's pushing it.

While I believe it is possible to use an access DB as the backend to a website running IIS (using ASP I think) it's a right horror and to be avoided.

If money is completely unavailable, then a free linux server running postgres or mysql with a php scripted frontend hosted on apache is the defacto standard way of doing things; it's usually called the LAMP stack. If running linux is a problem, it's also possible to do the same on windows, WAMP.

If a little cash is available, microsoft do offer extremely high discounts for education for their server platforms. I think it cost us about £100 for server 2008+sql server 2008, and bout £5 a SQL CAL per user (we already had a volume licence agreement though, which makes life simpler). Tie that into IIS or apache/php for your web front end, and it should be pretty flexible and scalable. You can also whip up a frontend in Access to the same MSSQL database on the backend if you also want a fat client frontend over the LAN.
posted by ArkhanJG at 11:17 PM on November 23, 2009

"The school *really* needs a database"

For what? Don't you really mean they need some database-driven applications?

Please don't cobble together something in Access, they won't love you for it in the long run. Instead, assess the exact requirements then look at what existing solutions (probably web-based for flexibility) are available. And if you don't have a fairly solid background in both web app and network security then make sure you bring in some expertise to sanity-check your proposed solutions, especially when allowing off-site users.
posted by malevolent at 12:13 AM on November 24, 2009

It would help me more to know what you're trying to do with your database.
posted by seanyboy at 1:00 AM on November 24, 2009

I think you severely overestimate the likelihood that teachers would touch Access. But keep in mind that student records are controlled by laws and whatnot, and finances by other laws, and personnel records by yet another, in the US. If your data isn't any of those, then by all means roll out postgres / mysql / access. I'm guessing the DB load would be super light.
posted by pwnguin at 1:44 AM on November 24, 2009

On some machine, install PostgreSQL. I recommend it be a spare computer you can put Ubuntu on.

Then on all machines, install the PostgreSQL ODBC Driver and in MSFT Windows settings, add an ODBC Database Source that points to that machine. Anything that speaks ODBC (like MSFT Access) can then connect to a real database.
posted by cmiller at 4:40 AM on November 24, 2009 [1 favorite]