Search code examples
vb.netms-access-2007

How to make VB.NET application work as Multi-user?

I am developing a VB.Net application. That application might be working on a LAN. MS Access as a back end will be used. I have developed many single user applications, but don't know of multi user , LAN, manage DB etc. How do I make the program as Multi user on LAN. Data will be accessed at the same time. How to manage such things. Please give me some help and Guidance.

Thanks

Solution

  • Your VB application does not care how many people run it.

    Your database, with MS Access, has some serious issues with multiple users. Get away from it if you can. SQL Server has a free version called SQL Express. If you only plan on 2 people, you might be OK with Access for a while but be prepared to support it more.

    That was all the easy stuff, now you have to think about how you are going to handle multiple users trying to access and update the same data (concurrency).

    Imagine this, you are a user looking at employee record 1 and so is someone else. You change the birthday and save. The the other user changes thier suppervisor and saves. How do you know something changed? What do you do if something changed? These are questions I cannot answer for you, you must decide based on your situation.

    There are 2 main types of concurrency, optimistic and pessimistic. See this link for a great explaination and discussion on them: optimistic-vs-pessimistic-locking

    You can look at this on a table-by-table basis.

    1. If a table is never updated, you dont have to worry about concurrency
    2. If a table is rarely updated, like a table of states, you can decide if it is worth the extra effort to add concurrency.
    3. Everything else, pretty much should have some type of concurrency.

    Now, the million dollar question, how?

    You will find as many ways to handle concurrency as you will find colors in the rainbow. Here are some of the ones I like:

    1. Simple number that you increment with each save. Small and easy.
    2. DateTime stamp - As long as you dont expect to ever have 2 people save the same record during the same second, this is easy. (I personally dont like it by it's self)
    3. User Name - Pretty simple gives a little bit of an audit by knowing who last inserted/edited the record but doesn't handle an issue I have seen to often. Imagine the same senerio as above but you had 2 instances of record 1. Now you change the data again, maybe supervisor, and when you save, you overwrite the changes from your first save with those of the second save.
    4. Guid - VB can create a guid, SQL Server can create a guid and so can Access. It is nice an unique and most important, you can create it on the client so you dont have to requery the database after you save the record to get a refreshed record.
    5. Combination of these. I like 2 and 3 myself. Gives a mini audit and is unique to the user.

    If you use a DataAdapter, by default, MS will assume concurrency checking means to compare EVERY field to make sure it did not change. This works, but is completely un-scaleable and should not be done.

    All of this depends on the size of your application and how you see it being used. Definately do some more research before you settle on a decision.