Link to home
Start Free TrialLog in
Avatar of billy bob
billy bob

asked on

putting record attachment directly into db with vba and not using attachment datatype or control

Hello.  I have been presented with a requirement that has the attachment being added to the database but not by using the Attachment datatype or the Attachment control.  This makes one attachment per line rather than the attachments all in one field.  Some examples I was shown has anywhere from 1 to 61 attachments in the one attachment field.  In the past I have added the path of the attachment using the code below.  Now, the attachment itself is to be added, not the pathway.  In the past I have provided the plus and minus for this and even did the pathway only at one time.  Needless to say, i lost weight after that from being chewed on by others.  So, I do what they want.  So please spare me the lecture.  The attachment can be of any type of document type (pdf, word, excel, powerpoint, picture, etc).  The user will need to be able to add, open, delete the attachment.  The delete part is questionable for now since they are still hashing that one over.  To verify, would the datatype for the field be 'OLE Object'?  The code below should still be viable to use since it opens the dialog to select the file.  I am considering a continuous form with the buttons at the end of each row.  Would this be the way to go?  There needs to be a description field added since some attachment file names are numbers.  In the past when I did the pathway only, I used a regular unbound form with listbox and unbound fields with an insert statement behind button.

So, I need some guidance on this and if anyone has any or knows of sites with samples/examples of this that I can review and study, thanks.

... John

    Dim f As Object
    Dim strFile As String
    Dim strFolder As String
    Dim varItem As Variant

        Set f = Application.FileDialog(3)
        f.AllowMultiSelect = True
        If f.Show Then
            For Each varItem In f.SelectedItems
                strFile = Dir(varItem)
                strFolder = Left(varItem, Len(varItem) - Len(strFile))
                Me.txtReferenceDocument = varItem
            Next
        End If
        Set f = Nothing
Avatar of John Tsioumpris
John Tsioumpris
Flag of Greece image

You can use OLE Object as DataType for your field and ADODB.Stream to read/write to this
Avatar of billy bob
billy bob

ASKER

Mr Tsioumpris, thank you for your response.  Thank you for verifying the OLE datatype.  Now, for the Stream.  That is new for me.  I started looking around for it and found info about Blobs.?  Not sure if that is the same thing or part of STREAM.  Do you have a sample or example you are able to share using STREAM?  

Thank you again for response.

... John
This question needs an answer!
Become an EE member today
7 DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.