Solved

HOw to create multi user application using vb6 and access.

Posted on 2006-06-12
4
1,020 Views
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
0
Comment
Question by:ziorrinfotech
  • 2
4 Comments
 
LVL 17

Expert Comment

by:inthedark
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

Loop

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)
ADO.BeginTransaction
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
        DoEvents
    Loop
Loop
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
RS.MoveFirst
MoveFirstOK = True
Exit Function

ErrorTrap:
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
Err.Clear

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
Else
    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 = ""
Err.Clear
If Not IDE Then ' when testing you want to see all errors
    On Error Resume Next
End If
If pbUpdateBatch Then
    RS.UpdateBatch
Else
    RS.Update
End If
If Err.Number = 0 Then
    UpdateOK = True
Else
    ErrN = Err.Number
    ErrD = Err.Description
    UpdateOK = False
End If

End Function












0
 
LVL 17

Expert Comment

by:inthedark
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.


0
 
LVL 9

Expert Comment

by:Naveen Swamy
ID: 16893984
0
 
LVL 12

Accepted Solution

by:
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
 oCN.Open
---------------------------------------------------------------------
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

758 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

21 Experts available now in Live!

Get 1:1 Help Now