Developing a multi-user program using Visual Basic
Hi to all! I'm developing a multi-user application using visual basic. The database (MSAccess XP) resides on the server. The vb program is running on five different computers in a network accessing the same database on the server through ODBC. I'm using ADO to connect to the database. My problem is what should i add in my code to handle if in case two or more users attempted to write or update to the database at the same time? What I want is to lock the database or just a table to prevent other users from updating the same data.
right now I have one idea in mind. This is to set the locktype property of the adodc to adReadonly if I will be updating the database. Will this work?
How can I lock a certain table only (not the whole database) thus allowing other unaffected tables to be access and updated by other users ?
and also i've heard that msaccess database can only handle up to 3 concurrent users? is this true? cause the program is to be used by five users at the same time.
Anybody who could give me some information and help me will be greatly appreciated. Thanks!
[1143 byte] By [
nokiamania] at [2007-11-17 16:58:11]
# 1 Re: Developing a multi-user program using Visual Basic
hi,
you should not use adReadonly, readonly option for only retriving data purpose, you can't update wih readonly mode.
And also, you should not lock the entire table or entire database in multi environment, because the same table may use more than one user and they need to update.
you can use adLockOptimistic option, this will lock only the updating records.
K.Babu
K.Babu at 2007-11-10 0:23:12 >
# 2 Re: Developing a multi-user program using Visual Basic
i have 30 users at the same time using my access database with no worries. There is a setting in access under tools > options that allow you to lock the record that is being edited and if someone else tries to change that record it will give them a message box saying it is locked and another user is editing it.
that should work.
later...
# 3 Re: Developing a multi-user program using Visual Basic
Thanks for all the replies! :)
K. Babu : thanks! I will set the locktype to adLockOptimistic
TheIceman5 : Thanks man! You just gave me what i need :) Before my database has no locking in its options. Now i've already set is to lock the edited records.
I think this one will work :)
if anyone could give me more information regarding multi-user application using msaccess and vb will be highly appreciated, because i'm a newbie to this kind of stuff :)
Thanks again!
# 4 Re: Developing a multi-user program using Visual Basic
You can set the lock type of your recordset to optimistic or pesimistic. Optomistic gives you greater flexibility, as it only tries to lock the record if you start to make changes. Only problem is, Access locks in 2kb pages, which means that you can lock records around your record as well.
I have an application, which checks when you last loged on (from a recordset), runs a query to see if you have any new tasks since then, and then updates the recordset to set the last logged on date to today. Sometimes I can get a lock because someone else is logging on at the same time.
You can monitor for an error code and do something in the event of a lock.
# 5 Re: Developing a multi-user program using Visual Basic
my understanding is that concurrency is handle mainly by the database when you use the optimistic locking, is this correct?
when the system locks a record, does this mean it is locked both for reading and writing of other users? or just locked for writing?
Assuming I am using ADO recordsets, when does the system starts to lock a recordset? When you assign sql statement to the recordsource? or when you issue the .update command? Cause i can foresee some problems that might occur which will result to inaccurate data.
# 6 Re: Developing a multi-user program using Visual Basic
1. optimistic lock the records only at update time, that means when call update.
2. when at open with sql stament using with adlockoptimistic, it can't lock any records
3. you can read when the adlockoptimistic lock (update time), but you can't write or update the records.
K.Babu
K.Babu at 2007-11-10 0:28:10 >
# 7 Re: Developing a multi-user program using Visual Basic
K. Babu: What will happen if in case a record your updating is locked by other users? The system will tell you the record is locked, right? So will it wait for the record to be free and do the corresponding updates or it will just bypass the update?
Cause in my application, I have a sequential update, i have two adodc objects updating records in two different tables. This is done in sequence, what i'm concern is if the first adodc has successfully update the record and the second adodc failed to do so because the record it wants to update is locked. This will cause my database to be inconsistent. How will i handle this?
# 8 Re: Developing a multi-user program using Visual Basic
I am looking for an code example of a simple
multi user application using VB accessing
oracle/sqlserver database.
Thanks
Sai
# 9 Re: Developing a multi-user program using Visual Basic
Hi nokiamania,
Another approach that can be used when using a db in a multi-user environment, is instead of locking your records using the built-in database locking mechanisms, to set your own flag on each record (ie, have a separate field in the database that holds a timestamp, or the ID of the user who retrieved it for editing, or both). This is set when you retrieve your record, and cleared when updated.
This approach wouldnt suit all scenarios, but does have a few advantages. You can close your connection while editing your data. You dont need to worry about a connection being lost due to machine or network failure, and a record staying locked. You can also use the timestamp to determine how long since the record was retrieved and set a time limit on editing.
This approach also provides good portability to creating a web solution, if you ever wanted to go down that road.
Cheers.
