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

V

vonik

New Member
Joined
Oct 3, 2013
Messages
16
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

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
[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

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

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Sort by date Sort by votes
You have a selectfile function shown, but I don't see it anywhere in your click event code. As far as I can see, the FilePathSDS variable is empty.
 
Upvote 0
Thank you for your response Xenou,

The FilePathSDS variable isn't empty, it is called by another subroutine that is called by an on-click event of another command button and FilePathSDS is declared as "Global filepathSDS As Variant"

In the first part of the code when adding a completely new record to the table its works perfectly.
This part of the code here:

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


The error occurs when updating an existing record that doesn't have an attachment yet, as shown by the second part of the code here:

I'm trying to add an attachment to the record where ID=597; do i need a different approach in the second half of the code since I'm editing an existing recordset?

Code: 
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
           [SIZE=3][COLOR=#b22222] UpdateSDS.Fields("filedata").LoadFromFile = filepathSDS[/COLOR][/SIZE]
            UpdateSDS.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
 
Upvote 0
Exactly what type of variable is UpdateSDS supposed to be? You have it dimmed as a DAO.Recordset2. But I don't see where you've opened a recordset with it. Given that UpdateSDS is a Recordset2 object, this line looks strange:
Code: 
Set UpdateSDS = UpdateChemical.Fields("SDS").Value

I've never worked with file attachments so I don't know for sure if that's legit or not.
 
Upvote 0
On the line of code that gives you the error you are treating LoadFromFile as a property instead of a method. Does removing that equal sign fix the problem?
 
Upvote 0
Thanks Xenou,

UpdateSDS is Dimmed as a DOA.Recordset2 8 lines up from where the error occurs.

Code: 
            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
[SIZE=2][COLOR=#b22222]            UpdateSDS.Fields("filedata").LoadFromFile = filepathSDS[/COLOR][/SIZE]
            UpdateSDS.Update

Thanks,

Vonik
 
Upvote 0
AngelJ said:
On the line of code that gives you the error you are treating LoadFromFile as a property instead of a method. Does removing that equal sign fix the problem?
Click to expand...

Thanks AngelJ,

That worked, I feel like such an idiot for missing that I swear i looked over that code a million times!

Do you know of any way i can add the attachment without have to use that recordset such as something that would look like this?

Code: 
dbChemicalData.Execute ("UPDATE ChemicalLibrary SET ChemicalLibrary!SDS.filedata = " & filepathSDS & " WHERE ChemicalLibrary.ID=" & ChemicalID)

Thanks,

Vonik
 
Upvote 0
vonik said:
Thanks AngelJ,

That worked, I feel like such an idiot for missing that I swear i looked over that code a million times!
Click to expand...
No problem, it happens. It's an easy one to miss because of how VBA requires parentheses sometimes and requires that they're removed other times. It stands out to me because I work in other languages where they are always required.

vonik said:
Do you know of any way i can add the attachment without have to use that recordset such as something that would look like this?

Code: 
dbChemicalData.Execute ("UPDATE ChemicalLibrary SET ChemicalLibrary!SDS.filedata = " & filepathSDS & " WHERE ChemicalLibrary.ID=" & ChemicalID)

Thanks,

Vonik
Click to expand...
I don't know, but I'd be surprised if there's a way to do it in plain SQL. I also have no experience working with attachments, as I prefer not to store attachments in the database. Instead, I store the path of the file in a text field on the database.

I did a quick search but I mostly found more people advising against storing files in attachment fields.
 
Upvote 0
Yeah thats what I had read on the Internet too and had a hard time finding examples of code used for storing files in the database. I'm very new to VBA, programming and databases and still trying to learn and get my bearings.

AngelJ, thank you for your help and looking for a SQL solution too.

xenou, thank you too for looking into my problematic code.


Thanks Again,

Vonik
 
Upvote 0
vonik said:
I'm very new to VBA, programming and databases and still trying to learn and get my bearings.
Click to expand...
In that case I'd say you're doing quite well! :biggrin:

Anyway, for now it looks like you're all set with the LoadFromFile method. But, if you want to switch to storing the files off the database (or if you hit that 2GB limit) then I'm sure we'll be able to help with that too.
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question
Access 2013 Audit Trail VBA Error "Operation is not supported for this type of object"
2
Replies
17
Views
4K
geospatial
G

Forum statistics

Threads
1,214,954
Messages
6,122,462
Members
449,085
Latest member
ExcelError

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top