Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Cornwall, England
    Posts
    393
    Thanks
    0
    Thanked 0 Times in 0 Posts

    zero records affected (access 97)

    i am trying to start using the .execute method rather than docmd.runsql as it is meant to be faster and i am having trouble.
    As this process is part of the database object i thought i would also dip my toe into the transaction waters too.
    I have come up with this code :
    With DBEngine.Workspaces(0)
    .BeginTrans
    CurrentDb.Execute strQryName, dbFailOnError + dbSeeChanges
    If MsgBox(CurrentDb.RecordsAffected & " records will be deleted." & _
    vbCrLf & "Do you want to continue ?", vbYesNo + vbQuestion, "Records to be deleted") = vbYes Then
    .CommitTrans
    Else
    .Rollback
    End If
    End With

    however, the messagebox comes up with the message that zero rows will be affected, even though running the same query through the database window gets 9 rows affected.

    why so and how do i get it fixed please ?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: zero records affected (access 97)

    The problem is that you call CurrentDb twice. Each time, you create a new instance.
    Declare a variable of type Database:

    Dim dbs As Database
    Set dbs = CurrentDb

    and then use dbs.Execute and dbs.RecordsAffected.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Cornwall, England
    Posts
    393
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: zero records affected (access 97)

    thank you, i was not aware that CurrentDB was 'created' i thought it was a reference to the Current Database. A valuable lesson learned.
    thanks again. <img src=/S/trophy.gif border=0 alt=trophy width=15 height=15>

Posting Permissions

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