Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Feb 2001
    Location
    Vancouver, BC, Br. Columbia, Canada
    Posts
    96
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ADO - Delete records (Access 2000)

    Dear Loungers,
    I have tried (I think) every combination of locktypes and cursortypes, but still Access will not allow me to delete records. It should be so simple. I'm going against a plain vanilla jet data database.

    Sub test()
    Dim rstPurchaseDetail As ADODB.Recordset
    Dim strsql As String
    strsql = "SELECT tblPurchaseHeader.*, tblPurchaseDetail.* "
    strsql = strsql & "FROM tblPurchaseHeader INNER JOIN tblPurchaseDetail ON "
    strsql = strsql & "tblPurchaseHeader.PurchaseID = tblPurchaseDetail.PurchaseID "

    Set rstPurchaseDetail = New ADODB.Recordset
    rstPurchaseDetail.Open Source:=strsql, ActiveConnection:=CurrentProject.Connection, _
    CursorType:=adOpenKeyset, LockType:=adLockOptimistic

    rstPurchaseDetail.Delete adAffectAll
    End Sub

    Hope you can help,

    Gwenda <img src=/S/bash.gif border=0 alt=bash width=35 height=39>

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: ADO - Delete records (Access 2000)

    I'm not sure what you're trying to do here. You appear to be selecting all the records in two tables with an inner join and then trying to delete them. Why no just use DELETE in the SQL in the first place? There doesn't appear to be any other purpose to your sub, so why do it the hard way? Create the SQL for a DELETE query, assign it as the commandtext of a command object and then execute it. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

    By the way, I've had trouble with adAffectAll. Its effects are sometimes bizaare. Try adAffectCurrent instead. It isn't intuitive, but it seems to work.
    Charlotte

  3. #3
    Star Lounger
    Join Date
    Feb 2001
    Location
    Vancouver, BC, Br. Columbia, Canada
    Posts
    96
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ADO - Delete records (Access 2000)

    Charlotte-oh-blessed-guru-divine ... I can learn from you in 2 seconds what it takes hours to figure out by books. Thank you ... I will do as you suggest and let you know how it works out.

  4. #4
    Star Lounger
    Join Date
    Feb 2001
    Location
    Vancouver, BC, Br. Columbia, Canada
    Posts
    96
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ADO - Delete records (Access 2000)

    Thank you Charlotte.

    Code below works like a beauty and now I'll integrate it into my program. BTW, it won't work if the SQL specifies a join between the header and detail records, but in the Relationships window they are joined with "Cascading Delete" turned ON, so if I delete the header records .. eh voila! the detail is gone too.

    Sub test3()
    Dim cmdDelete As ADODB.Command
    Dim strSQL As String
    Dim conn As ADODB.Connection

    strSQL = "DELETE tblPurchaseHeader.* FROM tblPurchaseHeader"
    Set conn = CurrentProject.Connection
    Set cmdDelete = New ADODB.Command
    Set cmdDelete.ActiveConnection = conn
    cmdDelete.CommandText = strSQL
    cmdDelete.Execute

    End Sub

Posting Permissions

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