Concurrent Users w/Access&ADO 1

[chadrj]

I have an application that uses ADO to access an Access 97 DB. Everything works fine with one user, but in testing with multiple users I get various errors. I am having a hard time finding any information on how to handle multiple users with different recordsets from the same table.

The problem occurs when I carry out the following process on 2 separate machines

Machine 1 - Add new record
Machine 2 - add new record

Machine 1 - modify record previousy added by Machine 1 * try to update ***Here is where the error occurs. The record does not update because it has been deleted from the database. I must repair the database each time this happens.

I can provide more information if needed, but want to avoid composing a novel.

Thanks!

 

[CCLINT]

Use for the recordset a client side cursor and adLockBatchOptimistic and UpdateBatch for updating.
Are you also using AutoNumber fields? Don't, unless you requery the db after an AddNew, or, upgrade to JET 4 MDB (2000).

 

[chadrj]

Well, my cursor is client side, and I have changed to adLockBatchOptimistic and Update batch.

I am using an AutoNumber field as the key, but want to keep it that way if I just need to requery after an addnew. At what point after the addnew do I need to requery?

Thanks for your help!

 

[chadrj]

Since last post... trial and error with little success. Some more tidbits of information that may help others identify the problem.

1) The program works fine if I run 2 instances on my machine

2) The error in the original post actually occurs when Machine 2 tries to update. It appears to delete the record created on machine one (but again, this only happens when I use 2 separate machines

3) I have grown weary of running back and forth from office to office to test this so I have aquired an additional computer, but am having a heck of a time getting the installation to take. I get "ADO could not find the specified provider" errors for each ADO control

4) I keep having an overwhelming urge to throw my computer out the window, but am unsure if that will help.

 

[CCLINT]

In your leading question you said:
Machine 1 - Add new record
Machine 2 - add new record
Did you mean delete on the second line?

There are two problems:
One has to do with the AutoNumber, and the other has to do with optimistic lock cursors when a record is changed by two recordsets opened during the same time.
The latter is normal optimistic locking (thread222-649545)
The former has to do with the newely record not found because the recordset does not have the newly inserted AutoNumber. With JET 4 Mdbs, the JET provider will return the new AutoNumber. But on JET 3 mdbs, this is not the case.
If you run:
rs.Close
rs.Open

the recordset will have the new Autonumber value. You just loose the position of the cursor in the recordset.

 

[chadrj]

Machine 2 is really adding a new record. I am trying to simulate entries of calls in a call center environment where multiple reps are entering calls but may overlap to some extent.

With the autonumber, I am capturing the newly assigned number that populates a text box and using that to requery the database before saving the second time.

I looked at your other thread, and tried to apply some things, but was not successful

Thanks, as always, for your help.

 

[CCLINT]

>I looked at your other thread, and tried to apply some things, but was not successful

I had given you two conditions, and only the second applied if you were not using an AutoNumber field.

Now I know you are using an AutoNumber with a JET 3 mdb. See my first post where I touched on this.

If you cannot change to a JET 4 mdb, and are using this in a multi-user environment, then probably your only option is to add to the db table a new field and when adding the new record, either update this new field with a random number (instead of a time stamp, as a time stamp with a JET mdb is cannot handle milli-seconds) or generate a GUID and add the value to this field, or just use the field to add a Date/Time value along with the user's name and work station ID.
The reason for these, is you need to be able to find the record just added, and do not know what Autonumber was really used. So we need some other uniquness in the record in order to query the table using this/these unique field(s) in order to find the correct record and get it's AutoNumber value.
I would really suggest adding a field to hold a GUID if you are working with Autonumber fields.
Then use the GUID as the primary key.

You also have to realize that with-out requerying the recordset and pulling in the newly added record, the ADO recordset object does not know where the new record is located in the db table because it also cannot find the primary key - this is because it DOESN'T have a primary key to work with until you requery.

 

[chadrj]

>The reason for these, is you need to be able to find the record just added, and do not know what Autonumber was really used

I do capture the auto number that was used. It populates a text box at the time a new record is added. Would I still need to create a GUID to actually find the record?

 

[CCLINT]

1. >I do capture the auto number that was used
How?
In a multi-user environment using a JET 3 mdb this isn't accurately possible unless you have some other uniqness which you are using to find the new record with (or you are locking the table just prior to adding, preventing other users from using it, and it remains locked until you requery the data unsing something like a MoveLast and then get the Number).

2.>I do capture the auto number that was used
Does the recordset also contain this new number?
You will need to requery in order for it to get it into the recordset (unless you use a BatchOptimistic lock - not always good with autonumbers)

3. >Would I still need to create a GUID to actually find the record

Yes, or something else unique which you are generating and adding to the record yourself, in order to find it again.
How else are you going to find it?
Say user one adds a record about the same time (with-in a second or even faster) that user2 does?
Which auto number are you going to use?
A Guid on the otherhand will be unique.

 

[chadrj]

1. I refresh the recordset after adding a record which helps populate the text box with the most recently added record for the individual (caller). I understand the problem that can arise if another user opens a record at about the same time, but these records are first filtered by the caller. Since the caller can only speak to one user at a time, there will not be 2 separate records created for the same caller that could overlap. For this reason I am not concered that I will pull the incorrect record as the most recent.

2. I assume # 2 is actually in regards to "It populates a text box at the time a new record is added". The correct number appears in the text box.

3. I think I answered this with number 1... Is this

I have a couple of other theories about what the problem could be...

first - Currently I have 1 executable on a shared drive that each user would run. I am going to try running the program from separate executables on its own machine.

second - I don't remember right now, but it will come to me on my way home tonight when I can't write it down.

Thank you for all your help up to this point. The GUID is a good idea that I think I will implement just to be safe (users are so unpredictable).

 

[CCLINT]

>something like a MoveLast and then get the Number
Here I meant using a query with MAX().

I was assuming that you were using a client side cursor, but the is apparently not the case.
Using a Server side cursor changes this a bit.

So, let's review:
1. Machine 1 adds a record
2. Machine 2 adds a record
3. Machine 1 edits the record just added by machine 1.


Is this correct so far?

4. When machine 1 saves the data for that edited record, the error occures, and you find out that the record was deleted, (or never really got added to the db).


-The database is a JET 3 database.
-The Table in question uses an AutoNumber field as a primary key

The additional info needed to pursue this effectively is:
1. Connection string
2. Connection's cursor location
3. Recordsource info: the SELECT sql being used
4. The recordset's CursorLocation, CursorType and LockType
5. Are the controls bound to the recordset?

 

[jbradley]

I hate to interrupt, but I've always been able to retrieve the value of an autonumber field when creating a new record in an Access 97 table.

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

    Set cn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\db1.mdb;"
    rs.Open "Table1", cn, adOpenKeyset, adLockOptimistic
    
    rs.AddNew
    lngIdKey = rs.Fields("AutoNumber")
    rs.Fields("Field1") = soemvalue
    ...
    rs.Update
    
    rs.Close
    cn.Close
    Set rs = Nothing
    Set cn = Nothing

You can then edit the record by executing an UPDATE query with lngIdKey as the WHERE criteria or opening a one-record recordset, again with lngIdKey as the WHERE criteria, changing the field values as needed and UPDATEing the recordset.

 

[CCLINT]

1. I mentioned (at the beginning, and a little later) that I was speaking of Client side cursors. This is then Not possible.

2. This, as you have shown, with using a Server side cursor, is still supposively not considered 100% reliable in a multi-user environment and has been reported as such, but I haven't yet seen myself (I have indeed mentioned what you have in other threads already)

 

[chadrj]

1. Machine 1 adds a record
2. Machine 2 adds a record
3. Machine 2 edits the record just added by machine 2.
4. Machine 1 edits the record just added by machine 1.

>When machine 1 saves the data for that edited record, the error occures, and you find out that the record was deleted, (or never really got added to the db).

Correct, exept that I know it was added initially by actually going into the db and seeing both records.


>-The database is a JET 3 database. -- True
> -The Table in question uses an AutoNumber field as a primary key -- True

Additional info 1-4

            With adoHistory
              .CursorLocation = adUseClient
              .LockType = adLockBatchOptimistic
              .ConnectionString = "Provider=Microsoft.Jet.OLEDB.3.51;" & _
              "Persist Security Info=False;Data Source=S:\Data\Human Res" & _
              "ources\Comp & HRSC\Support Center\SCIP\SCIP.mdb"
              .RecordSource = "Select * From tblIncident Where Pernr = " & strPernr & _
              " ORDER BY tblIncident.[Incident ID] DESC"
            End With

5. Yes, the controls are bound to the recordset

 

[CCLINT]

>Correct, exept that I know it was added initially by actually going into the db and seeing both records.

One last thing, what was the autonumber values of both added records?
Were they different? And is there a "Hole" in the autonumber order where the record from machine 1 was? (98, 99, 100, hole, 102)

Sounds like the autonumbers are clashing.
I'll think about it tonight - need a beer for this - JET and Autonumbers just never seemed to tickle me.

 

[chadrj]

No hole. They are consecutive.

Words can not describe how gratefull I am that you are willing to take a look at this. Enjoy your beer.

 

[jbradley]

CCLINT,

Ok, now I see what you're saying about the cursor location.

Thanks...

 

[CCLINT]

chadrj, what's the error number and message?

 

[chadrj]

There is no error detected, at run time. It just corrupts the database. But... Good news. I think I have fixed the problem. It is possible that this is something that you have advised me to do at some point, or that you assumed I should have already been doing.

Before each time I save a record (following the initial adding of a record) I set the record set to nothing and then re-set the connection string and everything. Here is the code I am using...

    adoHistory.Recordset.ActiveConnection = Nothing
        
    With adoHistory
              .CursorLocation = adUseClient
              .LockType = adLockBatchOptimistic
              .ConnectionString = "Provider=Microsoft.Jet.OLEDB.3.51;" & _
              "Persist Security Info=False;Data Source=S:\Data\Human Res" & _
              "ources\Comp & HRSC\Support Center\SCIP\SCIP.mdb"
              .RecordSource = "Select * From tblIncident Where Pernr = " & strPernr & _
              " ORDER BY tblIncident.[Incident ID] DESC"
              .Refresh
    End With

I don't know if this is normal, or right, but it seems to work.

I appreciate all you have done to help me with this, and any additional feedback would be appreciated.

 

[CCLINT]

Uff!
I just noticed you are using the ADDC!!

This means you are also using a Client cursor for the connection, and not just the recordset!

Try to get away from this and use a connection and recordset object.

For now you can also do the following until you replace the data control (Make Sure in the DataControl's Property Windowyou remove the ConnectionString and RecordSource properties):

Private conn As ADODB.Connection
Private m_rs As ADODB.Recordset

Private Sub Form_Load()
Set conn = New ADODB.Connection
Set m_rs = New ADODB.Recordset
Conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Persist Security Info=False;Data Source=S:\Data\Human Res" & _
"ources\Comp & HRSC\Support Center\SCIP\SCIP.mdb"
Conn.Open

Set m_rs.ActiveConnection = conn
m_rs.CursorLocation = adUseClient
m_rs.LockType = adLockBatchOptimistic
m_rs.Source = "Select * From tblIncident Where Pernr = " & strPernr & _
" ORDER BY tblIncident.[Incident ID] DESC"

m_rs.Open Options:= adCmdText

Set adoHistory.Recordset = m_rs
End Sub

Private Sub Form_Unload(Cancel As Integer)
If Not m_rs Is Nothing Then
If m_rs.State = adStateOpen Then m_rs.Close
End If
Set m_rs = Nothing

If Not conn Is Nothing Then
If conn.State = adStateOpen Then conn.Close
End If

Set conn = Nothing

End Sub

Now, the data control will be using the same recordset.
So, use the m_rs recordset object everywher in code instead of the Adodc.

Another thing you might want to change is using the JET 4.0 provider instead. It will work with a JET 3 db also.

What it sounds like is that the control is still holding a write lock and then the connection is somehow abruptly ended and destroyed, with-out getting first closed and then destroyed properly, as I have done in the Form unload event. The ADODC is supposed to take care of this, but, no telling what other code you are using, and you are anyways using a client side cursor on the connection instead of just the recordset.
THAT (!) will cause a mdb to corrupt fast!