Software

Ask something
Discussion Locked

Which DB?

0 helpful
11 replies

by mhowarth

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

I am currently trying to put a report together for my MD, assessing the pro's and con's of an inhouse development project and buying a pacakaged solution for our stock control system.

Another party within the company with little technical knowledge has got involved and is pushing for us to go down the Access route. I am personally against this given whilst we are a small company now (15 people) over the next year major acquisitions are going to be made and I have visions of solution being quickly outgrown.

Can anyone advise me on Access limitations with regard to concurrent connections in a networked environment?

11 replies

by monkeybutter

According to the MS spec, Access is supposed to be able to handle something like 32 concurrent users. In reality, you end up experiencing problems after about 5, and the entire thing will fall over after about 8 or 9.

If you've only got 5 peopleusing the database, you can get away with access, but any more and you'll want something better suited to larger numbers of simultaneous users. If you're in a MS miasma, I'd suggest SQL Server as a client/server database. You can then use Access as a front end, and only the sysadmin needs to learn anything new.

Access's primary problem is (arguably) its scalability. As a simple shared file database system for relatively small efforts, it's good at what it does. You try and inflate it, it'll pop.

by mg

My company ran Access for years with about 10 regular users (most doing SELECTs, not INSERTs or UPDATEs). There were frequent headaches and modifying the system (my duties) could be hellacious.

We now use SQL Server because we reached Access' file size limit. We still have problems, but that stems from bad design rather than from SQL Server itself.

by mg

We are now looking (very slowly) at the idea of purchasing an ERP system to replace what has been entirely in-house designed.

The Pros and Cons as I see them:
PRO: With careful evaluation and vendor selection, you can end up with a wonderful system that is a joy to use. Conversely, if you haven't considered all aspects of what you need/want; a purchased system becomes a White Elephant.

CON: The first and biggest con against a vendor is always cost. In-house development will probably bea cheaper route; if the team is capable and has support from management.

The biggest pro for in-house development is that you can get exactly what you want in a system and you'll (probably) have the system designers on-hand for fixes and enhancement.

The biggest con for in-house...time. Doing it right will take a much greater length of time than buying from a vendor.

by gwhiting

We had many clients start with Access but had to move to a real server based Database. With one we found that they were rebuilding Access daily due to locking up. We were able to track down that when ever they were working with Access and a client hit the site to access the same Access file it would hung. This client has had no problems since converting everyting to MySQL with SQLyog client interface. MySQL is FREE and can grow with you almost up to the size of Microsoft without costing you an arm and leg.

By the way we choose ourselves to convert everything from Microsoft SQL 7.0 to MySQL rather than MS SQL 2000 since we could not afford the high price of Microsoft's upgrade. We have been very please and even now working toward transforming our whole shop into a non-Microsoft ISP.

by fredfr

I have Unix, Linux & Win2000 servers; Unix & Linux NEVER breaks down or needs rebooting. Using Oracle (very expensive) on Unix; MySQL on Linux (free); got rid of all databases on Windows (constantly had to upgrade, and reboots needed daily). Oracleis custom design(expensive, time-consuming) & MySQL talks to Java (reasonably priced development). Everyone who starts uses Access to prototype, but move to Oracle or MySQL for development and production. By the way, been doing database design for 22 years....

by martyconnelly

The MySQL database server is available without a license fee under the GNU General Public License (GPL). Commercial non GPL licenses are available.
Unless you wan't to publish your application sever software under GPL. If your application is not licensed under GPL or compatible OSI license
approved by MySQL AB and you intend to distribute MySQL software (be that internally or externally), you must first obtain a commercial license $450 to the MySQL software in question.
Look at the furore this is causing with MySQL taking over the distribution of SAPDB which was open source and free with no GPL.

by the chad

I recently was forced to use Access for reporting on a large data set (about 2 million rows); management didn't want to use a real database, because Access is 'free' with MS Office.

Well, when you have many rows (not sure what the limit is), you are unable to modify your reports or queries -- not to mention that reports take forever to run.

My procedure was:
1) Export all the rows to a flat file
2) Change the reports/queries (even costmetic changes!)
3) Import the rows back in and generate the report, and
4) Go home

Importing and running took about 4-6 hours, and this was on a fast machine with all files local.

The bottom line is that Access is just fine for simple things, but when you get going with sizable datafiles/complicated queries, etc., then don't buy cheap.

Oh, and MySQL is not as good as many make it out to be, because it doesn't have triggers or stored procedures.
(see http://det-dbalice.if.pw.edu.pl/det-dbalice/documents/all/html/db_compare/db_compar_chp03.html)

by rpmcaninch

I designed a db using access at work. We have 5 people who access it all day long. It locks up periodically because it is NOT a multiuser package. The rule of thumb - Access is ok for 5 or fewer concurent users and the db will be less than 1G. Use SQL Server if more required.

by peleg

Access is really two tools in one. It has a very powerful front end (that is, the enviornment in which you write all your screens and reports) and a back-end database engine, called "Jet". Now, even MS doesn't claim that the Jet back-end is truly industrial-strength and that for those larger applications, some sort of SQL server is required. But I want to emphasize that the front-end works well and it *is* industrial-strength when used with a SQL server back-end.

Don't overlook or denigrateAccess because of the limitations of the Jet database engine. The Jet engine is handy and has its uses. The front-end is equally useful for Jet back-ends or SQL server back-ends.

I suggest that you try to use Access to do some small application,using either back-end, and you will begin to get a sense of the power of Access as a front-end.

by interpid2002

I am a software engineer and have developed in Access since Microsoft First released it. It is possible to develop a custom solution in access with one or more access back end db's and run without problems. The beauty is that the initial app can be built with an Access front-end/back-end platform and then up-scaled to a SQL backend solution quite easily when you outgrow the access backend db. Access does have a 2GB limitation on the db size, but is usually takes a while before you hit it, unless you are storing image files in the db. The way to get around this is multiple access db's store specific data. Example: One access back end db for products and detail, one for customer data and one for product photos/images. This can all be access through ODBC connectivity. I have developed solutions for sales environments with 15 to 20 concurrent users (don't exceed 25). Be sure that your network infrastructure is adequate and that you have a powerful enough server and adequate workstation with enough memory. Don't try this solution over antiquated hardware!!! Additionally, if you find a professional developer (not some access macro builder), he/she will be able to build the app so that you do not need to buy Office or access. They will build it using a runtime license found in the Office Developer tool kit. Again, you need to find a professional developer usually a VB/SQL client server developer who has done VBA and office development since version 7.0 (office 9.5). Anyone who saysthat you can not run over 5 concurrent users or that Access does not perform well in a networked front-end/back-end environment does not possess hands-on real life development experience with the product. Remember, you will always be able to upgradethe back end data source to a more powerful RDBMS later down the road when you need it. (Be sure to get the source code and development .mdb from the developer)

by miles.felton

While a pure Access database does not scale to many users, you can upgrade the data to SQL Server by the use of a wizard. You can then use Access to link to the tables in the other database. Multiple copies of Access can access the same DB. Note that with little more effort you can transfer the data to MySQL (which is free) if you wish, or to Oracle, or any ODBC database. Remember that Access is first and foremost a database front-end, and a pretty good one at that. Do not forget security and get a distribution kit.

This discussion is closed to new replies.