Database - How can I store images (or other files) in a database?

Most databases allow you to store data of various kinds, including differing kinds of numbers and text. Most also allow you to store binary data, which allows you to store entire files inside your records.


One thing to bear in mind is that storing files can increase the size of your database dramatically - which can cause problems for some database systems if you have too many (MS Access especially). Storing images in the database does however mean that you can backup the images along with the rest of your data.

An alternative to storing images in the database is to store them in an area that all users of the database can connect to, and just keep the path to the file in your database, eg: "\\MyServer\Pictures\Picture1.jpg". Which method you use is up to you.

One final thing to think about, if you are storing files in the database you may want to create a text field too, so that you can store the name of the file. This way, when you get the file back out again you can save it with the original file name - otherwise you will have to make up the name (or let the user do it), but if you don't use the right extension (eg .jpg) you wont be able to open it!


Anyway, on to the part you came here for... A tutorial by Beacon to store images in an Access database can be found in the attachment at the bottom of the first post in the thread Tutorials and Tips, which is now also shown in the post below!

With Beacon's permission, the following is a re-production of his attachment in the Tutorials and Tips thread, which is for VB6 (or VB5/VBA). :thumb:

Please note that the code can be used for any database system, not just Access - you just need to change the data type of the Picture field to a binary format, and use your usual connection string instead of the one posted.



G’day so you want a way to store your happy snaps in a Access Database. Well here’s your answer, a simple tutorial on how to store pictures in a Access Database!

It is relatively simple but does recquire a minimal knowledge of databases. Hopefully in the next few paragraphs you’ll learn how to do it. If not well I failed badly in my objective.

Note: This will be done in MS Access 97. Why? Because…
Also this is to store the image not just the path, this is only recommended for small to medium sized databases.

STEP 1 - CREATING THE DATABASE

Load Access and make a database call it images.mdb for the sake of it.

Go into design view and construct a table with 3 fields.

Code:

---

Field Name                        Type
PicID                                Number
Description                        Text
Picture                                OLE *

---

* Note: for SQL Server (2000 or earlier), the data type needed for the Picture field is Image (which stores large binary data) rather than OLE.
For SQL Server (2005 or later), the data type you should use is varbinary(max)
For anything other than Access or SQL Server, see the documentation of your database system to find the data type (you want something like "large binary" or "BLOB").


Save the table and call it what you want for the sake of this tutorial I called mine Table1.

Great you have now constructed the table needed to house the Information. Proceed to Step 2.


STEP 2 - CREATING THE FORM

Load Visual Basic and create a standard .exe.

Once the form has loaded you will need too put:

4 command buttons. cmdPrevious, cmdNext, cmdDelete and cmdAdd
1 text box: txtDescription
1 image box: image1 and
add the Microsoft Common Dialog Control from the components Menu call it dlgAdd.

Your Form should now look something similar to this:

http://www.vbforums.com/attachment.p...chmentid=47210

If so you're on your way to Image Heaven, if not go straight to hell.


STEP 3 - THE CODE:

The code is pretty well straight forward and doesn’t need much explaining.

Firstly declare these:

VB Code:

Option Explicit
 
Private cn As ADODB.Connection
Private rs As ADODB.Recordset

This is for the connection to the database.

Now on the form load event add this code.

VB Code:

Private Sub Form_Load()
    
    Set cn = New ADODB.Connection
'make this the path to the image database.
    cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.3.51;" & _
      "Data Source= f:\image_library\images.mdb" 
    cn.Open
    
    
    Set rs = New ADODB.Recordset
    rs.Open "Table1", cn, adOpenKeyset, adLockPessimistic, adCmdTable
        
    
    FillFields 'this is a sub that I’ll explain later.
End Sub

On the cmdPrevious button have this:

VB Code:

Private Sub cmdPrevious_Click()
      'making txtdescription show whats in the description field
    rs.Fields("Description") = txtDescription.Text  
 
    
    rs.MovePrevious
      'just to make sure your at the start and so you don’t get that error.
    If rs.BOF Then rs.MoveFirst 
    
    FillFields
End Sub

On cmdNext have this:

VB Code:

Private Sub cmdNext_Click()
      'making txtdescription show whats in the description field
    rs.Fields("Description") = txtDescription.Text
    
    rs.MoveNext
    If rs.EOF Then rs.MoveLast
    
    FillFields
    
If rs.EOF Then
    MsgBox "At last record" 'avoiding that nasty error.
End If
End Sub

On cmdDelete do this:

VB Code:

Private Sub cmdDelete_Click() 'this will delete the entire record.
    If Not (rs.BOF And rs.EOF) Then 
        rs.Delete
        If Not (rs.BOF And rs.EOF) Then
            rs.MoveNext
            If rs.EOF Then rs.MoveLast
            FillFields
        End If
    End If
End Sub

Ok now the good one adding the image to the db!
On cmdAdd have this:

VB Code:

Private Sub cmdAdd_Click()
    Dim bytData() As Byte
    Dim strDescription As String
    
    On Error GoTo err
    
    With dlgAdd 'remember the common dialog box.
          'filtering so only jpg’s and gifs are shown!
        .Filter = "Picture Files (*.jpg, *.gif)|*.jpg;*.gif" 
        .DialogTitle = "Select Picture"  'sets the title of it.
        .ShowOpen  'show the open dialog box.
           
          'bit to “convert” the image to binary.
        Open .FileName For Binary As #1  
        ReDim bytData(FileLen(.FileName))
    End With
      
    Get #1, , bytData
    Close #1
    
      'show a input box to enter description to the description field.
    strDescription = InputBox("Enter description.", "New Picture")     
    
    With rs
        .AddNew
        .Fields("Description") = strDescription  'adding record to db
        .Fields("Picture").AppendChunk bytData  'adding the picture to the db
        .Update
    End With
    
    FillFields
    Exit Sub   
err:
    If err.Number = 32755 Then     'simple error check.
    Else
        MsgBox err.Description
        err.Clear
    End If
End Sub

Here’s the all important fillfields sub:

VB Code:

Public Sub FillFields()
    If Not (rs.BOF And rs.EOF) Then
        txtDescription.Text = rs.Fields("Description")
        Set Image1.DataSource = rs 'setting image1’s datasource
        Image1.DataField = "Picture" 'set its datafield.
    End If
End Sub

And lastly on form unload:

VB Code:

Private Sub Form_Unload(Cancel As Integer)
    On Error Resume Next
    
    rs.Close
    cn.Close
    Set rs = Nothing
    Set cn = Nothing 'closing all connections.
End Sub

Now press Run and add in a image you should now get something similar to this:

http://www.vbforums.com/attachment.p...chmentid=47211

Now you have a basic Image Library to store all your happy snaps in.
Any problems don’t see me!

Good Morning, Good Afternoon and if I don’t see you in the Forums Good Night!