vba 如何使用 ms access 2007 打开存储在 sql server [image] 字段中的 PDF

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

提问by Seth Griffin

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.

我正在将数据库从访问转换为 sql 后端访问前端。该数据库嵌入了 pdf 文档，这些文档最终被 SQL 服务器的数据导入工具存储为 [图像] 数据。

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.

我的问题是我希望用户能够通过单击在 access 中创建的报告中的 pdf 图标来打开 pdf 文件。

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

这可以用 VBA 完成还是有更简单的方法？我完全不知道如何做到这一点。

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)

我编辑了 BlobToFile 函数以去除 ole 标题，因为 adobe 无法读取文件（evince 可以，mac 预览也可以）

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

回答by HK1

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.

如果我了解您要做什么，您基本上希望 Adob​​e Reader 打开内存中的 pdf 文件“对象”。这是不可能的。您需要将 pdf 文件写入系统硬盘驱动器，然后从那里打开它。您可以通过使用计算机 Temp 文件夹或自己管理文件/文件夹来实现您的要求。例如，您可以在每次打开应用程序时清理您的 PDF 文件夹。

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.

这里有一些代码可以帮助你做你想做的事情。这段代码不处理与创建文件夹、生成文件名、检查文件是否已经存在等有关的任何事情。我假设您能够处理这些。我在 Command1_Click 中的代码假定您将 SQL Server 与 ODBC 链接表一起使用。

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

我在这里使用 FollowHyperlink 但我强烈建议您使用Allen Browne 的 GoHyperlink 功能来打开文件。您可能会在使用 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