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"

[IMGNOTHUMB]https://bytes.com/attachments/attachment/10096d157193170 3/untitled-picture.png[/IMGNOTHUMB]

[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