Adding an Attachment Error '3251' Operation is not Supported for this Data Type of Ob (1 Viewer)

Vonik

Registered User.
Local time
Today, 00:26
Joined
Mar 17, 2014
Messages
17
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 occurs near the end of the subroutine shown here.
Code:
            UpdateSDS.AddNew
            UpdateSDS.Fields("filedata").LoadFromFile = filepathSDS           '<--------This line here
            UpdateSDS.Update


Code:
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
            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

Code:
Option Compare Database
Global filepathSDS As Variant


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 will be greatly appreciated.

Thanks,

Vonik
 
Last edited:

Vonik

Registered User.
Local time
Today, 00:26
Joined
Mar 17, 2014
Messages
17
I fixed it, i dont know why i added an equals sign but once it took it out it worked

Code:
            UpdateSDS.AddNew
            UpdateSDS.Fields("filedata").LoadFromFile filepathSDS   '<--------This line here
            UpdateSDS.Update
 

Users who are viewing this thread

Top Bottom