Database software recommendation - Access? SQL?

[dquan97]

At work, they have an access database with about a thousand records, with 30 concurrent users. The problem is that the program crashes pretty frequently. What kind of software (web-based or not) would be a good substitute? fyi: 99% of the users are basic computer users.

 

[bunker]

May have nothing to do with the database, all though, 30 concurrent users on Access is stretching it. Could be the front end was written poorly. You need to find out why it's crashing.

1000 records hardly requires a powerful database like MS SQL.

 

[MrChad]

Are users using an Access application, or a separate front-end? Microsoft SQL Server is easy to setup and administer, and it can easily handle the load you're describing. MySQL is free and scalable; I haven't used it though. You will likely need to reconfigure your existing front-end for the database or create one if you are using Access as your front end.

 

[tkdkid]

1k records is small. Uhm....write a visual basic app as a replacement front end. Write it such that it opens the connection when needed and closes it when it's finished. That way the number of concurrent users will always be quite small.

You could also write a web app that interfaces with the database. It would work the same way, since the connection would be opened and closed each time a page is requested.

 

[oog]

30 connections is pushing Access. SQL Server is overkill, but MSDE is worth considering. MySQL is also worth considering as a replacement. If they are using Access to provide the front-end, you'll need to put together a new one though.

 

[dquan97]

Originally posted by: MrChad
Are users using an Access application, or a separate front-end? Microsoft SQL Server is easy to setup and administer, and it can easily handle the load you're describing. MySQL is free and scalable; I haven't used it though. You will likely need to reconfigure your existing front-end for the database or create one if you are using Access as your front end.

Users are using an Access application, as the database is located in a shared drive. I've thought about integrating MySQL and make it web-based, but since I work for the gov't...it wont be an easy sell

 

[MonkeyK]

Access can handle it. If you can get away with it, use MSDE (Microsoft Data Engine). Nowadays Access installs will ask if you want to use the Access data engine or MSDE. MSDE is like SQL Server Lite.

In either case, you should recreate the front end in VB. There is one db query method that is faster than others, but I do not remeber what it is. You should find out an use that one.

 

[tkdkid]

Keep in mind that MSDE is limited to 5 concurrent user connections. If the UI is redone in VB, this shouldn't be a problem, since the connections will only be needed for a second or two at most.

Having multiple people (more than 2) open an access database as a file is a really, really terrible way to write any customization. Having 30 people do this is just straight from hell. Access was never intended to be used like that.

Redoing the code in VB and still using the access database should work for you for a while.