1

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

Rahul Sharma
  • 63
  • 3
  • 13

2 Answers2

5

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.

Community
  • 1
  • 1
Steve
  • 5,585
  • 2
  • 18
  • 32
  • Thanks !!....application will be installed on 2 computers only that's why i am preferring MS Access as Back end...so only 2 users to access the database... – Rahul Sharma Oct 17 '13 at 21:48
  • 1
    BTW, for a beginner, you might be asking a simpler question like where is the Access database? If so, it needs to be in a location that all users can access it at once. This is usually a file server shared folder or can be on one of the users computer, and they other user talks to the database on the first users computer, also through a shared folder. – Steve Oct 17 '13 at 21:50
  • sir how is it possible to achieve using VB.NET over LAN ? i am preferring the database location one of the users computer. what changes i would have to make in connection string ? – Rahul Sharma Oct 17 '13 at 21:57
  • 1
    What is your specific question. How do you make a share, how do you setup the connection string, how do you do concurrency... – Steve Oct 17 '13 at 21:59
  • how to set connection string for multi user application in app.config? and How to make share of DB ? DB will be exist on one of the users computer and other user must be able to access that database and make changes to database...1st i want to know about all initial steps then i will work on its concurrency issue :) – Rahul Sharma Oct 17 '13 at 22:06
  • 1
    The connection string isn't much different than it is now. If it currently points to c:\Temp\MyDatabase.mdb, then you just need to point it to the shared folder. This is best if you use UNC rather than mapped drive letter. IE, \\JimsComputer\SharedFolder\MyApp\MyDatabase.mdb – Steve Oct 17 '13 at 22:21
  • I think there is an exclusive flag in access still but unless your connection string is configured for exclusive access, it is already allowing for multiple users. To test this, run the EXE for your application twice. Tada.. multiple users. – Steve Oct 17 '13 at 22:23
  • sir my current connection string is Dim cs As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\PayrollManagerDB.accdb;Persist Security Info=False;" so you mean i need to make changes in Data Source=\\JimsComputer\SharedFolder\MyApp\MyDatabase.mdb – Rahul Sharma Oct 17 '13 at 22:34
  • let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/39453/discussion-between-steve-and-rahul-sharma) – Steve Oct 17 '13 at 22:36
1

There are a number of solutions here.

If I may suggest a drastic alternative, have you considered pairing the client running on the user's computer with a server component (through a web service)? A simpler alternative would be for the client to talk directly to a SQL Server (or other database) instance through the network?*

*I'm not a fan of having client side apps talk directly to the database. It will mean maintenance headaches in the future, but I included it to give you options

.

I found this random example via Google so YMMV.

JFish222
  • 1,026
  • 7
  • 11