This sample uses a SQL Server database with a table named tblImages. This table has 3 fields.
Field Name Data Type Size
Picture Image
ID Int 4
Description VarChar 255If
you are using Access you are better of saving the path to the file
instead. Access has a size limit of 2GB for a database and it may not
take long to fill that with pictures.
In a module add this code
Option Explicit
Public strConnString As String
Public Sub AddImageToDB(ByVal strFile As String, ByVal ID As Integer, ByVal Description As String)
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strStream As ADODB.Stream
Set cn = New ADODB.Connection
cn.ConnectionString = strConnString
cn.Open
'Add the image to the database
Set strStream = New ADODB.Stream
strStream.Type = adTypeBinary
strStream.Open
strStream.LoadFromFile strFile
Set rs = New ADODB.Recordset
With rs
.ActiveConnection = cn
.Source = "SELECT ID, Picture, Description FROM tblImages"
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open
End With
rs.AddNew
rs.Fields("ID").Value = ID
rs.Fields("Description").Value = Description
rs.Fields("Picture").Value = strStream.Read
rs.Update
rs.Close
'Cleanup
strStream.Close
Set strStream = Nothing
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub
Public Function ViewFromDB(ByVal ID As String, ByVal TempPath As String) As Boolean
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strStream As ADODB.Stream
Dim strSQL As String
Set cn = New ADODB.Connection
cn.ConnectionString = strConnString
cn.Open
strSQL = "SELECT Picture, Description " & _
"FROM tblImages " & _
"WHERE ID = " & ID
Set rs = New ADODB.Recordset
With rs
.ActiveConnection = cn
.Source = strSQL
.Open
End With
If Not (rs.BOF And rs.EOF) Then
Set strStream = New ADODB.Stream
strStream.Type = adTypeBinary
strStream.Open
strStream.Write rs!Picture
strStream.SaveToFile TempPath, adSaveCreateOverWrite
strStream.Close
Set strStream = Nothing
ViewFromDB = True
End If
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Function
And on your form to save and view a picture try this.
Option Explicit
Private Sub cmdSave_Click()
On Error GoTo cmdSave_Error
With CommonDialog1
.CancelError = True
.Filter = "Image Files (*.gif; *.bmp; *.jpg)| *.gif;*.bmp;*.jpg"
.ShowOpen
End With
AddImageToDB CommonDialog1.FileName, 1, "File added to database"
Exit Sub
cmdSave_Error:
End Sub
Private Sub cmdView_Click()
Dim strTempPath As String
Dim strTempName As String
Dim strTempFile As String
Dim blnShow As Boolean
'Create a temp file name
strTempPath = IIf(Right(AppPath, 1) = "\", App.Path, App.Path & "\")
strTempName = Format(Now, "MMDDYYHHNNSS") & ".bmp"
strTempFile = strTempPath & strTempName
blnShow = ViewFromDB(1, strTempFile)
If blnShow Then
Picture1.Picture = LoadPicture(strTempFile)
DoEvents
Kill (strTempFile)
End If
End Sub
Private Sub Form_Load()
'Set the connectionstring to your database
strConnString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=ImageDatabase;Data Source=MARKPC"
End Sub
|