asked on

adding blobs (long binary data) and other fields to a table

Hello. I am adding file attachments to a table using the code below (and I know the consequences but need to do what is required) that is on a submit button. As you can see, I am adding different fields to the table. With what I have below only adds the Attachment field and the PropertyInspectionID field. The other 3 do not get entered into the table. I do a step thru and the info shows. The sql statement has the order of the fields in the table. I rarely work with arrays and I have a feeling that is where the problem is.

Can another set of eyes look this over and see where or what I'm missing?

Thank you.

... John

Dim cn As ADODB.Connection
Dim rs As New ADODB.Recordset
Dim filepath As String: filepath = Me.txtPath
Dim sqlAttachment As String

sqlAttachment = "SELECT AttachmentTitle, AttachmentDescription, AttachmentExtension, Attachment, PropertyInspectionID " & _
"FROM tblPropertyInspectionAttachment " & _
"WHERE PropertyInspectionID = " & Me.txtPropertyInspectionID

Set cn = CurrentProject.Connection

'arrays are zero based
Dim fieldList(4) As Variant 'create array to hold list of fields to insert values into
Dim valueList(4) As Variant 'create array to hold list of values to insert values into

fieldList(0) = "AttachmentTitle"
fieldList(1) = "AttachmentDescription"
fieldList(2) = "AttachmentExtension"
fieldList(3) = "Attachment"
fieldList(4) = "PropertyInspectionID"

valueList(0) = Me.txtAttachmentTitle
valueList(1) = Me.txtAttachmentDescription
valueList(2) = Me.txtAttachmentExtension
valueList(3) = filepath
valueList(4) = Me.txtPropertyInspectionID

'Create stream to hold data
Dim mStream As New ADODB.Stream
mStream.Type = adTypeBinary
mStream.Open
mStream.LoadFromFile (filepath)

'Open recordset with just the fields we need
rs.Open sqlAttachment, cn, adOpenForwardOnly, adLockOptimistic
'add new record with our values,
rs.AddNew fieldList(4), valueList(4)
'then write our stream
rs.Fields("Attachment").Value = mStream.Read
'finally save
rs.Save

'tidy up
mStream.Close
rs.Close
cn.Close

Set mStream = Nothing
Set rs = Nothing
Set cn = Nothing

John Tsioumpris

Are you sure is rs.Save and not rs.Update ?
From what i see .Save is used to save the record to an external file

billy bob

ASKER

Mr Tsioumpris, thank you for the response. I get the same result. Only the file and PropertyInspectionID are the only items going into the table. The other 3 fields do not.

Other thoughts or is there something more I should try?

... John

John Tsioumpris

This the code i use on my form

Private Sub cmdInserBlob_Click()
Dim fileName As String
Set mystream = New adodb.Stream
mystream.Type = adTypeBinary
mystream.Open
fileName = SelectFile
mystream.LoadFromFile fileName
Me.Image = mystream.Read

Open in new window

Image is a Bound Object Frame
And this is my code with DAO.Recordset

 fileStream.TYPE = adTypeBinary
        fileStream.Open
        fileStream.LoadFromFile CStr(.SelectedItems(1))

        rsImages.AddNew
        rsImages.Fields("MAINImage") = fileStream.Read

Open in new window

Also have you checked that actually read the file...
Just after mStream.Read put a watch and check if the stream object has content

ste5an

The problem is the combination of the array's usage and the direct access. I'm using parameterized queries for this:

Public Function InsertBlob(APath As String, AFileName As String) As Boolean

    Dim Stream As ADODB.Stream
    Dim Command As ADODB.Command
    
    Dim varFileBinary
    
    Set Stream = New ADODB.Stream
    Stream.Type = adTypeBinary
    Stream.Open
    Stream.LoadFromFile APath & AFileName
    varFileBinary = Stream.Read
    Stream.Close
    Set Stream = Nothing
    
    Set Command = New ADODB.Command
    With Command
        .CommandText = _
          "PARAMETERS paramPath Text(255), paramFileName Text(255), paramData LongBinary; " & _
          "INSERT INTO [DestinationTable] (Path, FileName, Data) " & _
          "VALUES (paramPath, paramFileName, paramData);"
        .CommandType = adCmdText
        .Parameters.Append .CreateParameter("paramPath", adVarChar, adParamInput, 255, APath)
        .Parameters.Append .CreateParameter("paramFileName", adVarChar, adParamInput, 255, AFileName)
        .Parameters.Append .CreateParameter("paramData", adLongVarBinary, adParamInput, 2147483647, varFileBinary)
        Set .ActiveConnection = CurrentProject.Connection
        .Execute , , adExecuteNoRecords
    End With
    
    InsertBLOB = True
    
    Set Stream = Nothing
    Set Command = Nothing

End Function

Open in new window

billy bob

ASKER

ste5an, thank you for the response and a possible method of getting the info loaded. I took what you provided and connected it to the button and needed to modify it. Now I get an error "Syntax error in PARAMETER clause." The error is at the .Execute line. I had the ', , adExecuteNoRecords' in there but was getting error 'Variable not define'. Any thoughts on either of these?

Thanks.

... John

Private Sub btnSubmit_Click()
    Dim Stream As ADODB.Stream
    Dim Command As ADODB.Command
    Dim varFileBinary
    Dim filepath As String: filepath = Me.txtPath

    Set Stream = New ADODB.Stream
    Stream.Type = adTypeBinary
    Stream.Open
    Stream.LoadFromFile filepath
    varFileBinary = Stream.Read
    Stream.Close
    Set Stream = Nothing

    Set Command = New ADODB.Command

    With Command
        .CommandText = _
          "PARAMETERS paramTitle Text(255), paramDescription Text, paramExtension Text(5), paramAttachment LongBinary, paramDate Date/Time, paramInspectionID Number; " & _
          "INSERT INTO [tblPropertyInspectionAttachment] (AttachmentTitle, AttachmentDescription, AttachmentExtension, Attachment, DateAttached) " & _
          "VALUES (paramTitle Text(255), paramDescription Text, paramExtension Text(5), paramAttachment LongBinary, paramDate Date/Time, paramInspectionID Number);"
        .CommandType = adCmdText
        .Parameters.Append .CreateParameter("paramTitle", adVarChar, adParamInput, 255, Me.txtAttachmentTitle)
        .Parameters.Append .CreateParameter("paramDescription", adVarChar, adParamInput, 8000, Me.txtAttachmentDescription)
        .Parameters.Append .CreateParameter("paramExtension", adVarChar, adParamInput, 5, Me.txtAttachmentExtension)
        .Parameters.Append .CreateParameter("paramAttachment", adLongVarBinary, adParamInput, 2147483647, varFileBinary)
        .Parameters.Append .CreateParameter("paramDate", adDate, adParamInput, Date, Date)
        .Parameters.Append .CreateParameter("paramInspectionID", adInteger, adParamInput, , Me.txtPropertyInspectionID)
        Set .ActiveConnection = CurrentProject.Connection
        .Execute
    End With

    Set Stream = Nothing
    Set Command = Nothing
End Sub

Open in new window

ste5an

Create a query and add your parameters using the wizard. The parameter data types are:

- Text(255) for ShortText including its maximum length.
- DateTime for Dates with/out Time portion.
- LongText for Memos.
- Long for long integers.

E.g. something like this:

With Command
  .CommandText = _
    "PARAMETERS paramTitle Text(255), paramDescription LongText, paramExtension Text(5), paramAttachment LongBinary, paramDate DateTime, paramInspectionID Long; " & _
    "INSERT INTO [tblPropertyInspectionAttachment] (AttachmentTitle, AttachmentDescription, AttachmentExtension, Attachment, DateAttached) " & _
    "VALUES (paramTitle Text(255), paramDescription Text, paramExtension Text(5), paramAttachment LongBinary, paramDate Date/Time, paramInspectionID Number);"
  .CommandType = adCmdText
  .Parameters.Append .CreateParameter("paramTitle", adVarChar, adParamInput, 255, Me.txtAttachmentTitle)
  .Parameters.Append .CreateParameter("paramDescription", adVarChar, adParamInput, 8000, Me.txtAttachmentDescription)
  .Parameters.Append .CreateParameter("paramExtension", adVarChar, adParamInput, 5, Me.txtAttachmentExtension)
  .Parameters.Append .CreateParameter("paramAttachment", adLongVarBinary, adParamInput, 2147483647, varFileBinary)
  .Parameters.Append .CreateParameter("paramDate", adDate, adParamInput, Date, Date)
  .Parameters.Append .CreateParameter("paramInspectionID", adLong, adParamInput, , Me.txtPropertyInspectionID)

Open in new window

btw, limiting an extension to only 5 chars is too restrictive. There are longer ones. E.g. .config in the .NET world.

p.s. use the CODE button to embed code in posts (please, edit yours).

billy bob

ASKER

ste5an, the code above has been edited using the CODE button.

... John

billy bob

ASKER

Hello ste5an. I get the error "Syntax error (missing operator) in query expression 'paramTitle Text(255)'. It occurs at the .Execute line. Any further thoughts? Below is what I have so far.

Thanks.

... John

Private Sub btnSubmit_Click()
    Dim Stream As ADODB.Stream
    Dim Command As ADODB.Command
    Dim varFileBinary
    Dim filepath As String: filepath = Me.txtPath

    Set Stream = New ADODB.Stream
    Stream.Type = adTypeBinary
    Stream.Open
    Stream.LoadFromFile filepath
    varFileBinary = Stream.Read
    Stream.Close
    Set Stream = Nothing

    Set Command = New ADODB.Command

    With Command
        .CommandText = _
          "PARAMETERS paramTitle Text(255), paramDescription LongText, paramExtension Text(50), paramAttachment LongBinary, paramDate DateTime, paramInspectionID Long; " & _
          "INSERT INTO [tblPropertyInspectionAttachment] (AttachmentTitle, AttachmentDescription, AttachmentExtension, Attachment, DateAttached) " & _
          "VALUES (paramTitle Text(255), paramDescription LongText, paramExtension Text(50), paramAttachment LongBinary, paramDate DateTime, paramInspectionID Long);"
        .CommandType = adCmdText
        .Parameters.Append .CreateParameter("paramTitle", adVarChar, adParamInput, 255, Me.txtAttachmentTitle)
        .Parameters.Append .CreateParameter("paramDescription", adVarChar, adParamInput, 8000, Me.txtAttachmentDescription)
        .Parameters.Append .CreateParameter("paramExtension", adVarChar, adParamInput, 50, Me.txtAttachmentExtension)
        .Parameters.Append .CreateParameter("paramAttachment", adLongVarBinary, adParamInput, 2147483647, varFileBinary)
        .Parameters.Append .CreateParameter("paramDate", adDate, adParamInput, Date, Date)
        .Parameters.Append .CreateParameter("paramInspectionID", adInteger, adParamInput, 15, Me.txtPropertyInspectionID)
        Set .ActiveConnection = CurrentProject.Connection
        .Execute
    End With

    Set Stream = Nothing
    Set Command = Nothing
End Sub

Open in new window

ASKER CERTIFIED SOLUTION

ste5an

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

billy bob

ASKER

ste5an, thank you for the help on solving the vast mysteries of adding blobs to a table. I was able to spend the morning going over the code and solving the errors I would get. I will provide the completed code below so others can view it and use if they need to. But, the big item was when I started going thru using it and adding many attachments of different types and sizes. I spent several hours loading to see how the db worked and performed. Once I started to see it work, I then began watching the size of the backend increase in size. I got to slightly over 500mgs. And I only added about 251 attachments. I felt sorry for the backend and how bloated it got. It could have used a few TUMS. I ran some calculation numbers and took this info to the Proj Mgr and Business Analyst, who then went (and me) to the head person's office with the info. Gave her the numbers. She went to a prior project and ran some numbers and said there was no way we could store the attachments this way. She made 2 comments that made me smile:
1) the backend would reach 2gigs in about a year or so;
2) she apologized and said she should have listened to me early on when I was talking about the storage issue and options.

After I said 'thanks' I indicated we need to act quickly to determine which way the users really want to go (continue as is or store path only in table) so I can begin working on the method. So, she instructed the other 2 in the room to have an answer to me by end of week.

Overall, I had a good day.

Thanks again.

... John

Things to remember for code below:
This code is connected to a button to submit attachment to table.
The 3 lines at the end I added to assist for when I was adding attachments.

    Dim objStream As ADODB.Stream
    Dim objCmd As ADODB.Command
    Dim varFileBinary
    Dim filepath As String: filepath = Me.txtPath

    Set objStream = CreateObject("ADODB.Stream")
    objStream.Type = 1  'is adTypeBinary
    objStream.Open
    objStream.LoadFromFile filepath
    varFileBinary = objStream.Read
    objStream.Close
    Set objStream = Nothing

    Set objCmd = CreateObject("ADODB.Command")

    With objCmd
        .CommandText = "PARAMETERS paramTitle Text (255), paramDescription LongText, paramExtension Text (255), paramAttachment LongBinary, paramDate DateTime, paramInspectionID Short; " & _
                        "INSERT INTO [tblPropertyInspectionAttachment] (AttachmentTitle, AttachmentDescription, AttachmentExtension, Attachment, DateAdded, PropertyInspectionID) " & _
                        "VALUES (paramTitle, paramDescription, paramExtension, paramAttachment, paramDate, paramInspectionID);"
        .CommandType = adCmdText
        .Parameters.Append .CreateParameter("paramTitle", adVarChar, adParamInput, 255, Me.txtAttachmentTitle)
        .Parameters.Append .CreateParameter("paramDescription", adVarChar, adParamInput, 8000, Me.txtAttachmentDescription)
        .Parameters.Append .CreateParameter("paramExtension", adVarChar, adParamInput, 50, Me.txtAttachmentExtension)
        .Parameters.Append .CreateParameter("paramAttachment", adLongVarBinary, adParamInput, 2147483647, varFileBinary)
        .Parameters.Append .CreateParameter("paramDate", adDate, adParamInput, Date, Date)
        .Parameters.Append .CreateParameter("paramInspectionID", adInteger, adParamInput, 255, Me.txtPropertyInspectionID)
        Set .ActiveConnection = CurrentProject.Connection
        .Execute , , 128
    End With

    Set objStream = Nothing
    Set objCmd = Nothing
    
    'puts new autonumber in field
    Me.txtPropertyInspectionAttachmentID = DMax("PropertyInspectionAttachmentID", "tblPropertyInspectionAttachment")

    MsgBox "ALL DONE"
    
    Me.lstAttachment.Requery

Open in new window

ste5an

Just a further comment:

paramInspectionID being Integer instead of Long should have a good reason. Integer can only store numbers from -32k to +32k. These numbers can overflow pretty quick, when they are generated as auto-increment.

My rule of thumb: Use always Long. As long as there is no good, documented reason for an Integer.