Storing BLOBs

[ordnance1]

I am using some code developed by Alan Warren and modified by Armund Dezii. Using MS Access as a front end to an SQL Server backend, it allows me to import graphic files and store them in SQL Server as BLOBs. I can pass along all the code if desired.

This works fine but I would also like to import PDFs and DOCX files. When I modify the line below by adding *.pdf I get an error, see attached screenshot.

.Filters.Add "Images", "*.jpg; *.jpeg; *.tiff; *.gif; *.bmp; *.png; *.pdf; *.ico"

[NeoPa]

Hi Ordnance1. Welcome to Bytes.com.

I would check more thoroughly for you, but the code you've included is either missing the start of the line or it's part of a With statement that you haven't included. That makes checking what it claims to support very difficult.

Having said that, I would have thought that the error message explains the situation fairly clearly. That may be because I'm possibly more used to intrerpreting them than most, but it seems pretty clear that whatever object you are using doesn't support a PDF format file. It isn't the line of code that is causing the problem (Remember Before Posting (VBA or SQL) Code.) but trying to execute that line with the file selected. Had it been the line itself then you'd not have got as far as selecting the file so its name would not have appeared in the error message.

NB. It seems a little-known fact that you can copy an error message pop-up box to the clipboard and it will paste as decently formatted text. This is always a good idea as it saves you the trouble of posting pictures and takes up less space. You've done well to post the picture as the info there made the whole situation much clearer. It's just that there is an easier way - that now you know about :-)

[NeoPa]

I suppose I should have added that it *MAY* be possible to get around this in a limited way by renaming the file (The Name statement in VBA.) before selecting it. That said, I can't tell without checking (which I'm not able to do without knowing the object you're working with and your version of Access) whether it determines the type of file by the extension or the contents. If it's the former then that should work, but if the latter then probably not.

I'll leave you to play around with that if you're interested.

[twinnyfo]

There is also the option of, rather than importing the files as BLOBs, to simply copy the files to a networked folder, and rather than saving the file itself, save the location of the file in a text field. This allows you to acces the file at any time (or modify it, if need be) simply by referring to its location.

Just an option--but I know that does not directly respond to your question.

[ADezii]

1. The *.pdf Files should never be incorporated into the Graphic Images Filter, but should be independent of it. In the sample Code below, there are three Filters, namely: Graphic Files, Word Documents, and PDFs (Portable Document Format).
   

   'Must 1st set a Reference to the Microsoft Office XX.X Object Library
   Dim dlgOpen As FileDialog
   Dim strFile As String
    
   Set dlgOpen = Application.FileDialog(msoFileDialogFilePicker)
    
   With dlgOpen
     .AllowMultiSelect = False
     .ButtonName = "Import Graphic"
     .Title = "Browse for Graphic Files"
     .InitialView = msoFileDialogViewLargeIcons
     .InitialFileName = "C:\"
     .Filters.Clear
       .Filters.Add "Images", "*.jpg; *.jpeg; *.bmp; *.ico"
       .Filters.Add "Word Documents", "*.doc; *.docx"
       .Filters.Add "Portable Document Format", "*.pdf"
         If .Show <> -1 Then Exit Sub      'Nothing selected
           strFile = .SelectedItems(1)     'File Selected
   End With

2. IMHO, *.pdfs should, and must, be handled differently from the Graphic Files. This is easily accomplished by checking the Extension of a File(s) once selected and taking the appropriate action.
3. My initial thought is that the contents of the *.pdf Files can be placed into a Byte() Array and written directly into the OLE Object Fields. In this manner, they will co-exist among the BLOBs.
4. I am not 100% sure on this approach, but if you are interested, and not in a rush, I'll see what I can come up with.

[ordnance1]

Thanks for taking the time to reply. I altered my code to reflect your changes but I still get the same error that I was getting in my original post. I did notice that as soon as I selected a PDF file the Open button changed to Import Graphic.

I am creating this db for my brother who is on the road when he uses it, and connects through a dsnless connection. I have to admit I am not an IT pro but a retired sailor and retired public transit dispatcher who does this to keep my mind active. So please excuse any ignorance on my part. I greatly appropriate people like you and the many others on these forums that so freely give of your time and expertise.

Private Sub btn_LoadImage_Single_Click()
Dim strFullPath As String
    'Sets the form to a new record. Prevents over writing the current record
        DoCmd.GoToRecord , , acNewRec
 
            'Sets the 2 textboxes to blank so that nothing has to be entered
                Me.txt_BlobTitle = ""
                Me.txt_BlobDescription = ""
 
    'Must 1st set a Reference to the Microsoft Office XX.X Object Library
        Dim dlgOpen As FileDialog
        Dim strFile As String
        Set dlgOpen = Application.FileDialog(msoFileDialogFilePicker)
        With dlgOpen
          .AllowMultiSelect = False
          .ButtonName = "Import Graphic"
          .Title = "Browse for Graphic Files"
          .InitialView = msoFileDialogViewLargeIcons
          .InitialFileName = "C:\"
          .Filters.Clear
            .Filters.Add "Images", "*.jpg; *.jpeg; *.bmp; *.ico"
            .Filters.Add "Word Documents", "*.doc; *.docx"
            .Filters.Add "Portable Document Format", "*.pdf"
              If .Show <> -1 Then Exit Sub      'Nothing selected
                strFile = .SelectedItems(1)     'File Selected
    End With
 
        With Me
          .ImageX.Picture = strFile
        End With
 
        Set dlgOpen = Nothing
            On Error GoTo Err_btn_LoadImage_Single
        Dim strPath As String
 
        If Nz(Me.ImageX.Picture, "") = "" Then
          Exit Sub
        End If
 
        If IsNull(Me![txt_BlobTitle]) Then
          MsgBox "You must enter a value for Item Name before the Record can be saved", _
                  vbExclamation, "Missing Item Name"
           Me![txt_BlobTitle].SetFocus
            Exit Sub
        ElseIf IsNull(Me![txt_BlobDescription]) Then
          MsgBox "You must enter a value for Item Description before the Record can be saved", _
                  vbExclamation, "Missing Item Description"
           Me![txt_BlobDescription].SetFocus
            Exit Sub
        End If
 
        DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
 
        strPath = Me.ImageX.Picture
 
        ImgCusBLOB.Picture = getBLOBFromFile(Me.BlobInventory_ID, strPath)
 
Exit_btn_LoadImage_Single:
 
 
        TempVars!Test = Me.ImageX.Picture
 
 
        Kill TempVars!Test
 
          Exit Sub
 
Err_btn_LoadImage_Single:
          MsgBox Err.Description, vbExclamation, "btn_LoadImage_Single_Click()"
            Resume Exit_btn_LoadImage_Single
        End Sub

[ADezii]

PDFs can actually be placed in an OLE Object Field, as a Binary Stream (BLOB), in the same exact manner as the Graphic Images are. The only differences are in the implementations. Obviously *.pdfs cannot be placed into Image Controls as the Graphic Files are and there are always the issues of Enabling/Disabling Controls for the Graphic Images, Editing the Graphic Images, etc. I am finishing up a Demo that will illustrate how *.pdfs can be dynamically loaded into an OLE Object Field paralleling your Code. You can then incorporate it into the existing Code or execute it independently. Be patient, and I'll Upload the Demo as soon as it's complete.

[ADezii]

1. Here is the Demo that I was referring to. It consists of a Table (tblDemo) with 6 Records. These Records contain *.pdfs stored as BLOBs in an OLE Object Field along with a Primary Key,File Path, and Extension.
2. I won't go into a detailed explanation of the Coding but simply state the general concept.
3. Click the Put Into BLOB Field Command Button to open the File Dialog Box filtered for certain Graphic Files as well as *.pdfs which will be the Default Filter. Select a *.pdf, then the Load File Command Button. The *.pdf will now be stored in the OLE Object Field as a BLOB.
4. Click the Load BLOB Into File Command Button to extract a specific *.pdf File based on the Primary Key. You will then be given an Option to Open that File.
5. If you select a Graphic File, you will simply be given a message stating that the Demo does not support it at this time.
6. Have fun, and should you have any questions, feel free to ask.

P.S. - The System will not allow me to Attach this File even though it is in Zip Format and below the Maximum Size allowed. Will try again at some point. Got it now, but only a single File in tblDemo.

[ordnance1]

I have spent about the last 14 hours working with your sample and merging it with my original code and can now import graphics and pdf's. Just wondering my graphics display in my ole control but not my pdf's. Should I be able to get them to display?

After I get the pdf's to display (if that is possible I will move on to doc and docx files

Either way I am burned out for one day. I do want to thank you for your help.

[ADezii]

1. Previously, I hinted on the problems that you may be having storing Graphic Images, Word Documents, and PDFs as Binary Data in an OLE Object Field.
2. I created another 'No Frills' Demo that should avoid this problem. You can click on the Put File Into BLOB Field Command Button to Open a File Dialog that is filtered for Graphic Images (*.jpg, *.jpeg, *.bmp, *.ico), Word Documents (*.doc, *.docx), and Portable Document Format (*.pdf). You can add addition Files and File Types at any time.
3. Once you select a File, it is written as a Stream of Binary Data into a BLOB ([BLOB]) Field along with basic information.
4. To retrieve and optionally View these BLOBs, Select the File Type from the Option Group and the Combo Box will list all Files of that Type.
5. Select a File from the Combo Box and the Data will be written as the Original File Name to the TEMP Folder with the appropriate Extension. If the TEMP Folder does not exist, it will be created.
6. You will then be given the option to View the extracted BLOB. Should you elect to View the File, the Server Application registered for the File Extension in the Registry will Open it. This is accomplished by use of the ShellExecute() API Function.
7. For the sake of brevity and simplicity, no provision is made for Error Trapping and removal of the extracted Files.

[NeoPa]

Hi guys.

Although this thread is not going the normal way threads are expected to go, so will not be great as far as a searchable question, I can see that you're both involved and making good progress. Great attitude all round so I'm very happy to give my seal of approval and allow this to continue just as it is.

I can see a lot of time and effort has gone into it already and lots of progress is being made by the OP. Both parties seem happy to be involved so the best of luck to you both :-)

-Ade (Admin).

[ADezii]

Thanks NeoPa, for letting us proceed. I do understand that this Thread is a little unorthodox, but integrating multiple File Types and storing them as Binary Objects is a little tricky, at least for me. As you already have stated, the OP (ordnance1) has invested a lot of time and effort into this Project and I am happy to assist in any way I can. Thanks again!

[ordnance1]

Thanks to both of you

[ADezii]

I had the opportunity to look at your DB and it appears to be coming along well. I see that you had no Code for the Bulk Import Operation, so I took the liberty of creating it for you. When performing a Bulk Import, a Folder Dialog Box will Open, and once a Folder is selected you will be prompted to Import <X> number of Files from <Folder Name>. If you choose Yes, then all Files in that Folder will be imported and a verification Dialog will appear. The Code is listed below as well as the helper Function to determine the number of Files to be Imported. The Code does NOT check the File Extensions in that Folder, that will be up to you.

Dim strFile As String
Dim intCtr As Integer
Dim rstBLOB As ADODB.Recordset
Dim mstream As ADODB.Stream
Dim strFolder As String
Dim dlgOpen As Office.FileDialog
Dim intResponse As Integer
Dim intCtr2 As Integer
Dim lngNumOfFiles As Long
 
Set dlgOpen = Application.FileDialog(msoFileDialogFolderPicker)
 
With dlgOpen
  .AllowMultiSelect = False
  .ButtonName = "Select Import Folder"
  .Title = "Browse for Folders"
  .InitialView = msoFileDialogViewLargeIcons
  .InitialFileName = "C:\"
    If .Show <> -1 Then Exit Sub
      If .SelectedItems(1) = "C:\" Then
        strFolder = .SelectedItems(1)
      Else
        strFolder = .SelectedItems(1) & "\"
      End If
End With
 
lngNumOfFiles = fRetNumOfFiles(strFolder)
 
intResponse = MsgBox("Import " & CStr(lngNumOfFiles) & " Files from " & strFolder & _
                     "?", vbQuestion + vbYesNo, "Import Prompt")
If intResponse = vbNo Then
  DoCmd.Hourglass False
    Exit Sub
End If
 
Set rstBLOB = New ADODB.Recordset
rstBLOB.Open "tblDemo", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
 
intCtr = 1
strFile = Dir(strFolder & "*", vbNormal)
 
Set mstream = New ADODB.Stream
 
DoCmd.Hourglass True
 
Do While strFile <> ""
  With mstream
    .Type = adTypeBinary
    .Open
    .LoadFromFile strFolder & strFile
  End With
  With rstBLOB
    .AddNew
      .Fields("sFileName") = strFolder & strFile
      .Fields("sFileExtension") = Mid$(strFile, InStrRev(strFile, "."))
      .Fields("BLOB").Value = mstream.Read
    .Update
    End With
    mstream.Close: intCtr = intCtr + 1
    strFile = Dir
Loop
 
DoCmd.Hourglass False
 
rstBLOB.Close
Set rstBLOB = Nothing
 
MsgBox CStr(intCtr - 1) & " Files were Imported from " & strFolder & ".", _
       vbInformation, "Import Status"
Me.Requery

Public Function fRetNumOfFiles(strFldr As String) As Long
Dim strFile As String
Dim lngCtr As Long
 
If Right$(strFldr, 1) <> "\" Then strFldr = strFldr & "\"
 
strFile = Dir$(strFldr, vbNormal)
 
If strFile = "" Then fRetNumOfFiles = 0: Exit Function
 
Do While strFile <> ""
  lngCtr = lngCtr + 1
    strFile = Dir()
Loop
 
fRetNumOfFiles = lngCtr
End Function

[ordnance1]

Thank you for thew code. At the moment I am working to get the single file load up and running and am about complete.

Not sure if I mentioned that I am displaying images in an image control and PDF's in a webbrowser control. This is working well with the exception of a very odd problem.

When I go to add the PDF to the webbrowser control (Lines 7 through 10), the only way it will work is if I include the Msgbox. If I remove the Msgbox line nothing happens. The webbrowser control is not populated and I get no error message.

The code compiles .BlobInventory returns the correct value. So not sure what it could be. Do not know if you or any others in the forum have experienced this problem.

Private Sub Form_Current()
    With Me
      If .NewRecord Then
        ![ImageX].Picture = ""
        ![txtProperties] = ""
      End If
 
        If [BlobCategory_ID] <> 1 Then
        MsgBox "hi 1"
            Forms![frm_Navigation]![NavigationSubform].Form![DocumentViewer].Object.Navigate putBLOBInFile(.BlobInventory_ID)
        End If
 
        If [BlobCategory_ID] = 1 Then
            .ImageDisplay.Picture = putBLOBInFile(.BlobInventory_ID)
        End If
    End With
End Sub

[ADezii]

I'm a little confused on a couple of points:

1. Where is the Control [.BlobInventory_ID]?
   

   Forms![frm_Navigation]![NavigationSubform].Form![DocumentViewer].Object.Navigate putBLOBInFile(.BlobInventory_ID)

2. putBlobOnFile() is a Sub-Routine and doesn't return a String, it accepts a String Argument. You can't assign a Return Value from it to the Picture Property of the Image Control.
   

   .ImageDisplay.Picture = putBLOBInFile(.BlobInventory_ID)

   Public Sub PutFileIntoBLOB(strFileName As String)

[ordnance1]

BlobCategory_ID represents which type of file is to be imported. 1 = Image and 2 = PDF. Since the bulk import does not allow you to pick the file type I created to folders BlobUpload\Image and BlobUpload\PDF. Then code looks at BlobCatefory_ID to determine which folder to load.

In the code below if BlobCategory_ID = 1 then then it loads the image into the Image Control. If it is not 1 it loads the PDF into the Webbrowser Control.

I have come up with a work around for the problem I mentioned. I created a form set its width to .0069 Making it basically invisible set its Timer Interval to 1 and added On Timer event to close the form. I added code to the On Load event of my form in question to open the blank for as acDialog. This seems to work.

[NeoPa]

Hi Ordnance.

I'm guessing, from your reply, that you didn't quite get the significance of ADezii's questions. Certainly your responses don't answer the questions, nor indicate that you perceive what ADezii is trying to draw your attention to.

1. What you plan to use [BlobCategory_ID] for isn't the question. Where is it located on a Form? I suspect it hasn't been created which means your reference to it will not work.
2. This was about drawing your attention to the difference between Function procedures and Subroutine procedures. Procedures are sets of code that you can call. A Subroutine will run code. A Function will also run code, but on top of that will return a value. Notice the difference in the following example code :
   

   Public Sub MySub()
   ...
   End Sub

   Public Function MyFunction() As String
   ...
   MyFunction = "Return value"
   ...
   End Function

Your code was trying to use the value returned from a subroutine. This cannot work as subroutines, by definition, return no values.

As for your work-around. You may prefer working with a more normal-sized Form but hiding it so it doesn't interfere with the display. It makes it easier to work with.

As I say, I'm happy to let you two run with this without interfering, but I thought jumping in here quickly may prove helpful. Good luck with your project :-)

[ordnance1]

My apologies BlobCategory_ID produced by a combobox on the a form

[NeoPa]

I'm not sure apologies are necessary here. I wasn't taking you to task. Simply trying to guide and assist. Point you through the forest, as it were. It's easy to get lost ;-)

[ADezii]

I think that you are losing focus a little. You appear to be attempting to write a non-graphic File (*.pdf in this case) to a BLOB Field stored as a Binary Stream of Data. The exact opposite needs to occur in the Current() Event of your Form, namely the Binary Data needs to be extracted from the BLOB Field and placed into a Temporary File. Next, this File is loaded into a Web Browser Control. I didn't have much time, but I modified the Code in the Current() Event of my DB and the outcome was promising, namely the *.pdfs were displayed in the Web Browser. There were a couple of minor difficulties, but the overall approach was successful.

Private Sub Form_Current()
With Me
  If .NewRecord Then
    ![ImageX].Picture = ""
    ![txtProperties] = ""
  End If
 
  If [BlobCategory_ID] <> 1 Then
    Dim strSQL As String
    Dim rstBLOB As ADODB.Recordset
    Dim mstream As ADODB.Stream
    Dim strFullPath As String
    Dim strFolder As String
    Dim fso As FileSystemObject
 
    strFolder = "C:\TEMP\"
    If Dir$(strFolder, vbDirectory) = "" Then MkDir (strFolder)
 
    Set fso = New Scripting.FileSystemObject
 
    strSQL = "SELECT * FROM tblDemo WHERE [ID] = " & Me![txt_ID]
 
    Set rstBLOB = New ADODB.Recordset
    rstBLOB.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
 
    Set mstream = New ADODB.Stream
    With mstream
      .Type = adTypeBinary
      .Open
      .Write rstBLOB.Fields("BLOB").Value
    End With
 
    strFullPath = rstBLOB![sFileName]
    mstream.SaveToFile "C:\TEMP\" & strFullPath, adSaveCreateOverWrite
 
    Forms![frmTest].DocumentDisplay.Visible = True
    Forms![frmTest].DocumentDisplay.Navigate "C:\TEMP\" & strFullPath
 
    rstBLOB.Close
    Set rstBLOB = Nothing
  End If
End With
End Sub

[ADezii]

but I thought jumping in here quickly may prove helpful.

Always welcome and always helpful!

IMHO, Extracting Binary Data from an OLE Object Field, saving it to a File, then loading that File (*.pdf, *.doc, *.docs) into a Web Browser is very problematic to say the least. I arrived at another solution which you may/may not like. The attached Demo contains three Records, a *.bmp, *.pdf, and *.doc. As you navigate thru them Code in the Current() Event of the Form will load preset Graphic Images into the Image Control. As far as the Word Documents and PDFs, an Open File Command Button will appear. Clicking on the Command Button will then Open the File via the FollowHyperlink Method of the Application. I strongly feel that going the Web Browser route will lead to nothing but problems. Give the Demo a shot and see what you think.

[ordnance1]

Thank you I will take a look.

[ordnance1]

Mission complete. Thanks for all the help. This never would have happened without you.

[ADezii]

You are quite welcome, good luck with your Project!

@NeoPa:
Thanks for allowing us to continue and come to a successful conclusion.

[NeoPa]

Always a pleasure to observe you in action my friend :-)

There are a lot less valuable things you could be doing with your spare time.