Multi user problem in Access 97

[patelnitesha]

hi..

i have used one globle variable ...which gives me a new batch refrence numbers.. but when two users are working same time then ...it gives same number to both of them...i want different number as a result to both of them..
like it will give 37679...to both of them as new batch refrence numbr but i want 37679 and next number 37680 .....

plese help me how i can do this.... i really appreciate your help..
if need more information please let me know...

many thanks...

Nitesha

 

[Rabbie]

One solution would be to add 1 to the Global variable as soon as a user accesses it. Need more info about how it is used before I can give you more info on how to do that.

 

[patelnitesha]

hi..

hi....

i have stored this variable in one table ..and created one function which is get the current value from table and increment by one then assign this value to this globle variable and stored new value in the table.

now i call this function from my form when i press ok .... after that it will give me the value of this globle variable which is my new carrier refrence.

hope this will help...

 

[Dennisk]

do you perform table locking. I have a table to hold the last number generated and it works fine but I have built in comprehensive locking.

please Post your code

 

[patelnitesha]

code

sub GetNextBatchRef(carrier_ref As Long)
Dim dbs As database, rst As Recordset
Set dbs = CurrentDb()


Set rst = dbs.OpenRecordset("counter", , dbSeeChanges)
rst.MoveFirst
G_current_carrier_batch_ref = rst!counter

rst.Edit
rst!counter = rst!counter + 1
rst.Update
end sub

 

[patelnitesha]

if i lock the table then...the second person will not be able to use that table same time... and how to create a lock on table using code...

 

[Rabbie]

Your code seems OK for changing the value in the table. Is the table being updated correctly each time it is accessed.

How often is this subroutine called. Because unless it is called every time you need a new carrier ref then G_current_carrier_batch_ref will have the old value in it.

 

[patelnitesha]

this...routine is called every time whne new jobs come in...
this routine is called just before it gives me the new carrier refrence number...

 

[Rabbie]

Is the value in table what you would expect.

I would change your code as follows

sub GetNextBatchRef(carrier_ref As Long)
Dim dbs As database, rst As Recordset
Set dbs = CurrentDb()


Set rst = dbs.OpenRecordset("counter", , dbSeeChanges)
rst.MoveFirst
G_current_carrier_batch_ref = rst!counter

rst.Edit
rst!counter = rst!counter + 1
rst.Update
rst.close
rst.nothing
end sub

HTH

 

[Dennisk]

you need to provide locking otherwise you will get Clashes. This is how it is done in DAO.

Declare Sub Sleep Lib "kernel32" (ByVal dwMS As Long)
public const TABLE_LOCKED =3262

Public Function GetNextAutoNumber() As String
' Function to Emulate Autonumber Generation
' Generates err 3262 if table is locked
On Error GoTo Error_Handler

    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    
    Set db = CurrentDb
    Set rst = db.OpenRecordset("tblAutoNumber", dbOpenDynaset, dbDenyRead)
    
    rst.Edit
        rst!AutoNumber = rst!AutoNumber + 1
    
        'GetNextAutoNumber = Right$("0000" & rst!AutoNumber, LEN_OF_CASENOTE_NUMBER)
        GetNextAutoNumber = rst!AutoNumber
    rst.Update
      
Clean_Exit:
    rst.Close
    db.Close
    Exit Function
    
Error_Handler:
    If Err = TABLE_LOCKED Then
        ' Pause and try again
        Sleep 20
        Resume ' try to open the table again
    Else
        MsgBox "Error " & Err & " " & Err.Description
        Resume Clean_Exit
    End If

End Function

 

[patelnitesha]

hi

i will try this code ...

if i will have a problem then i will be back....

many thanks to both of you.
Nitesha

 

[The_Doc_Man]

Your problem is that global variables really aren't global.

i have used one globle variable ...which gives me a new batch refrence numbers.. but when two users are working same time then ...it gives same number to both of them...i want different number as a result to both of them..

Access is generally predictable. If you start with the same initial conditions for two users who are computing a new number, they will both compute the same new number. Because... global isn't global. A "variable" (in the MODULE sense of that word) is global only among sharers of the same WORKSPACE, not the same DATABASE.

You could probably make this work with an autonumbered table. A recordset shares the same locking problem. Instead, create a new entry in an autonumbered table using the name or initials of the requester - and then read back the last record with that person's initials. The autonumber will be unique and not shared. Use optimistic locking and just do the open, addnew, store the person's code, do the .update, do a .close, and then you could use a DLookup for DMax of the batch number where the person identifier matches the current user.

 

[patelnitesha]

hi...

i am trying the autonumber solutions.... if i will have the problem again then i will be back....

many thanks...

 

[Rabbie]

if i will have the problem again then i will be back.......

A promise or a threat????

 

[PuddinPie]

When running this modual I'm getting an error saying veriable not defined at the LEN_OF_CASENOTE_NUMBER.
Does anyone know of which veriable it should be defined as?

Public Function GetNextAutoNumber() As String
' Function to Emulate Autonumber Generation
' Generates err 3262 if table is locked
On Error GoTo Error_Handler
Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("tblAutoNumber", dbOpenTable, dbDenyRead)
rst.Edit
rst!AutoNumber = rst!AutoNumber + 1
GetNextAutoNumber = Right$("0000" & rst!AutoNumber, LEN_OF_CASENOTE_NUMBER)
rst.Update
Clean_Exit:
rst.Close
db.Close
Exit Function
Error_Handler:
If err = TABLE_LOCKED Then
' Pause and try again
Sleep 20
Resume ' try to open the table again
Else
MsgBox "Error " & err & " " & err.Description
Resume Clean_Exit
End If
End Function

 

[ChrisO]

Nitesha, is your database split?

Chris.