Adding an Attachment Error '3251' Operation is not Supported for this Data Type of Object.

[vonik]

I'm trying to add an attachment to a field "SDS" in my table "ChemicalLibrary" When I'm adding a completely new record to the table it works perfectly (the first half of the code below). My problem arises when I'm trying to update a record in the table and add an attachment. Below I'm trying to add the attachment to my table "ChemicalLibrary" where the primary key field "ID" =597. The SQL update queries work just fine for updating the other fields; however, I couldn't figure out how to write a SQL update query for adding the string "filepathSDS" containing the filepath for the attachment to the attachment "SDS.Filedata" field in my table so i decided to go with the recordset approach since it worked in the first half of my code for adding new records to my table. I keep getting the Error '3251' Operation is not Supported for this Data Type of Object.

The error is shown in larger Red font in the code below

Private Sub CommandSaveAndClose_Click()


Dim dbChemicalData As DAO.Database
Set dbChemicalData = CurrentDb


If IsNull(Me.TextChemicalID) Then


Dim NewChemicalID As Long
Dim NewChemical As DAO.Recordset
Dim NewSDS As DAO.Recordset2


Set NewChemical = dbChemicalData.OpenRecordset("ChemicalLibrary")


NewChemical.AddNew
NewChemical("ChemicalName").Value = Me.ComboChemicalName
NewChemical("CommonName").Value = Me.ComboCommonName
NewChemical("Supplier").Value = Me.ComboSupplier
NewChemical("CAS").Value = Me.ComboCAS
NewChemical("Fire").Value = Me.CheckFire
NewChemical("Reactive").Value = Me.CheckReactive
NewChemical("Pressure").Value = Me.CheckPressure
NewChemical("Acute").Value = Me.CheckAcute
NewChemical("Chronic").Value = Me.CheckChronic
NewChemical("Prop65").Value = Me.CheckProp65
NewChemical("Temp").Value = Me.ComboTemp
NewChemical("SPressure").Value = Me.ComboPressure
NewChemical("DOT").Value = Me.ComboDOT
NewChemical("PhysicalState").Value = Me.FrameState
NewChemical("Active").Value = Me.FrameActive
NewChemicalID = NewChemical("ID").Value


    If IsEmpty(filepathSDS) Then
    Else
   
        Set NewSDS = NewChemical.Fields("SDS").Value


        NewSDS.AddNew
        NewSDS.Fields("filedata").LoadFromFile filepathSDS
        NewSDS.Update
        
        MsgBox "You have added a new Chemical & SDS " & Me.ComboCommonName.Value & " to the library."


        NewSDS.Close
        Set NewSDS = Nothing
        
    End If
    
NewChemical.Update


NewChemical.Close
dbChemicalData.Close


Set NewChemical = Nothing
Set dbChemicalData = Nothing


MsgBox "You have added a new Chemical " & Me.ComboCommonName.Value & " to the library."


Else


Dim ChemicalID As Long
ChemicalID = Me.TextChemicalID


'dbChemicalData.Execute ("UPDATE ChemicalLibrary SET ChemicalLibrary.ChemicalName = " & Chr$(34) & Forms!Addchemical.ComboChemicalName & Chr$(34) & " WHERE ChemicalLibrary.ID=" & ChemicalID)
'dbChemicalData.Execute ("UPDATE ChemicalLibrary SET ChemicalLibrary.CommonName = " & Chr$(34) & Forms!Addchemical.ComboCommonName & Chr$(34) & " WHERE ChemicalLibrary.ID=" & ChemicalID)
'dbChemicalData.Execute ("UPDATE ChemicalLibrary SET ChemicalLibrary.Supplier = " & Chr$(34) & Forms!Addchemical.ComboSupplier & Chr$(34) & " WHERE ChemicalLibrary.ID=" & ChemicalID)
'dbChemicalData.Execute ("UPDATE ChemicalLibrary SET ChemicalLibrary.CAS = " & Chr$(34) & Forms!Addchemical.ComboCAS & Chr$(34) & " WHERE ChemicalLibrary.ID=" & ChemicalID)
dbChemicalData.Execute ("UPDATE ChemicalLibrary SET ChemicalLibrary.Fire = " & Chr$(34) & Forms!Addchemical.CheckFire & Chr$(34) & " WHERE ChemicalLibrary.ID=" & ChemicalID)
dbChemicalData.Execute ("UPDATE ChemicalLibrary SET ChemicalLibrary.Reactive = " & Chr$(34) & Forms!Addchemical.CheckReactive & Chr$(34) & " WHERE ChemicalLibrary.ID=" & ChemicalID)
dbChemicalData.Execute ("UPDATE ChemicalLibrary SET ChemicalLibrary.Pressure = " & Chr$(34) & Forms!Addchemical.CheckPressure & Chr$(34) & " WHERE ChemicalLibrary.ID=" & ChemicalID)
dbChemicalData.Execute ("UPDATE ChemicalLibrary SET ChemicalLibrary.Acute = " & Chr$(34) & Forms!Addchemical.CheckAcute & Chr$(34) & " WHERE ChemicalLibrary.ID=" & ChemicalID)
dbChemicalData.Execute ("UPDATE ChemicalLibrary SET ChemicalLibrary.Chronic = " & Chr$(34) & Forms!Addchemical.CheckChronic & Chr$(34) & " WHERE ChemicalLibrary.ID=" & ChemicalID)
dbChemicalData.Execute ("UPDATE ChemicalLibrary SET ChemicalLibrary.Prop65 = " & Chr$(34) & Forms!Addchemical.CheckProp65 & Chr$(34) & " WHERE ChemicalLibrary.ID=" & ChemicalID)
dbChemicalData.Execute ("UPDATE ChemicalLibrary SET ChemicalLibrary.Temp = " & Chr$(34) & Forms!Addchemical.ComboTemp & Chr$(34) & " WHERE ChemicalLibrary.ID=" & ChemicalID)
dbChemicalData.Execute ("UPDATE ChemicalLibrary SET ChemicalLibrary.SPressure = " & Chr$(34) & Forms!Addchemical.ComboPressure & Chr$(34) & " WHERE ChemicalLibrary.ID=" & ChemicalID)
dbChemicalData.Execute ("UPDATE ChemicalLibrary SET ChemicalLibrary.DOT = " & Chr$(34) & Forms!Addchemical.ComboDOT & Chr$(34) & " WHERE ChemicalLibrary.ID=" & ChemicalID)
dbChemicalData.Execute ("UPDATE ChemicalLibrary SET ChemicalLibrary.PhysicalState = " & Chr$(34) & Forms!Addchemical.FrameState & Chr$(34) & " WHERE ChemicalLibrary.ID=" & ChemicalID)
dbChemicalData.Execute ("UPDATE ChemicalLibrary SET ChemicalLibrary.Active = " & Chr$(34) & Forms!Addchemical.FrameActive & Chr$(34) & " WHERE ChemicalLibrary.ID=" & ChemicalID)


    If IsNull(DLookup("SDS.filedata", "ChemicalLibrary", "ID= " & ChemicalID)) Then
        If IsEmpty(filepathSDS) Then
        Else
            'dbChemicalData.Execute ("UPDATE ChemicalLibrary SET ChemicalLibrary!SDS.filedata = " & filepathSDS & " WHERE ChemicalLibrary.ID=" & ChemicalID)
            Dim UpdateChemical As DAO.Recordset
            Dim UpdateSDS As DAO.Recordset2
        
            Set UpdateChemical = dbChemicalData.OpenRecordset("Select * From ChemicalLibrary Where ID = 597")


            UpdateChemical.Edit
            Set UpdateSDS = UpdateChemical.Fields("SDS").Value


            UpdateSDS.AddNew
[COLOR=#b22222][SIZE=3]            UpdateSDS.Fields("filedata").LoadFromFile = filepathSDS[/SIZE][/COLOR]
            UpdateSDS.Update
            
            UpdateChemical.Update




            Set UpdateSDS = Nothing
            Set UpdateChemical = Nothing
            Set dbChemicalData = Nothing
            UpdateSDS.Close
            UpdateChemical.Close
            dbChemicalData.Close
            
        End If
        
    End If


MsgBox "Chemical " & Me.ComboCommonName.Value & "has been updated"


End If


'DoCmd.Close acForm, "AddChemical"


End Sub

Public Function SelectFile() As String


 Dim f As Object
 
 Set f = Application.FileDialog(3)
 
 f.AllowMultiSelect = False
 
 If f.Show = True Then
 SelectFile = f.selecteditems(1)
 filepathSDS = SelectFile
 End If


End Function

Any help with be greatly appreciated.

Thanks,

Vonik

 

[xenou]

Set UpdateSDS = UpdateChemical.Fields("SDS").Value

Out of curiousity, is this a multi-valued field, then?