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
billy bobAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

John TsioumprisSoftware & Systems EngineerCommented:
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
0
billy bobAuthor Commented:
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
0
John TsioumprisSoftware & Systems EngineerCommented:
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
0
The Five Tenets of the Most Secure Backup

Data loss can hit a business in any number of ways. In reality, companies should expect to lose data at some point. The challenge is having a plan to recover from such an event.

ste5anSenior DeveloperCommented:
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

0
billy bobAuthor Commented:
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

0
ste5anSenior DeveloperCommented:
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).
0
billy bobAuthor Commented:
ste5an, the code above has been edited using the CODE button.

... John
0
billy bobAuthor Commented:
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

0
ste5anSenior DeveloperCommented:
Ouch, yes. Didn't see this the first time:

You need to declare the parameter types only once. Copy'n'paste error I guess:

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, paramDescription, paramExtension, paramAttachment, paramDate
, paramInspectionID );

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
billy bobAuthor Commented:
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

0
ste5anSenior DeveloperCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.