none
linking BLOB to a report? RRS feed

  • Question

  • Ok let me start by saying I am very much a novice at Access, I have a background in ArcGIS... I have two databases: one with GPS locations, and the second is an image database comprised of BLOBs. The two databases are linked based on a GUID per GPS point. Whether there is one image or multiple images related to the one GPS point, the image(s) will have the same GUID as the related GPS point creating the relationship.

    Those of you who are familiar with ArcGIS know that it does not do well in dynamic form creation. In Access I populated a report creating an ODBC connection to the SQL Server where the databases are stored. I was able to populate the Report with no problem.

    Where I need someones help is, I am stuck on populating the report with the related BLOB. Does anyone have or know of a walk-through on how to load BLOBs based on a related feature?

    Wednesday, February 10, 2016 3:04 PM

Answers

  • >>>Where I need someones help is, I am stuck on populating the report with the related BLOB. Does anyone have or know of a walk-through on how to load BLOBs based on a related feature?

    According to your description, I suggest that you could refer to the following code will extract the contents of a BLOB Field into an ADODB Stream, write the Stream to a Unique File Name in a TEMP Directory under the Current Project Directory based on a Unique ID ([InvID]) and Extension ([sFileExtension]). These Files will then be dynamically loaded into an Image Control in a Report. Pay close attention to Code in the Click() Event of the single Command Button as well as the Format() Event of the Report's Detail Section. Make sure to set a Reference to the Microsoft ActiveX Data Objects X.X Object Library.

    Private Sub cmdTest_Click()
     On Error GoTo Err_cmdTest_Click
     Dim strSQL As String
     Dim rstBLOB As ADODB.Recordset
     Dim mstream As ADODB.stream
     Dim strFullPath As String
     
     'Create a Directory named TEMP if it doesn't exist under the Current Project Path
     If Dir$(CurrentProject.Path & "\TEMP\", vbDirectory) = "" Then
       MkDir CurrentProject.Path & "\TEMP\"
     End If
     
     strSQL = "SELECT tblInventoryPics.* FROM tblInventoryPics"
     
     Set rstBLOB = New ADODB.Recordset
     rstBLOB.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
     
     If rstBLOB.RecordCount = 0 Then Exit Sub
     
     Set mstream = New ADODB.stream
         mstream.Open
     
     With rstBLOB
       Do While Not .EOF
         mstream.Type = adTypeBinary
         mstream.Write rstBLOB.Fields("oPicture").Value          'Write to the Stream Object
     
         'Write BLOB to a File in the TEMP Directory under the Current Project Directory,
         'with a Unique Inventory ID (InvID) and Extension (sFileExtension)
         strFullPath = CurrentProject.Path & "\TEMP\" & ![InvID] & "." & ![sFileExtension]
         mstream.SaveToFile strFullPath, adSaveCreateOverWrite
           .MoveNext         'For each Record
       Loop
     End With
     
     rstBLOB.Close
     Set rstBLOB = Nothing
     
     'Open Report
     DoCmd.OpenReport "rptInventory", acViewPreview, , , acWindowNormal
     DoCmd.Maximize
     
     Exit_cmdTest_Click:
       Exit Sub
     
     Err_cmdTest_Click:
       MsgBox Err.Description, vbExclamation, "Error in cmdTest_Click()"
         Resume Exit_cmdTest_Click
     End Sub
    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
      Me![imgPicture].Picture = CurrentProject.Path & "\TEMP\" & Me![InvID] & "." & Me![sFileExtension]
    End Sub

    • Marked as answer by David_JunFeng Thursday, February 18, 2016 2:36 PM
    Thursday, February 11, 2016 6:06 AM