Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004
    Posts
    3

    Unanswered: ADP - resync problem

    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

    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?

  2. #2
    Join Date
    Feb 2004
    Posts
    199
    what really do you want to do? do you want just update a form data via
    Set rs = Forms("Provision").Recordset
    rs.Resync

    then why you don't use a Form's "Resync Command" property? (it's usefull when need updatable form based on not updatable rowsource.)
    MDB, ADP <-> MS SQL + VBA, ADO & RDO, .NET, Oracle, Java/Jsp.

  3. #3
    Join Date
    Mar 2004
    Posts
    3
    Originally posted by Kakha
    what really do you want to do? do you want just update a form data via
    Set rs = Forms("Provision").Recordset
    rs.Resync

    then why you don't use a Form's "Resync Command" property? (it's usefull when need updatable form based on not updatable rowsource.)
    Since I am designing this database app for a multi-user environment, I want to make sure that a user will see the most recent changes made by other users to the current record, hence the
    Set rs = Forms("Provision").Recordset
    rs.Resync adAffectCurrent, adResyncAllValues

    I played around with my code some more, and when I execute the following code
    Dim rs As New ADODB.Recordset
    Set rs = Forms("Provision").Recordset
    rs.Resync adAffectCurrent, adResyncAllValues
    from a command button, it shows the changed data right away. But, if it executes from OnCurrent, it takes two visits (see my first post for description) to show the changes.

    While I have set the Form's Resync Command, it does not seem to take care of my problem. I use the following code snippet to assign a recordset, unique table and ResyncCommand to the form (Circuits is a table):

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

    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 = ?"

  4. #4
    Join Date
    Feb 2004
    Posts
    199
    ok , so your problem is to resync a row durring OnCurrent event.
    As I guess problem may be related to event firing time, when event fires a row is already current and data is retrieved to bound controls (maybe) and your resync code doesn't affect them.
    I think problem is related with OnCurrent event firing time. Can't suggest you any way out, I'll think about it.
    MDB, ADP <-> MS SQL + VBA, ADO & RDO, .NET, Oracle, Java/Jsp.

  5. #5
    Join Date
    Mar 2004
    Posts
    3
    Thanks for your help! The problem does seem to lie in the OnCurrent timing. I've been developing in Access 2000, and I tried switching to Access 2002 today - it took care of my problems.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •