Store then Retrieve a File from a Jet Database Field - October 2000

I recently had a requirement to store a picture in a Jet [Access] database. I had never done this before so I posted a question in a couple of the news groups. I got suggestions on how to store the picture but I didn't get any real code - so I wrote some VB code to test the suggestions. I generalized the code, encapsulated it in a DLL, and decided to make it the October tip-of-the-month.

2003/02/09 - Add support for ADO. I converted the code to use ADO 2.7. Otherwise the tip remains the same.

Concept

Things to consider if you want to store a file in a Jet database:

Define a field in the database as an 'OLE Object' which is DAO/ADO data type 'long binary'.
Define a field in the database as 'text', length 255. The fully qualified file name will be stored in this field.
Use the 'AppendChunk' and 'GetChunk' DAO/ADO methods. This is done for you in the DLL described below.
Open the file for 'Binary' and use 'Get' and 'Put' to read/write the file. This is also done for you in the DLL.

Typical DAO Code Used to Call the DLL

First you need to register the DLL then make a reference to it [TB Save Create a File From an Access Field] in your application. Typical VB DAO calling code:

Private Function DoStoreInDB() As Boolean
' Store the file in a database field.
    Dim rs As Recordset
    Dim fldLongBinary As Field
    Dim fldFileName As Field
    Dim sTemp As String
    Dim obj As New SaveCreateFile.cStoreCreateFile  'define the object

    sTemp = Trim$(Me.Text1(0).text)    'get the file name
    Set rs = mDB.OpenRecordset(mcsSQL) 'make sure there is at least one record and store the file name
    With rs
        If .EOF Then
            .AddNew 'add one 'dummy' record if none
            .Update 'update the table
        End If
    End With
    Set rs = mDB.OpenRecordset(mcsSQL) 'file will be stored in the first record
    With rs
        If Not .EOF Then
            Set fldFileName = .Fields![sFileName]  'set reference to this field
            Set fldLongBinary = .Fields![oPicture] 'set a reference to the file field
            With obj 'now call the dll
                If .StoreFileIntoField(rs, fldFileName, fldLongBinary, sTemp) Then 'call the dll
                    DoStoreInDB = True
                End If
            End With
        End If
       .Close
    End With
End Function

Typical ADO Code Used to Call the DLL

First you need to register the DLL then make a reference to it [TB Save Create a File From an Access Field] in your application. Typical VB ADO calling code:

Private Function DoStoreInDB() As Boolean
' Store the file in a database field.
   
Dim rs As New ADODB.Recordset
    Dim fldLongBinary As ADODB.Field
    Dim fldFileName As ADODB.Field
    Dim sTemp As String
    Dim obj As New SaveCreateFileADO.cStoreCreateFileADO

    sTemp = Trim$(Me.Text1(0).Text)
    'make sure there is at least one record and store the file name
    rs.Open mcsSQL, mConn, adOpenKeyset, adLockOptimistic
    With rs
        If .EOF Then
            .AddNew 'add one 'dummy' record if none
            .Update 'update the table
        End If
    End With
    Set rs = Nothing

    'file will be stored in the first record
    rs.Open mcsSQL, mConn, adOpenKeyset, adLockOptimistic
    With rs
        If Not .EOF Then
            Set fldFileName = .Fields![sFileName] 'set reference to this field
            Set fldLongBinary = .Fields![oPicture] 'set a reference to the file field
            With obj 'now call the dll
                'call the dll
                If .StoreFileIntoField(rs, fldFileName, fldLongBinary, sTemp) Then
                    DoStoreInDB = True
                End If
            End With
        End If
        .Close
    End With
End Function

Reference the DLL

The StoreCreateFile DLL

I encapsulated the code to store and retrieve a file to a jet [access] database in class DLL 'StoreCreateFile'. Methods and properties exposed are shown in this object browser screen shot. The ADO version has ADO appended to the name.

Method or Variable Description Prototype or Comment
StoreFileIntoField Store a File in a Field .StoreFileIntoField rs, fldFileName, fldLongBinary, sFileName
CreateFileFromField Create a File from a Field .CreateFileFromField fldLongBinary, sFileName
StoreFileNameIntoField Store the File Name in a Field .StoreFileNameIntoField rs, fldFileName, sFileName
rs Recordset Points to a record [row] in an open recordset.
fldFileName Field The field in the recordset in which the fully qualified file name is stored.
fldLongBinary Field The 'long binary' field in the recordset in which the file is stored.
sFileName String A fully qualified file name.

You don't need to call method 'StoreFileNameIntoField'. It is called by 'StoreFileIntoField'. The method is provided so you could 'override' the file name stored by 'StoreFileIntoField'.

Demo Program

I wrote a demo program that stores a picture [actually any file] in a jet database. At startup the program loads a picture, of my daughter, in the left image control. The picture is 'stretched' [resized to fit the image control]. Double-click on the picture to un-stretch it. Double-click again to stretch it.

1. Get a New File - Use this button to browse for another file. If the file is a picture it is displayed in the left image control. You are prompted for a file name, for example:

Use the 'open' tool bar button on the 'Get a New File' button to open the selected file with its associated application. In this example the photo is opened with the 'Microsoft Photo Editor', my default application associated with 'JPG' files:

The 'open' routines use 'ShellEx' to open the file with it's associated application. ShellEx is the topic of the February 2000 tip-of-the-month. For more information on ShellEx click here.

2. Store the File in a Database Field - Use this button to store the file in an access database field. The file name contained in the 'heading' text box is opened and stored in long binary field 'oPicture' in table tblPicture in database 'StorePicture.mdb'. The file name is stored in field 'sFilename'. The table layout:

3. Get the Stored File from the Database Field - Use this button to retrieve the file from the database. If it is a picture the image is shown in the right image control. A temporary file is created in 'app.path' with an extension that matches the extension of the stored file name. The temporary file is loaded into the image control with the LoadPicture function. After loading the temporary file is deleted.

4. Save the Database Field as a File - Use this button to store the last retrieved file. You are prompted for a file name, for example:

Use the 'open' tool bar button on the 'Save the Database Field as File' button to open the last saved file with its associated application. The 'open' button is only shown if a file has been saved in 'Step 4'.

Uses DAO 3.51 and ADO 2.7

If using the DAO version I used DAO 3.51 in the DLL and the 'demo' program. You can use DAO 3.6 if you want to. Just open the VB projects and change the reference. To download DAO 3.6 components or learn more about it click here

If using the ADO version I used ADO 2.7.

Notes

I first planned to use the VB SavePicture method to 'move' the picture from the image control to a file. This worked fine except that the SavePicture method does not save 'transparent' pictures properly. However; if the picture is 'loaded' from a file into the database field then all picture attributes are stored, including transparency.

Any file can be saved in the database field. I suspect that a database could be stored, but I have not tried it. And I suspect that trying to store the database within itself would cause problems!

Storing a 'setup' program in a database field may be a good way to distribute database oriented applications! An 'extractor' program could launch the setup program then delete it from the database. The possibilities are 'endless' and I'll leave the testing to others.

The Demo Program Uses Code from Prior Tips

The demo program uses the following modules, some of  which are 'prior' tips. Link to the tip by clicking on the module name.

Month Module Comment
Dec 1997 modRegistry Read and write to the registry.
Mar 1998 modTempName Get a temporary file name.
Aug 1998 modConnectToWebSite Used to connect to a web site using a URL and display this page.
Sep 1998 modLocationLocationLocation Form housekeeping, including window sizing.
May 1999 modPath Extract the file name, folder or extension from a fully qualified file name.
May 1999 modAddBackslash Add a backslash to a folder name if needed.
Feb 2000 clsShellEX Launch a file with its associated application.
June 2002 modStatus Show status information in a status bar control.
  modNotTooSmall Make sure a value is not too small or too big.
Dec 2003 modMouseSet Nest and 'un-nest' mouse pointer values.
  cDialog A class module used to browse for a file.

Download VB Code

DAO Version - Click here to download the VB 6 Source Code for the DAO version [148k] for the DAO dll and the DAO demo program.

ADO Version - Click here to download the VB 6 Source Code for the ADO version [150K] for the ADO dll and the ADO demo program.

Both the dll and demo program have been compiled using VB6. VB 6 source code is provided for both. Be sure to register the dll before executing the demo program. 

Update 2000/10/15 - The VB source code was modified to provide better error feedback to the caller. More errors are raised. If you downloaded a version before this date you may want to download the updated source code.

Previous Tip   Tip Index    Next Tip

Home

Updated 2003/11/27