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).
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:
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:
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!