Solved

Storing a .jpg in Access

Posted on 2002-05-09
15
402 Views
Last Modified: 2013-11-19
How do I store an employee’s .jpg file in an Access database.

Has anyone got code to do this as well as display the picture when needed.

If I store the .jpg in the database will it slow it down. Should I just store a reference to the file?

I am using VB6

Thanks
deneuve
0
Comment
Question by:deneuve
  • 5
  • 3
  • 2
  • +3
15 Comments
 
LVL 18

Expert Comment

by:deighton
ID: 6998512
you can do this, I've got some code at home.  Performance is slightly degraded.
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 6998570
See the AppendChunk method for an example of adding binary data to a database.

But I would not do it. Databases that are containing binary fields becomes too big and, with Access, are subject for corruption. I prefer to have a link to the file stored into the table.
0
 
LVL 15

Expert Comment

by:robbert
ID: 6998660
Here is a ready-to use sample:
Loading/Saving Images to Database: Demo Application
http://www.freevbcode.com/ShowCode.Asp?ID=589

True, loading a picture from a database is a few percent slower than loading it from file. Yet I haven't experienced problems with Access/images/corruption. - You probably would not use "image databases" but it's OK to have images in databases, i.e., in order to have the data together.
0
 

Author Comment

by:deneuve
ID: 6998726
to emoreau

Is there a code example to link the .jpg to the database.

Thanks

Kathy Deneuve
0
 
LVL 18

Expert Comment

by:deighton
ID: 6998747
the sample code is ok, it writes to a file then uses loadpicture to get the image back again.  The one I've got at home sends data straight to the picture.
0
 
LVL 15

Expert Comment

by:robbert
ID: 6998775
> Is there a code example to link the .jpg to the database.

There is no such "linking" but you would simply have the image's filename in a db text field.

Set Picture1.Picture = LoadPicture(App.Path & "\images\" & objRst!strImgFileName)
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 6998824
you cannot link the jpg to a database. you have to store the full pathname to a character field into your database, and use this field to retreive your picture at runtime.
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 69

Expert Comment

by:Éric Moreau
ID: 6998827
robbert already said what I just said.
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 6998877
What are you using ADO, DAO?
0
 

Author Comment

by:deneuve
ID: 6998901
Thanks

to Richie Simonetti

I am using ADO
0
 

Author Comment

by:deneuve
ID: 6998932
to deighton

I would like to wait and see your code. Thank you

Kathy
0
 
LVL 18

Expert Comment

by:mdougan
ID: 6999053
Here is an example for updating an Access Database with a jpg file or reading an image from an Access Database and displaying it in a picturebox using the ADODB Stream Object:

' Project references the Microsoft Active Data Objects Library v 2.5

Dim Conn As ADODB.Connection
Dim RS As ADODB.Recordset

Const SQL_SERVER_DATABASE = "Provider=SQLOLEDB;Data Source=MYSERVER; Initial Catalog=MYDATABASE; User ID=MYUSERID;Password=MYPASSWORD;"
Const ACCESS_DATABASE = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=C:\Code\RPMShell\data\nwind.mdb"

Private Sub cmdUpdateImage_Click()
' You can use code like this to save out to a file, but only bmp supported
' SavePicture Picture1.Picture, "c:\winnt\compaq.bmp"

' Open your recordset
Set RS = New ADODB.Recordset
With RS
  .Source = "Select * From Employees"
  .ActiveConnection = Conn
  .CursorType = adOpenDynamic
  .LockType = adLockOptimistic
  .Open

' you can use code like this to add a new record
'  .AddNew
'  RS("caption").Value = "compaq logo image"
'  AddImage RS, "c:\winnt\compaq.bmp"

' either add a new record or find an existing one
  .Find "[LASTNAME] = 'Dougan'"
' call the function to update the Photo field of the recordset
  AddImage RS, "c:\Code\moitessier.jpg"
' close the recordset
  .Close
End With
Set RS = Nothing
MsgBox "Update Complete"
End Sub

Private Sub cmdReadImage_Click()

' Open your recordset
Set RS = New ADODB.Recordset
With RS
  .Source = "Select * From Employees"
  .ActiveConnection = Conn
  .CursorType = adOpenDynamic
  .LockType = adLockOptimistic
  .Open
' Find a particular record
  .Find "[LASTNAME] = 'Dougan'"
' Save the Photo to a local file
  ReadImage RS, "c:\test.jpg"
' Load the local file into a picturebox
  Picture1.Picture = LoadPicture("c:\test.jpg")
  .Close
End With
Set RS = Nothing
MsgBox "Read Complete"
End Sub

Sub AddImage(RS As ADODB.Recordset, ByVal FileName As String)
' Declare a stream object
Dim oStream As ADODB.Stream

' Create a new instance
Set oStream = New ADODB.Stream
oStream.Type = adTypeBinary
oStream.Open

' Tell it the source of the stream
oStream.LoadFromFile FileName
 
'Load the binary object into the field value
RS.Fields("Photo").Value = oStream.Read

' Update the recordset (either here or you could do this elsewhere)
RS.Update

' Close the stream and clean house
oStream.Close
Set oStream = Nothing

End Sub

Sub ReadImage(RS As ADODB.Recordset, ByVal FileName As String)
' Declare a stream object
Dim oStream As ADODB.Stream

' Create a new instance
Set oStream = New ADODB.Stream
oStream.Type = adTypeBinary
oStream.Open

' Read the field value into the stream object
oStream.Write RS.Fields("Photo").Value

' Save out to a local file
oStream.SaveToFile FileName, adSaveCreateOverWrite

' Close the stream and clean house
oStream.Close
Set oStream = Nothing

End Sub


Private Sub Form_Load()
' Open your database connection
Set Conn = New ADODB.Connection
With Conn
'    .ConnectionString = RPM_DATABASE
    .ConnectionString = ACCESS_DATABASE
    .CursorLocation = adUseClient
    .Open
End With

End Sub

Private Sub Form_Unload(Cancel As Integer)
'Close your database connection
Conn.Close
Set Conn = Nothing

End Sub


It's not bad if you are not going to have thousands of images, however, if you are, then storing simply the path to the image (You can use UNC names like "\\myserver\photos\photo1.jpg" to make it possible to use the same paths from all computers on the network) and then loading the image into a picturebox using LoadPicture as indicated in the above example.

(interesting name by the way)

Mike
0
 

Author Comment

by:deneuve
ID: 6999222
Thank you Mike.

It is a very explicit example.
I will try this.

Name is from father French, mother Irish. Nothing famous...unfortunately

Kathy
0
 
LVL 18

Accepted Solution

by:
mdougan earned 100 total points
ID: 6999249
You're welcome.

I'd answered this question several times before using the GetChunk and AppendChunk syntax, but I've seen some other experts who've suggested this Stream syntax and I like it a lot better.  Your question finally convinced me to rewrite the GetChunk sample using the Stream Object...

Oh well, it's a fortunate name to have anyway <grin>

Here is a more complete sample (you'll have to guess at the controls to put on the form based on their references... cdlg is a common dialog control).

The nwind database comes with images in them, but they are in Ole Object bitmap format and can't be displayed using the code as written.  But, if you update each record with a valid jpg/gif/bmp then the code will work.  You can mix and match file types.  I'm saving the temp files as temp.dat and the LoadPicture method is smart enough to figure out what the base file type is.

' Project references the Microsoft Active Data Objects Library v 2.5
' Project component references the Common Dialog Control

Private Conn As ADODB.Connection
Private RS As ADODB.Recordset

Private sPath As String

Const SQL_SERVER_DATABASE = "Provider=SQLOLEDB;Data Source=MYSERVER; Initial Catalog=MYDATABASE; User ID=MYUSERID;Password=MYPASSWORD;"
'Const ACCESS_DATABASE = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=C:\Code\RPMShell\data\nwind.mdb"
Const ACCESS_DATABASE = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=H:\Code\ImageDB\nwind.mdb"

Private Sub cmdFind_Click()
Dim sCriteria As String
Dim vSavePlace As Variant

    vSavePlace = RS.Bookmark
   
    If txtFirstName.Text <> "" Then
        sCriteria = "FIRSTNAME LIKE '" & txtFirstName.Text & "%'"
    End If
   
    If txtLastName.Text <> "" Then
        If Len(sCriteria) = 0 Then
            sCriteria = "LASTNAME LIKE '" & txtLastName.Text & "%'"
        Else
            sCriteria = sCriteria & " AND LASTNAME LIKE '" & txtLastName.Text & "%'"
        End If
    End If
   
    RS.Find sCriteria, , adSearchForward, 1
    If RS.BOF Or RS.EOF Then
        MsgBox "Record Not Found"
        RS.Bookmark = vSavePlace
    End If
   
    DisplayRecord
   
End Sub

Private Sub cmdFirst_Click()
    RS.MoveFirst
    DisplayRecord
End Sub

Private Sub cmdLast_Click()
    RS.MoveLast
    DisplayRecord
End Sub

Private Sub cmdNext_Click()
    RS.MoveNext
    If RS.EOF Then
        RS.MoveLast
    End If
    DisplayRecord
End Sub

Private Sub cmdPrevious_Click()
    RS.MovePrevious
    If RS.BOF Then
        RS.MoveFirst
    End If
    DisplayRecord
End Sub

Private Sub cmdUpdateImage_Click()
   
    cdlg.DialogTitle = "Update Image"
    cdlg.Filter = "JPG Files *.jpg|*.jpg|GIF Files *.gif|*.gif|BMP Files *.bmp|*.bmp|All Files *.*|*.*"
    cdlg.ShowOpen
    If cdlg.FileName <> "" Then
        AddImage RS, cdlg.FileName
        RS.Update
        DisplayRecord
    End If
 
End Sub

Private Sub AddImage(RS As ADODB.Recordset, ByVal FileName As String)
' Declare a stream object
Dim oStream As ADODB.Stream

' Create a new instance
Set oStream = New ADODB.Stream
oStream.Type = adTypeBinary
oStream.Open

' Tell it the source of the stream
oStream.LoadFromFile FileName
 
'Load the binary object into the field value
RS.Fields("Photo").Value = oStream.Read

' Close the stream and clean house
oStream.Close
Set oStream = Nothing

End Sub

Private Sub ReadImage(RS As ADODB.Recordset, ByVal FileName As String)
' Declare a stream object
Dim oStream As ADODB.Stream

' Create a new instance
Set oStream = New ADODB.Stream
oStream.Type = adTypeBinary
oStream.Open

' Read the field value into the stream object
oStream.Write RS.Fields("Photo").Value

' Save out to a local file
oStream.SaveToFile FileName, adSaveCreateOverWrite

' Close the stream and clean house
oStream.Close
Set oStream = Nothing

End Sub

Private Sub DisplayRecord()
On Error Resume Next

    txtFirstName.Text = RS("FIRSTNAME").Value
    txtLastName.Text = RS("LASTNAME").Value

' Save the Photo to a local file
    ReadImage RS, sPath & "temp.dat"

' Load the local file into a picturebox
    picPhoto.Picture = LoadPicture()
    picPhoto.Picture = LoadPicture(sPath & "temp.dat")
   

End Sub

Private Sub Form_Load()
' Open your database connection
Set Conn = New ADODB.Connection
With Conn
'    .ConnectionString = SQL_SERVER_DATABASE
    .ConnectionString = ACCESS_DATABASE
    .CursorLocation = adUseClient
    .Open
End With

If Conn.State <> adStateOpen Then
    MsgBox "Failed to Open Connection", vbExclamation, "Open Connection"
    End
End If

' Open your recordset
Set RS = New ADODB.Recordset
With RS
  .Source = "Select * From Employees"
  .ActiveConnection = Conn
  .CursorType = adOpenDynamic
  .LockType = adLockOptimistic
  .Open
End With

If RS.State <> adStateOpen Then
    MsgBox "Failed to Open Recordset", vbExclamation, "Open Recordset"
    End
End If

If Right(App.Path, 1) = "\" Then
    sPath = App.Path
Else
    sPath = App.Path & "\"
End If

End Sub

Private Sub Form_Unload(Cancel As Integer)
'Close your database connection
Conn.Close
Set Conn = Nothing

End Sub

0
 

Author Comment

by:deneuve
ID: 7000698
Thank you all

Thanks Mike

Kathy
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

Keep your audience engaged and get the most out of your next presentation with these quick Prezi tips.
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In addition to being a great web-based presentation tool, Prezi also makes it easy to save your presentation as a PDF to share with others as well. Learn how in this tutorial. Select the share icon from the top menu in your Prezi editor: Select "D…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now