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