HOw to create multi user application using vb6 and access.

Posted on 2006-06-12
Last Modified: 2008-02-01

I have created a application in vb6 and access but right now this application is single user.

Now i have a need to make this multiuser.

Now wht  i am doing is that i am taking all the data from the database and fill it in a recordset object and close the connection and then shows the data in the form fields (fields are unbounded). Whenever a user makes some changes in any records and clicks on the save button then i write a updte query and then exceute that query to update that particular record.

but will this functionality is ok for multiuser enviroment or I have to change in the coding still to make it multiuser.

I have found few answers  related to my topic which says to apply locks and all, but in my condition i got the data in to recordset and there then there is no connectivity between the database and the application and to do operation like upate or insert or delted I am using sql queries.

SO how can i use lock in my application.

And also I also found one more answer which says to have one copy of the database on the server and one copy on the client or user machine which are linked table to the server database.
Can anybody explians me about this conecpt how will it work or wht i have to do to implement this thing into my application
Question by:ziorrinfotech
  • 2
LVL 17

Expert Comment

ID: 16892992
You do not need to use linked tables.  This is a good idea in an Access application, but not need in VB6.

Here are some comments which I hope will help you:~)

Your code should work for maintaining tables. But when creating transactions it may pay to change your code.  There are very few good examples showing how to create multi-users apps using sql statements. Also it depends if you are using ADO or DAO.

Here is the approach that I would go for:

1) When you create your app use a starter exe to shell to your real app say your starter exe could be called MyApp.exe. When you compile your real app give is a name like MyApp10.exe, then next time MyApp11.exe, etc.  Each time you compile you edit the text file MyApp.TXT with the name of the next EXE file.

When you have hundreds of users all using the same exe file, you can never get all of the users to quit, so relasing updates is very hard. So give each user a short-cut to the start exe, say MyApp.exe, which is on a network drive. In this way you can relase updates without needing to ask all users to exit.

2) Table updates.

You already have a good approach, but make sure you put all updates in a transaction block that looks a bit like this:

Global ADO as New zADO ' place all databse functions in a class module, in this way your tabel updates all look and do the same. The following is not VB code but is simplefied to show the logic used for multiuser updates.

OK = ADO.OpenConnectionOK(CN)
lTry = 0

Do ' Start of update look

    OK = False ' set flag

    ADO.BeginTransaction CN ' start a databas transaction
    OK = ADO.UpdatesRunOK(CN) ' Run the database updates

     If OK Then
         OK = ADO.CommitTransactionOK(CN)
     End If
     If OK Then
         Exit Do
     End If

     OK  = ADO.RollBackTranbsaction

     lTry = lTry + 1
     If lTry > 3 Then
         OK =   MsgBox("Unable to save data "+ADO.getLastError(CN), vbExclamation+vbRetryCancel, "Error")
          If OK <> vbRetry Then
            OK = False
            Exit Do ' the updates have failed
           End If
     End If  
     Sleep 2000 ' wait for 2 seconds


ADO.CloseConnection CN

If Not OK Then
    MsgBox "Unable to save data "+ADO.getLastError(CN), vbExclamation, "Error"
End If

The key points about the update loop:

a) It will retry if the update failed. (Say sombody is editing  a table's structrue no updates can take place.)
b) All updates are performed using the same code. Making it easy to switch to SQL Server or MySQL, etc.
c) The connection is only open while the data is being updated. Allowing database maintenance operations.

3) When creating transaction you need to have one of 2 approaches

3.1) Creating transactions

When you create a transaction you can insert records into a table, you need to pickup the Identity (Autonumber or counter) for the new record. How you get the identity depends on which version of access or SQL Server you are using.  But Select @@Ideintiy normally works after you ran the Insert SQL statement. You then add sub-transactions using the identity for the header record so you do not end up with any duplicate records. Again all done using a transaction loop like the one above.

3.2) Locking records

Using ADO you do not lock a record until you move to it within a recordset.  SO you cannot use raw SQL for this function.

Here is a function that locks a record:

sql = "Select ........ where [MyID]=1"
OK = ADO.OpenConnection(CN)
OK = ADO.OpenRSOK(CN, RS, SQL, adOpenDynamic, adLockPessimistic, adCmdText, adUseServer)
OK = ADO.MoveWaitOK(RS)
RS("MyField") = NewValue
OK = ADO.UpdateRS(RS)
OK = ADO.CommisTransactionOK(CN)

Function MoveFirstWaitOK(RS As ADODB.Recordset, Optional psngTimeOutSeconds As Single = 20) As Boolean

' wait until the first record in a recordset is ok to amend

Dim dtWait As Date
Dim dtWait2 As Date
Dim OK As Boolean
Const OneSecond As Double = 1# / (1440 * 60#)
dtWait = Now + OneSecond * psngTimeOutSeconds
OK = False
Do Until Now > dtWait
    ' lock the record
    OK = MoveFirstOK(RS)
    If OK Then Exit Do
    dtWait2 = Now + OneSecond * 2
    OK = False
    Do Until Now > dtWait2
MoveFirstWaitOK = OK
End Function

Public Function MoveFirstOK(RS As ADODB.Recordset) As Boolean

' this is used in a pesemistically locked server-side recordset

On Error GoTo ErrorTrap
MoveFirstOK = True
Exit Function

MoveFirstOK = False
End Function

Public Function OpenRSOK(CN As ADODB.Connection, RS As ADODB.Recordset, SQL As String, Optional CursorType As ADODB.CursorTypeEnum = adOpenForwardOnly, Optional LockType As LockTypeEnum = adLockReadOnly, Optional CommandType As ADODB.CommandTypeEnum = adCmdText, Optional CursorLocation As ADODB.CursorLocationEnum = adUseServer, Optional AppendOnly As Boolean = False) As Boolean

' Opens any type of recordset return true is OK

' to open for locking updates
'OK = OpenRSOK(CN, RS, SQL, adOpenDynamic, adLockPessimistic, adCmdText, adUseServer)

' to open for fast readonly
' but don't use client with large tables
'OK = OpenRSOK(CN, RS, SQ, adOpenStatic + adOpenForwardOnly, adLockReadOnly, adCmdText, adUseClient)

Set RS = New ADODB.Recordset

On Error Resume Next

RS.CursorLocation = CursorLocation
If AppendOnly Then
    ' **** Warning only works with Access
    ' but even then it don't work.
    ' so it is best to use some sql a bit like this
    ' where there will never be a record zero
    ' SQL = "Select * from [myTable] Where [MyID]=0
    ' and open the table for normal updates as appendonly does not seem to work
    ' as it says in the manual
    RS.Properties("Append-Only Rowset") = True
End If
RS.Open SQL, CN, CursorType, LockType, CommandType

If Err.Number <> 0 Then
    Set RS = Nothing
    OpenRSOK = False
    OpenRSOK = True
End If

End Function

Function UpdateOK(RS As ADODB.Recordset, Optional pbUpdateBatch As Boolean = False) As Boolean
' Recordset update
' trap any errors with an update statement
ErrN = 0
ErrD = ""
If Not IDE Then ' when testing you want to see all errors
    On Error Resume Next
End If
If pbUpdateBatch Then
End If
If Err.Number = 0 Then
    UpdateOK = True
    ErrN = Err.Number
    ErrD = Err.Description
    UpdateOK = False
End If

End Function

LVL 17

Expert Comment

ID: 16893315
Another cool way to handle table updates is to use disconnected recordsets.

So when you app starts you can create some dummy disconnected recorsets with the specs for your key tables.
In this way you can put the data into a clone of your dummy recordset and pass the clonde to a generate function that will update any table in your system, thereby cutting down the code needed to handle multi-user senarios to just one function. Also making it easy to debug as the code only needs to be written once.

Also when you have a disconnected recordset you can prepare the data off-line then attach it to a connecttion and table , then run an update.  The problem with this is that SQL server and access sometimes screws up and seems to be unable to handle large batches (it thinks it has worked, does not give any errors, but fails to update some records). So it is better to generate the sql statement and update each record.


Expert Comment

by:Naveen Swamy
ID: 16893984
LVL 12

Accepted Solution

jkaios earned 500 total points
ID: 16916381
If you're using ADO to connect to your Access database, then there is a feature in ADO that allows the "Record-Level" Locking mechanism.
 Set oCN = New ADODB.Connection
 oCN.Provider = "Microsoft.JET.OLEDB.4.0"
 oCN.Properties("Data Source") = "C:\Temp\myDB.mdb"
 oCN.Properties("Jet OLEDB:Database Locking Mode") = 1
 oCN.CursorLocation = adUseServer

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question ( in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now