CODEWITHSUNDEEP
vbams-access-2010
Terry S
Terry S

Reputation: 23

Delete data in attachment field

I have a local table with an attachment field. I have tried deleting with vba and manually and the previous attachments keep coming back. In vba I have tried it with a recordset and with SQL. The SQL 

Let strSQL = "DELETE tblSpecsPics.PicID,tblSpecsPics.SpecID,tblSpecsPics.SpecDetailID,tblSpecsPics.PicNum,tblSpecsPics.Pic.FileData,tblSpecsPics.Pic.FileName,tblSpecsPics.Pic.FileType,tblSpecsPics.ImageDesc FROM tblSpecsPics;"

db.Execute strSQL, dbSeeChanges

The recordset

Set rst = db.OpenRecordset("SELECT * FROM tblSpecsPics WHERE Not (tblSpecsPics.Pic.FileData) Is Null;", dbOpenDynaset, dbSeeChanges)

If Not rst.BOF And Not rst.EOF Then
rst.MoveFirst
Do Until rst.EOF
    rst.Edit
    Set rsv = rst.Fields(4).Value
    rsv.Delete
    rst.Update
    rst.MoveNext
Loop

End If

When I add new records the old attachments reappear without fail.

Upvotes: 0

Views: 3777

Answers (3)

MBOEGH
MBOEGH

Reputation: 1

Option Compare Database
Option Explicit
Public Sub RemoveAttachments()
On Error GoTo SP_FjernNNDok_Err
Dim rst As DAO.Recordset
Dim rstAttachment As DAO.Recordset2
Dim SQL As String
Dim RecordsetCount As Integer
Dim rstAttCount As Integer
Dim i As Integer
Dim RecordNumber As Integer
Dim AttachmentNumber As Integer
RecordNumber = 1
AttachmentNumber = 1
SQL = "Your Query as string”
Set rst = CurrentDb.OpenRecordset(SQL)
rstMedlemsCount = 0
rstAttCount = 0
rstMedlemsCount = rst.RecordCount
If RecordsetCount = 0 Then
MsgBox "There are no records with attched files"
Else
rst.MoveFirst
    Set rstAttachment = rst.Fields(2).Value ’count columnnumber in the query starting with 0 from the left side
    rstAttCount = rstAttachment.RecordCount
    While Not rstAttachment.EOF
        For i = 1 To rstAttCount
            rstAttachment.Delete
            rstAttachment.MoveNext
            AttachmentNumber = AttachmentNumber + 1
            i = i + 1
            RecordNumber = RecordNumber + 1
        Next i
    Wend
End If
    MsgBox rstMedlemsCount & " Contained attached files which were deleted."
Set rst = Nothing
Set rstAttachment = Nothing
RemoveAttachments_Exit:
    Exit Sub
RemoveAttachments_Err:
    MsgBox Error$
    Resume RemoveAttachments_Exit:
    Exit Sub
End Sub

Upvotes: 0

Hunter Kingsley
Hunter Kingsley

Reputation: 61

I found the update method unnecessary. Also, for clarity, I start at the end of the attachments and move back as it was less than obvious if the delete/next would skip every other record. Private Sub TestThis_Click()

On Error GoTo errHandler Dim rs1Count As Integer Dim rs1 As DAO.Recordset Dim rs2 As DAO.Recordset2 Dim strSql As String Dim db As Database rs1Count = 0 Set db = DBEngine(0)(0) strSql = "SELECT T_Vendor_Complaint_A.[F_VCN_A], T_Vendor_Complaint_A.[F_Attachments_A] FROM T_Vendor_Complaint_A WHERE T_Vendor_Complaint_A.[F_VCN_A] = 271" Set rs1 = db.OpenRecordset(strSql, dbOpenDynaset) rs1.MoveLast MsgBox ("rs1.RecordCount:" & rs1.RecordCount & " in T_Vendor_Complaint_A VCN:" & rs1![F_VCN_A]) Set rs2 = rs1!F_Attachments_A.value If rs2.RecordCount > 0 Then rs2.MoveLast Do Until rs2.RecordCount = 0 rs2.Delete rs2.MovePrevious Loop End If errExit: Exit Sub

errHandler: MsgBox Err.Number & ": " & Err.Description Exit Sub

End Sub

Upvotes: 0

Parfait
Parfait

Reputation: 107687

Because MS Access Attachment Types are actually linked tables of meta data (filename, filedata, fileflag, filetype, etc.), you need to delete these underlying linked records. So consider nesting another recordset loop on the attachment field value from main table:

strSQL = "SELECT * FROM tblSpecsPics WHERE Not (tblSpecsPics.Pic.FileData) Is Null;"

Set rst = db.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)

If Not rst.BOF And Not rst.EOF Then
    rst.MoveFirst
End If

Do Until rst.EOF

    Set childrst = rst.Fields(4).Value
    Do Until childrst.EOF
       childrst.Delete
       childrst.MoveNext
    Loop

    rst.MoveNext
Loop

Upvotes: 4

Related Questions