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

    Exclamation Unanswered: Access 2000, SQL Server, transactions and recordsets

    I have a Access 97 database with a SQL Server backend. It uses the following code, which works. However, when the database is converted to Access 2000, the folowing code does not work.

    The code is on a bound form, using a stored Access query, which is returning am Integer, Date/Time and Boolean fields. If I open the query manually, I can add records. The error occurs on the AddNew method, with 'The action was cancelled by an associated object' message.

    The problem remains if I use Me.Recordset rather then Me.RecordsetClone. I have even tried putting the table in Access with no luck. I do not know how significant the VBA changes are from Access 97 to Access 2000, and have not really dealt iwth SQL Server backends and transactions before.

    Your quick response and help would be very much appreciated.


    Dim rst As Recordset
    Dim wsp As Workspace

    Set wsp = DBEngine.Workspaces(0)
    Set rst = Me.RecordsetClone

    wsp.BeginTrans

    rst.MoveLast

    With rst
    .AddNew

    'Add necessary data

    .update
    End With

    Me.Requery
    DoCmd.GoToRecord acActiveDataObject, , acLast

    rst.Close

    'Some SQL is ran using Docmd.RunSQL, based on data within rst

    wsp.CommitTrans

    Set rst = Nothing
    Set wsp = Nothing

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    Is it working with the clone?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Mar 2004
    Posts
    5
    Yes. This is the original code from Access 97 which runs okay. When converted to Access 2000, it compiles, but will fail when executed.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I think I would probably want to do this with a clone in any case. If it ain't broke....
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by darybrain
    The problem remains if I use Me.Recordset rather then Me.RecordsetClone.
    Ooops - I misread this

    Ok - why mix a bound form, recordset updating and running SQL statements all together?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    The default recordset for 97 is DAO, 2000 uses ADO. Check your references to ensure you're using the right library. Alternately, try prefacing your recordset call with DAO. If it doesn't work, you have a reference issue.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Mar 2004
    Posts
    5
    I am using the DAO 3.6 library and have already tried prefacing the recordset and workspace variable with DAO, i.e. DAO.Recordset. Nonetheless, it still fails on the AddNew method. Any other ideas?

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    What is your current, fully qualified code?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  9. #9
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi All

    I do not know if this is relevant but, I always thought that the RecordSetClone object was read only ?

    ie from A2k AND A97 help
    "This property is available only by using Visual Basic and is read-only in all views"

    ??

    MTB

Posting Permissions

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