[AccessD] Opening Files from BLOB in SQL Server 2012

[Jurgen Welz]

It turns out that you don't need to do the Unicode conversion if you write the byte array directly to the file system.  The sample code I had from the old knowledgebase article used a string variable but that step is unnecessary as is taking fixed size chunks out of the file in a loop plus any remainders.

I have an error logger (LogErr), a function that returns a pointer to a persistent database object (fnDB, that instantiates it if it doesn't exist), a logged in user name function (fnUserName Api call) and a file launcher (RunApp Api Call) that opens files based on extension with the default windows file association.  All users running the application have a folder with the base path defined in strFilePathName since that's where the application runs from.  The function is passed the AttachmentID, grabs the data from the blob, writes it to the location with the file extension with which it was uploaded and saves it with the extension name.  If a person opened a file and then ran the function again with the same file ID while that file is open, there would be a file access error so I'll build a check to see whether such a file already exists (Dir())and if so, whether it is open (Open strPathFileName For Binary Access Read Lock Read As intFile) and if it is, append a version suffix in a loop until the incremented version suffix reaches an unfound and/or unlocked version) before saving and opening it a subsequent time.

Barring the replacement of the assignment statement for the strFilePathName with a function that returns the file name for opening concurrent versions of the same file, the following is getting the job done.

Public Function ShowFile(lngID As Long)
'AttachmentID is primary Key of Blob file record
'AttachmentFileType is the file extension including the '.'  - ie '.txt' or '.docx'
    On Error GoTo ErrorHandler

    Dim db As DAO.Database
    Dim r As DAO.Recordset

    Dim strUser As String
    Dim lngFileLength As Long
    Dim lngI As Long
    Dim intFile As Integer
    Dim bytes() As Byte
    Dim strFilePathName As String

    strUser = fnUserName
    Set db = fnDB
    Set r = db.OpenRecordset("SELECT AttachmentData, AttachmentFileType FROM HQAF WHERE AttachmentID = " & lngID, dbOpenSnapshot)
    strFilePathName = "C:\Users\" & strUser & "\GOM\" & lngID & r(1)
    intFile = FreeFile
    Open strFilePathName For Binary As intFile
    lngFileLength = r(0).FieldSize()
    ReDim bytes(0 To lngFileLength)
    bytes = r(0).GetChunk(0, lngFileLength)
    Put intFile, , bytes
    Close intFile
    RunApp strFilePathName, 1

ExitRoutine:
    On Error Resume Next
    r.Close
    Set r = Nothing
    Set db = Nothing
    Exit Function

ErrorHandler:
    With Err
        Select Case .Number
            Case Else
                LogErr "modBlob", "ShowFile", .Description, .Number
        End Select
    End With
    'Resume 0
    Resume ExitRoutine
End Function