Home | What's New | Articles | Code Downloads | Tool Box | Links | Code Snippets  

Database Code Snippets

Placing a file into an Access database

Using ADO a file can be saved into a field of type OLE Object.  The sub setBLOB will read a file from the disk and place it into the field specified. getBLOB will take the file at the specified field and save it to disk.

Public Sub getBLOB(RS As ADODB.Recordset, Field As String, Des As String)
    Dim lngFieldSize As Long
    Dim fileBytes() As Byte
    Dim intFileHandle As Integer

    intFileHandle = FreeFile
   
    lngFieldSize = RS(Field).ActualSize
    If lngFieldSize > 0 Then
        fileBytes = RS(Field).GetChunk(lngFieldSize)
        Open Des For Binary As intFileHandle
            Put intFileHandle, , fileBytes
        Close intFileHandle
    End If
End Sub

Public Sub setBLOB(RS As ADODB.Recordset, Field As String, Source As String)
    Dim fileBytes() As Byte
    Dim intFileHandle As Integer

    intFileHandle = FreeFile

    Open Source For Binary As intFileHandle
        fileBytes = InputB(LOF(intFileHandle) - 1, intFileHandle)
        RS(Field).AppendChunk fileBytes
    Close intFileHandle
End Sub

Example of use:

setBLOB myRecordSet, "FileField", "c:\myfile.gif" ' Places file into database
' Save file to disk
getBLOB myRecordSet, "FileField", "c:\myfile_extracted_from_database.gif"

Preventing 'invalid use of null' error

If you try to assign a value from a database field to a text box and the field contains a null an error will be generated.  One way to prevent this is to append an zero length string as shown below.

Text1.Text = myRecordSet!MyField & ""