Page 1 of 2 12 LastLast
Results 1 to 15 of 29
  1. #1
    aspen is offline Competent Performer
    Windows Vista Access 2010 64bit
    Join Date
    Jun 2011
    Posts
    127

    Remove only All the attachments from Table Attacment field

    Hi guys I need a little help. I have a form build on a table with an attachment field. I have saved all the attachments to a folder and I have the path recorded in the table. So now I want to remove all the attachments in the table to decrease size while keeping the text fields. Can some one please help. I have tried calling this function. But I am getting errors. ambiguous name detected " the function name" . Here is the function. Thanks
    http://msdn.microsoft.com/en-us/libr...ffice.15).aspx
    Code:
    Function RemoveAttachment(strRemoveFile As String, Optional strFilter As String) As Long
        Dim dbs As DAO.Database
        Dim rst As DAO.Recordset2
        Dim rsA As DAO.Recordset2
        Dim fld As DAO.Field2
        
        'Get the database
        Set dbs = CurrentDb
        
        'Open the recordset. If the strFilter is supplied, add it to the WHERE
        'clause for the recordset. Otherwise, any files matching strFileName
        'will be deleted
        If Len(strFilter) > 0 Then
            Set rst = dbs.OpenRecordset("SELECT * FROM tblAttachments WHERE " & strFilter)
        Else
            Set rst = dbs.OpenRecordset("tblAttachments")
        End If
        
        'Get the Attachment field
        Set fld = rst("Attachments")
        
        'Navigate through the recordset
        Do While Not rst.EOF
        
            'Get the recordset for the Attachments field
            Set rsA = fld.Value
            
            'Walk the attachments and look for the file name to remove
            Do While Not rsA.EOF
                If rsA("FileName") Like strRemoveFile Then
                    rsA.Delete
                    
                    'Increment the number of files removed
                    RemoveAttachment = RemoveAttachment + 1
                End If
                rsA.MoveNext
            Loop
                    
            'Cleanup the Attachments recordset
            rsA.Close
            Set rsA = Nothing
            
            'Next record
            rst.MoveNext
        Loop
        
        rst.Close
        dbs.Close
        Set fld = Nothing
        Set rst = Nothing
        Set dbs = Nothing
    End Function
    Just for more information


    Here is the code I use to remove just one attachment. Its behind a form button.
    Code:
    Private Sub Command1130_Click()
    On Error GoTo err_proc
    Dim strSQL As String
    Dim intPic As Integer
        DoCmd.RunCommand acCmdSaveRecord
        Me.Refresh        'New line
        Me.Files.Requery        'New line
        intPic = Me.Files.CurrentAttachment
    '  Instantiate the parent recordset.
        Dim db As DAO.Database
        Dim qdf As DAO.QueryDef, rst1 As DAO.Recordset, rst2 As DAO.Recordset
        strSQL = "SELECT Files FROM Table1 WHERE ID=" & Me.ID
        
        Set db = CurrentDb
        Set qdf = db.CreateQueryDef("", strSQL)
        Set rst1 = qdf.OpenRecordset
        If rst1.EOF = True Then GoTo exit_proc
        rst1.MoveFirst
        rst1.Edit
       ' Instantiate the child recordset.
       
        Set rst2 = rst1.Fields("Files").Value
        rst2.OpenRecordset
        If rst2.EOF = True Then GoTo exit_proc
        rst2.MoveFirst
        If intPic > 0 Then rst2.Move intPic
        rst2.Delete
       ' Update the parent record
        rst1.Update
       
        Me.Files.Requery
        DoCmd.RunCommand acCmdSaveRecord
    exit_proc:
    On Error Resume Next
        rst2.Close
        rst1.Close
        qdf.Close
        Set db = Nothing
        Exit Sub
    err_proc:
        MsgBox Err.Description
        Resume exit_proc
        '===============================
        DoCmd.RunCommand acCmdSaveRecord
        Me.Refresh        'New line
        Me.Files.Requery        'New line
        '=======================================
    End Sub

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Why don't you just delete the Attachment field then run Compact & Repair?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    aspen is offline Competent Performer
    Windows Vista Access 2010 64bit
    Join Date
    Jun 2011
    Posts
    127
    Quote Originally Posted by June7 View Post
    Why don't you just delete the Attachment field then run Compact & Repair?
    Hi june. I want to keep the field but remove all the attachments in the field so that I can add new records with attachments later so I can get their path in the table. Can you please tell me how to remove the attachments keeping the field and other records. The above code removes one record. I want to repeat it by code for all the records.
    Thank you very much

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Which line of code do you think is the one that deletes the attachment?

  5. #5
    aspen is offline Competent Performer
    Windows Vista Access 2010 64bit
    Join Date
    Jun 2011
    Posts
    127
    Hi itsme. Its the second code above. It works fine for removing one attachment If there is one. So maybe we should use NEXT or Loop which I am not familiar with. please help
    Thanks

  6. #6
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Yah, I think I know. I am asking you if you can identify the single line of code that actually does the deleting. I am willing to walk you through it but I am not willing to write the code for you.

    I am not trying to force you to do anything you are uncomfortable with so.....

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Loop through the parent recordset as demonstrated in the first code.

    Step debug. Which line generates error message?

    Not sure what 'ambiguous name' indicates. A procedure cannot have same name as the module it is in.



    You can acquire the path of an external file without having to install it into attachment field.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    aspen is offline Competent Performer
    Windows Vista Access 2010 64bit
    Join Date
    Jun 2011
    Posts
    127
    Hi ItsMe And June7 Its Obviously Its this line " rst2.Delete". Glad with the way you are eager to help access lovers. Thanks and I appreciate this we will go ahead.

  9. #9
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    June and I posted at the same time. I agree that the easisest is not to use the attachment field. Managing files is easier using directories.

    My thought is that it may be easiest to bring some of the technique from the first quote in Post#1 into your code you are currently using.

    In order to loop the the Attachment field, you will need to understand your If Then statement associated to rs2.

    Since you have
    If rst2.EOF = True Then GoTo exit_proc
    rst2.MoveFirst

    You do not need
    If intPic > 0 Then rst2.Move intPic

    Go ahead and comment out the line with intpic and see if it runs. You have a kludge here and if the business rule is "Delete ALL attachments" you do not need to identify the names of the attachments.

    After the revised code successfully deletes the first attachment, look at the delete line and consider where you would place some code to loop through all of the attachments in that record.

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Sorry, don't know why that errors. However, don't see how your code will delete all attachments for all records - there is no looping. You have example of looping structure in the original code. Did you try the original code again? Just figure out the 'ambiguous name' error - procedure and module cannot have same name.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    aspen is offline Competent Performer
    Windows Vista Access 2010 64bit
    Join Date
    Jun 2011
    Posts
    127
    I have Changed the Table name and attachment fields Name to my table's and saved it as module1 and tried to call like this Call RemoveAttachment(Me.Files). Me.Files is the field In my table and form where the File name is stored. eg: filename.Mp4. And I get the error argument not optional. As ItsMe advised I have changed It several ways. here iam posting the code as of now. It removes the first record. and I get an error "object no longer set"

  12. #12
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    ??????????

  13. #13
    aspen is offline Competent Performer
    Windows Vista Access 2010 64bit
    Join Date
    Jun 2011
    Posts
    127
    Code:
    On Error GoTo err_proc
    Dim strSQL As String
    Dim intPic As Integer
        DoCmd.RunCommand acCmdSaveRecord
        Me.Refresh        'New line
        Me.Files.Requery        'New line
        intPic = Me.Files.CurrentAttachment
    '  Instantiate the parent recordset.
        Dim db As DAO.Database
        Dim qdf As DAO.QueryDef, rst1 As DAO.Recordset, rst2 As DAO.Recordset
        strSQL = "SELECT Files FROM Table1 WHERE ID=" & Me.ID
        
        Set db = CurrentDb
        Set qdf = db.CreateQueryDef("", strSQL)
        Set rst1 = qdf.OpenRecordset
        If rst1.EOF = True Then GoTo exit_proc
        rst1.MoveFirst
        rst1.Edit
       ' Instantiate the child recordset.
       
        Set rst2 = rst1.Fields("Files").Value
        '=========================
        Do While Not rst2.EOF
        '===============================
        rst2.OpenRecordset
        
        If rst2.EOF = True Then GoTo exit_proc
        
        'rst2.MoveFirst
        'If intPic > 0 Then rst2.Move intPic
        rst2.Delete
        'Update the parent record
        '====================
       rst2.MoveNext
            Loop
       '======================
        rst1.Update
       
       
        Me.Files.Requery
        DoCmd.RunCommand acCmdSaveRecord
    exit_proc:
    On Error Resume Next
        rst2.Close
        rst1.Close
        qdf.Close
        Set db = Nothing
        Exit Sub
    err_proc:
        MsgBox Err.Description
        Resume exit_proc
        '===============================
        DoCmd.RunCommand acCmdSaveRecord
        Me.Refresh        'New line
        Me.Files.Requery        'New line
        '=======================================
    Sorry I forgot to post the code.

  14. #14
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    So I would have expected some code that looks like this and then when you test it there is not any error. That would allow you to analyze code that works, then decide where a loop would be appropriate.

    Code:
        If rst2.EOF = True Then GoTo exit_proc
        
        rst2.MoveFirst
        rst2.Delete
       ' Update the parent record
        rst1.Update
    Can you get this code to work? It is a good idea to have working code before adding to it, especially a loop.

  15. #15
    aspen is offline Competent Performer
    Windows Vista Access 2010 64bit
    Join Date
    Jun 2011
    Posts
    127
    Code:
    On Error GoTo err_proc
    Dim strSQL As String
    Dim intPic As Integer
        DoCmd.RunCommand acCmdSaveRecord
        Me.Refresh        'New line
        Me.Files.Requery        'New line
        intPic = Me.Files.CurrentAttachment
    '  Instantiate the parent recordset.
        Dim db As DAO.Database
        Dim qdf As DAO.QueryDef, rst1 As DAO.Recordset, rst2 As DAO.Recordset
        strSQL = "SELECT Files FROM Table1 WHERE ID=" & Me.ID
        
        Set db = CurrentDb
        Set qdf = db.CreateQueryDef("", strSQL)
        Set rst1 = qdf.OpenRecordset
        If rst1.EOF = True Then GoTo exit_proc
        rst2.MoveFirst
        rst2.Edit
       ' Instantiate the child recordset.
       
        Set rst2 = rst1.Fields("Files").Value
       
        rst2.OpenRecordset
         '=========================
        Do While Not rst2.EOF
        '===============================
        If rst2.EOF = True Then GoTo exit_proc
        
        rst2.MoveFirst
        'If intPic > 0 Then rst2.Move intPic
        
        rst2.Delete
        'Update the parent record
        
        rst1.Update
        '====================
       rst1.MoveNext
            Loop
       '======================
      
        Me.Files.Requery
        DoCmd.RunCommand acCmdSaveRecord
    exit_proc:
    On Error Resume Next
        rst2.Close
        rst1.Close
        qdf.Close
        Set db = Nothing
        Exit Sub
    err_proc:
        MsgBox Err.Description
        Resume exit_proc
        '===============================
        DoCmd.RunCommand acCmdSaveRecord
        Me.Refresh        'New line
        Me.Files.Requery        'New line
        '=======================================
    I have changed it like this. Please look carefully. I get the error object variable or with block not set

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 15
    Last Post: 01-28-2014, 12:20 PM
  2. Replies: 5
    Last Post: 10-14-2013, 11:53 AM
  3. Replies: 6
    Last Post: 04-23-2012, 10:54 AM
  4. Replies: 7
    Last Post: 04-15-2011, 08:46 AM
  5. Remove contents from each field
    By nancyszn in forum Access
    Replies: 7
    Last Post: 08-06-2009, 03:41 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums