Help with a Multi-user database application

[Loony064]

Hey everyone,

I created a program for one of my clients which uses a back-end MS Access database with a front-end VB.net application that allows the user to enter details into various tables, search through the information already stored there and lastly, generated various reports based on specific criteria.

My client has now asked me to extend the program so that it can perform all the tasks they require. The most important criteria is that their employee's must be able to use the program concurrently on more than one terminal and any information one adds, reflects on all other terminals.

An example of the kind of thing they need is like in a retail environment: There are two computer terminals that can both perform sales transactions. The terminals show how many of a specific item there are in stock. If Employee A makes a sale, the number of that particular item in stock decreases, and must reflect on Employee B's terminal.

I've never done anything like this and was wondering if someone could give me suggestions on how to do it and where to start.

Any help would be greatly appreciated.
Regards
Laura.

[Teme64]

MS Access wasn't developed for a multi-user, concurrent environment needs. I would suggest some other back-end database. Good alternatives are SQL Server 2005/2008 (free Express Editions, check the end user license agreement) and MySQL (free Community Edition, check the end user license agreement). Both are "real" db's i.e. they support record level locking and concurrency, have full .NET programming support and there's a lot of code samples and tutorials for them. I would say that if you know MS Access, the step to use SQL Server is quite small, maybe a slightly bigger for MySQL.

If your application doesn't rely on MS Access specific features i.e. you only use it to store, retrieve and manipulate data, porting the application to use SQL Server or MySQL should be an easy task. Adding the concurrency support is just a snap after that.

This is just to make you to think of using the right tool in the first place. The final decision will probably be made by your client.

And I'm not saying that you couldn't get the job done with MS Access. I just wanted you to think of different approach to your task.

[Teme64]

If you decide to stick with MS Access, start with some googling

http://www.google.com/search?q="ms+access"+"concurrency"
http://www.google.com/search?q="ms+access"+"concurrency"+".NET"
http://www.google.com/search?q="ms+access"+"concurrency"+"VB.NET"

There seemed to come some useful articles, issues and samples.

[Loony064]

Thanks for the advice, Teme64! I'll definitely look into using that. My only problem is that I use MS Access for generating reports. I know that Crystal Reports would be a good substitute but unfortunately it costs too much for it to be worthwhile for this project.

If you have any idea's or suggestions, or another, less expensive program i can use to generate reports which is compatibly with VB and SQL, I'd appreciate it as well.

Regards
Laura

[Teme64]

My only problem is that I use MS Access for generating reports.

Ok. Then you should stick with MS Access. If you're familiar with MS Access and its reporting, you'll save the learning phase needed with any other solution. Use that saved time to learn concurrency issues with MS Access.

Like I said, you can get the job done with a few user environment. See the google links from my previous post if they get you started with concurrency issue and problems you may encounter.

Once you get started and run in to a problem you can't get solved by yourself, you know where to look for help :)

[RobinTheHood]

I've tried replyinga couple of times to this thread without success, heres my last attempt.

Hi,

I've used an Ms Access backend for the past 5 years that often handles 30+ concurrent users without experiencing any problems. Although I'm not suggesting access is in any way comparable to the likes of SQL or MySQL, you can certainly use it for small-medium user groups.

Make sure you have just one backend located on the server and install the front end on each terminal (ensure each front end is linked to the backend). Any changes to the data in the tables will then be reflected on each terminal.

Words of advice.
1) Be sure to make regular back-ups of the BE as unlike SQL there's no reverse gear in access.

2) I'm sure from time to time you'll need to make changes to the GUI and you should give some thought to how your going to update the front-end without having to go from terminal to terminal every time.

3) Consider taking the hit on reporting software and migrate to one of the suggested backends. You'll be much, much better of in the long run.

4) MAKE REGULAR BACKUPS OF YOUR DATA

5) MAKE REGULAR BACKUPS OF YOUR DATA

6) MAKE REGULAR BACKUPS OF YOUR DATA

Regards
RobinTheHood

[Teme64]

I've used an Ms Access backend for the past 5 years that often handles 30+ concurrent users without experiencing any problems. Although I'm not suggesting access is in any way comparable to the likes of SQL or MySQL, you can certainly use it for small-medium user groups.

For the Loony064's purposes MS Access will do just fine. I just wanted to point out few other options.

One thing to keep in mind when building successfully multi-user applications, is to separate DB (back-end) code, business logic and UI code. In a good N-tier design and implementation, it shouldn't make much difference in the long term which back-end DB you have. With a scalable application you can "swap" the back-end DB if you need more power in the DB part.

1) Be sure to make regular back-ups of the BE as unlike SQL there's no reverse gear in access.

You should make regular back-ups no matter what your BE is.

4) MAKE REGULAR BACKUPS OF YOUR DATA

I totally agree with that!

5) MAKE REGULAR BACKUPS OF YOUR DATA

I totally agree with that!

6) MAKE REGULAR BACKUPS OF YOUR DATA

I totally agree with that!

:)