How to open PDF stored in sql server [image] field using ms access 2007

Question

I'm converting a database from access to a sql backend access front end. The database has embedded pdf documents which end up getting stored as [image] data by SQL server's data import tools.

My problem is that I want the users to be able to open the pdf file by clicking the pdf icon in a report created in access.

Can this be done with VBA or is there an easier way? I'm at a total loss on how to make this happen.

Thanks for the answer!

I edited the BlobToFile function to strip out the ole header since adobe couldn't read the file (evince could and so could mac preview)

I was able to do what I wanted like this:

 Private Sub PDFDocument_Click()
     Call BlobToFile("C:\db\MyPDFFile.pdf", Me.PDFDocument)
     If Dir("C:\db\MyPDFFile.pdf") <> "" Then
        FollowHyperlink ("C:\db\MyPDFFile.pdf")
     End If
 End Sub

 'Function:  BlobToFile - Extracts the data in a binary field to a disk file.
 'Parameter: strFile - Full path and filename of the destination file.
 'Parameter: Field - The field containing the blob.
 'Return:    The length of the data extracted.
  Public Function BlobToFile(strFile As String, ByRef Field As Object) As Long
     On Error GoTo BlobToFileError

     Dim nFileNum As Integer
     Dim abytData() As Byte
     Dim abytParsedData() As Byte
     Dim copyOn As Boolean
     Dim copyIndex As Long

     BlobToFile = 0
     nFileNum = FreeFile
     copyOn = False
     copyIndex = 0

     Open strFile For Binary Access Write As nFileNum
     abytData = Field
     ReDim abytParsedData(UBound(abytData))

     For i = LBound(abytData) To UBound(abytData) - 1
         If copyOn = False Then
             If Chr(abytData(i)) = "%" And Chr(abytData(i + 1)) = "P" And Chr(abytData(i + 2)) = "D" And Chr(abytData(i + 3)) = "F" Then
                 copyOn = True
             End If
         End If

         If copyOn = True Then
             abytParsedData(copyIndex) = abytData(i)
             copyIndex = copyIndex + 1
         End If
     Next

     Put #nFileNum, , abytParsedData

     BlobToFile = LOF(nFileNum)

     BlobToFileExit:
     If nFileNum > 0 Then Close nFileNum
          Exit Function

     BlobToFileError:
     MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, _
            "Error writing file in BlobToFile"
     BlobToFile = 0
     Resume BlobToFileExit

 End Function

Answer 1

If I understand what you are trying to do, you basically want Adobe Reader to open an in-memory pdf file "object". This isn't possible. You'll need to write the pdf file out to the system hard drive and then open it from there. You can somewhat achieve what you're asking by either using the computers Temp folder or else managing the files/folder yourself. For example, you could possibly cleanup your PDF file folder every time the application opens.

Here's some code to help you do what you're trying to do. This code does not handle anything to do with creating folders, generating file names, checking to see if the file already exists, etc. I'm assuming that you'll be able to handle that. My code in Command1_Click assumes that you're using SQL Server with ODBC linked tables.

I'm using FollowHyperlink here but I highly recommend that you use Allen Browne's GoHyperlink function instead to open files. You'll probably have security errors with FollowHyperlink.

Private Sub Command1_Click()
    Dim r As DAO.Recordset, sSQL As String
    sSQL = "SELECT ID, BlobField FROM MyTable"
    Set r = CurrentDb.OpenRecordset(sSQL, dbOpenDynaset, dbSeeChanges)
    If Not (r.EOF And r.BOF) Then
        Call BlobToFile("C:\MyPDFFile.pdf", r("BlobField"))
        If Dir("C:\MyPDFFile.pdf") <> "" Then
            FollowHyperlink("C:\MyPDFFile.pdf")
        End If
    End If
    r.Close
    Set r = Nothing
End Sub


'Function:  BlobToFile - Extracts the data in a binary field to a disk file.
'Parameter: strFile - Full path and filename of the destination file.
'Parameter: Field - The field containing the blob.
'Return:    The length of the data extracted.
Public Function BlobToFile(strFile As String, ByRef Field As Object) As Long
    On Error GoTo BlobToFileError

    Dim nFileNum As Integer
    Dim abytData() As Byte
    BlobToFile = 0
    nFileNum = FreeFile
    Open strFile For Binary Access Write As nFileNum
    abytData = Field
    Put #nFileNum, , abytData
    BlobToFile = LOF(nFileNum)

BlobToFileExit:
    If nFileNum > 0 Then Close nFileNum
    Exit Function

BlobToFileError:
    MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, _
           "Error writing file in BlobToFile"
    BlobToFile = 0
    Resume BlobToFileExit

End Function