Thursday, November 21, 2013

Storing PDFs in MS Access

Store is as a binary:

Function LoadFileFromDisk(Bestand, Optional FileName As String = "")
  Dim imgByte() As Byte
  If FileName = "" Then FileName = strFileName
  Open FileName For Binary Lock Read As #1
  ReDim imgByte(1 To LOF(1))
  Get #1, , imgByte
  Close #1
  If Not IsEmpty(imgByte) Then Bestand.Value = imgByte
End Function
This text is after the block

Microsoft website solution is quite long:

Amara BLOB example:

'Read the file 'Image-In.jpg' into the field named 'Image'.
FileToBlob "C:\Photos\Image-In.jpg", [Image]

'Extract the file contained in the field named 'Image' to a file named 'Image-Out.jpg'.
BlobToFile "C:\Photos\Image-Out.jpg", [Image]

'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 StringByRef Field As ObjectAs 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)

    If nFileNum > 0 Then Close nFileNum
    Exit Function

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

End Function

'Function:  FileToBlob - Loads a file into a binary field.
'Parameter: strFile - Full path and filename of the source file.
'Parameter: Field - The binary field into which the file is to be loaded.
Public Function FileToBlob(strFile As StringByRef Field As Object)
    On Error GoTo FileToBlobError

    If Len(Dir(strFile)) > 0 Then
        Dim nFileNum As Integer
        Dim byteData() As Byte

        nFileNum = FreeFile()
        Open strFile For Binary Access Read As nFileNum
        If LOF(nFileNum) > 0 Then
            ReDim byteData(1 To LOF(nFileNum))
            Get #nFileNum, , byteData
            Field = byteData
        End If
        MsgBox "Error: File not found", vbCritical, _
               "Error reading file in FileToBlob"
    End If

    If nFileNum > 0 Then Close nFileNum
    Exit Function

    MsgBox "Error " & err.Number & ": " & err.Description, vbCritical, _
           "Error reading file in FileToBlob"
    Resume FileToBlobExit

End Function

Display BLOB images in MS Access Report

ADO stream object - this may only work with SQL


Opening Excel application using VBA

In the references, add in "Microsoft Excel n Object Library".

Then the code should look as follows:

Dim exApp As Excel.Application
Set exApp = CreateObject("Excel.Application")
exApp.Visible = True

Microsoft Access Dim dbs object

When writing code in previous version of Microsoft Access, the database object could be dimensioned with the following line:

    Dim dbs As Database

That line now cause a compile error. MS Access is able to find the reference by changing it to:

    Dim dbs As DAO.Database

Using a file dialog box in Access 2010

When installing a "Microsoft Common Dialog Control 6.0" in an Access 2010 form, I receive the error "You don't have the license required to use this ActiveX control".

Apparently the ActiveX controls have a lot of new limitations for security reasons in Access 2010.

To work around this, I dimenioned the control directly in the code as follows:

Dim f As FileDialog
Set f = Application.FileDialog(msoFileDialogFilePicker)
MsgBox "file choose was " & f.SelectedItems(1)

When doing this, make sure to add "Microsoft Office 14.0 Object Library" to the references.