Data update problem with multiple users --> resync?

[flute72080]

I am very confused with how ADPs handle recordset updates. I have tried poured over books and newsgroups, and I still cannot get data to update correctly. Any assistance would be greatly appreciated.

In order to allow a form, "Provision", to obtain the most recent updates made by other users, I added the following code to its OnCurrent event:
Dim rs As New ADODB.Recordset
Set rs = Forms("Provision").Recordset
rs.Resync adAffectCurrent, adResyncAllValues

While this technically solved my problem, it created another in that the resync took too long to run. So, I decided that I only needed to update the current record and replaced the last line of code with
rs.Resync adAffectCurrent, adResyncAllValues

This is the strange part: If user1 goes to a record that user2 has updated, he does not see the update and he gets a write conflict if he tries to update it. But, if user1 leaves that record and then comes back to it a second time, the changes are there and the record can be edited. This happens whether the RecordSource is a view or a stored procedure, or if I assign a RecordSet programatically.

Any thoughts?

 

[flute72080]

I made a mistake... my first block of code should be

Dim rs As New ADODB.Recordset
Set rs = Forms("Provision").Recordset
rs.Resync

 

[cmmrfrds]

You don't show the cursor type in the example. The cursor type and whether the OLEDB Provider supports it will determine if you see the immediate updates without a requery. I believe it must be a dynamic cursor, but I am not sure about the SQL Server Provider supporting dynamic cursor.

 

[flute72080]

I use the following code to assign the recordset to the form:

Dim cnnLocal As New ADODB.Connection, rstCurr As New ADODB.Recordset, strQuery As String
Dim intTest As Integer

Set cnnLocal = CurrentProject.Connection
strQuery = "Select * from dbo.Circuits"
rstCurr.CursorLocation = adUseClient
rstCurr.Open strQuery, cnnLocal, adOpenDynamic, adLockOptimistic
Set Forms("Provision").Recordset = rstCurr
Forms("Provision").UniqueTable = "Circuits"
Forms("Provision").ResyncCommand = "Select * from dbo.Circuits WHERE dbo.Circuits.RecID = ?"

However, I just checked to see what kind of cursor was actually created - and it was static.

If I do need to use a requery instead of a resync, would you have any suggestions how I would do so? When a user navigates to a record I want to automatically requery the recordset, but I can't put Form.Requery in OnCurrent because it keeps requerying itself.

Thanks for your help!

 

[cmmrfrds]

What version of Access are you using? Access 2000 is more limited than the later versions.

With a cursor location of client side the ADO provider will force the cursor type to static. First, if this is a newer version of Access, then try cursor location of server side.

rstCurr.CursorLocation = adUseServer

With a cursor location of server then the dynamic cursor may work - give it a try.

 

[cmmrfrds]

Okay, I did a little checking in my ADO book by Rob Macdonald called "Serious ADO". He did a test with the Native SQL Server provider, and since you are using currentproject.connection it should be the native provider. There are other OLEDB providers for sql server, such as, the ODBC one and the MSDatashape providers.

What you are doing should be okay with the server side cursor as long as the Select Statement does not contain an order by clause on a varchar field.

I am not sure about the placement of the resync command, it may need to be in the afterupdate event of the Form.

 

[flute72080]

Thanks for your help! I'm using Access 2000 right now. Given your mention of its limitations, I tried my code in Access 2002 and it works fine without any changes. I am going to investigate upgrading to Access 2002 - I think it's probably my best bet.

 

[cmmrfrds]

Check this thread705-788005

In Access 2000, if you set up another connection using the MsDataShape provider that may work for you.

If I had the opportunity I would upgrade to 2002 myself.