Import Ole jpg files into a database table

Don't use OLE fields to store pictures. They will bloat your database significantly (a 30k jpg can bloat a db by 1MB or more). Store a path to the files instead - see here:
http://www.mvps.org/access/forms/frm0030.htm

I have found that storing the file name only is more flexible. Then you can have the cleint application determine the path.

EX: store pic.jpg

Then use that variable to complete the path in you application: /folder/[variable] = /folder/pic.jpg

Hi fordraiders,

Unless you have a database that must be transported on a floppy disk I strongly suggest you do store your images in the database catalogue.
There are many good reasons to support this, outlined in the following post, the least of which is the ability to include blob data in sql transactions and database maintenance plans, not to mention flexibility when connecting to the db from a myriad of other platforms.

Sample download Jet mdb and sample ASP image rendering scripts.
Using Binary Large Objects with Microsoft Access
  http://www26.brinkster.com/alzowze/blobs.asp


Alan

Alan,
The program is going to be Notebook based only . No server application involved.

What I have is simply this now

'=====================================
Private Const UNIT_PREFIX = "Unit1"
Private Const BIN_PREFIX = "Bin"


Private Sub cmdClear_Click()
 Dim myImg, myTxt As Control
Dim i As Integer
For i = 1 To 8
 Set myImg = Me.Controls("Image" & i)
 myImg.Picture = "c:\EE_BETA\not_filled.bmp"
 Set myTxt = Me.Controls("Text" & i)
 myTxt = ""
Next i
myPointer = 0

End Sub


Public Sub cmdCreate_Click()

    Dim i As Integer
    Dim j As Byte
    Dim sFile As String
    Dim iHandle As Integer
   
   
    On Error Resume Next
   
    sFile = GetCFGFile
    If sFile = "" Then Exit Sub

    iHandle = FreeFile
   
    Open sFile For Output As iHandle
    If Err.Number <> 0 Then
        MsgBox "Failed to open " & sFile & vbCrLf & Err.Description
        Exit Sub
    End If
   
    'Go thru the number of textboxes
    For i = NO_OF_TEXTBOXES To 1 Step (NO_OF_BINS * -1)
       
        ' no images ....   Get start position of textbox
        j = i
       
'        For j = 1 To NO_OF_BINS
'            Debug.Print UNIT_PREFIX, "Row" & (i / 4), BIN_PREFIX & j, "text" & i - NO_OF_BINS + j
'            Print #iHandle, UNIT_PREFIX & "-" & "Row" & (i / 4) & "-" & BIN_PREFIX & j & "|" & Me("text" & i - NO_OF_BINS + j).Value
 ' include images
 For j = 1 To NO_OF_BINS
            Debug.Print UNIT_PREFIX, "Row" & (i / 4), BIN_PREFIX & j, "text" & i - NO_OF_BINS + j, "image" & i - NO_OF_BINS + j
            Print #iHandle, UNIT_PREFIX & "-" & "Row" & (i / 4) & "-" & BIN_PREFIX & j & "|" & Me("text" & i - NO_OF_BINS + j).Value & "|" & Me("image" & i - NO_OF_BINS + j).Picture
        Next j
    Next i
   
    Close #iHandle
   
    MsgBox "Data saved in " & sFile
End Sub


Private Sub cmdRead_Click()
    Dim sFile As String
    Dim iHandle As Integer
    Dim sLine As String
    Dim sFirst() As String
    Dim sNext() As String
    Dim iRowNumber As Integer
    Dim iBinNumber As Integer
    Dim iStart As Integer
   
   
    sFile = GetCFGFile
    If Dir(sFile) = "" Then
        MsgBox "file does not exist"
        Exit Sub
    End If
    If sFile = "" Then Exit Sub

    iHandle = FreeFile
   
    Open sFile For Input As iHandle
    If Err.Number <> 0 Then
        MsgBox "Failed to open " & sFile & vbCrLf & Err.Description
        Exit Sub
    End If
   
    Do While Not EOF(iHandle)
   
        Line Input #iHandle, sLine
       
        'sFirst holds the value sFirst(0) holds everything before |, sFirst(1) holds everything after
        sFirst = Split(sLine, "|")
       
        'sNext holds everything delimited by a -, so second element is Row number
        sNext = Split(sLine, "-")
       
        iRowNumber = Val(Right(sNext(1), 1))
        iBinNumber = Val(Right(Left$(sNext(2), 4), 1))
       
        'Given the rownumber, we find out start textbox position
        iStart = ((iRowNumber - 1) * NO_OF_BINS) + 1
        Debug.Print "Setting text" & iStart + iBinNumber - 1 & " to " & sFirst(1)
' adding text
        Me("text" & iStart + iBinNumber - 1).Value = sFirst(1)
' adding images
        Me("image" & iStart + iBinNumber - 1).Picture = sFirst(2)

    Loop
   
    Close #iHandle

End Sub

Private Sub Form_Current()
  Me.myPointer = 0
End Sub

Private Sub noData_Click()
Dim x
x = nextText("No Data")
End Sub

Private Sub selCBO_Click()
Dim x

x = nextText(Me.selCBO)
End Sub
Private Function nextText(myString As String) As Boolean
  Dim myTxt As Control
Dim y As Integer

  If Me.myPointer < 8 Then
    nextText = True
  Else
    nextText = False
    MsgBox "you have filled all 8 fields now. What shall happen in this case ?"
    Exit Function
  End If
  Me.myPointer = Me.myPointer + 1 ' this switches to the next control
  Set myTxt = Me.Controls("Text" & Me.myPointer)
  myTxt = myString
 
Select Case Me.Frame33
 Case 1
 Me.Controls("image" & Me.myPointer).Picture = "C:\EE_BETA\WASHER.BMP"
 Case 2
  Me.Controls("image" & Me.myPointer).Picture = "C:\EE_BETA\HEXBOLT.BMP"
 Case 3
 Me.Controls("image" & Me.myPointer).Picture = "C:\EE_BETA\HEXNUT.BMP"

End Select
 
 
 
 
 
 
  nextText = True
End Function



'   Module information ==============================
Option Compare Database
Option Explicit

Type FILE_INFO
    lStructSize As Long
    hwndOwner As Long
    hInstance As Long
    strFilter As String
    strCustomFilter As String
    nMaxCustFilter As Long
    nFilterIndex As Long
    sFile As String
    nMaxFile As Long
    sFileTitle As String
    nMaxFileTitle As Long
    strInitialDir As String
    strTitle As String
    flags As Long
    nFileOffset As Integer
    nFileExtension As Integer
    strDefExt As String
    lCustData As Long
    lpfnHook As Long
    lpTemplateName As String
End Type

Declare Function GetOpenFileName Lib "comdlg32.dll" Alias "GetOpenFileNameA" (OFN As FILE_INFO) As Boolean

Public Function GetCFGFile() As String
   
    Dim OFN As FILE_INFO
    Dim sFileName As String
    Dim sFileTitle As String
    Dim sFilter As String
   
   
    On Error Resume Next

    'Default return value
    GetCFGFile = ""
   
    ' Allocate string space for the returned strings.
    sFileName = String(256, 0)
    sFileTitle = String(256, 0)
    sFilter = "All Files (*.*)" & vbNullChar & "*.*" & vbNullChar
   
    ' Set up the data structure before you call the function
    With OFN
        .lStructSize = Len(OFN)
        .hwndOwner = Application.hWndAccessApp
        .strFilter = sFilter
        .nFilterIndex = 3
        .sFile = sFileName
        .nMaxFile = Len(sFileName)
        .sFileTitle = sFileTitle
        .nMaxFileTitle = Len(sFileTitle)
        .strTitle = "Config File"
        .flags = &H80000
        .strDefExt = "cfg"
        .strInitialDir = "c:\"
        .hInstance = 0
        .strCustomFilter = ""
        .nMaxCustFilter = 0
        .lpfnHook = 0
        .strCustomFilter = String(255, 0)
        .nMaxCustFilter = 255
    End With

    GetOpenFileName OFN
   
    GetCFGFile = Replace(OFN.sFile, vbNullChar, "")

End Function



Basically Creating an assocation between textboxes and images.....
Creating text files that will be loaded intot a database via code.

I have an option box(s) on the form that MUST be checked in order to bring in the correct image to description.
Eventually, I will be adding a relationship between another variable(CustManufactureNumbers) that will grab a Picture in association with a CustManufactureNumbers.

Thus the reason for this question...
The bloating of the data base is a HUGE problem.
I only imported 6,000 images and reached the 2gig limit within a matter of minutes.
Each image about 40k.

I have the potential to maybe store 28,000+  images.
I know storing in ONE database probably will not happen.
Which is why now I'm forcing the user to choose an image selection. Instead of the program doing the choosing.

Is Blobing still a good choice ?

Thanks to all.
fordraiders

Yeah, but what if he needs to just access the file. If it is stored as a blob, he will always need to extract the file unless there is a way to remove the file out of the db and convert  it back to a jpg without using the client application. Plus, I think that loading the file from a file system might be quicker that translating the blob to an image. I was considering blobs (for SQL SERVER 2000) a while ago and everyone said stay away.

blobs is an excellent choice.

If you get properties on the folder that contains your images, how many bytes are we talking ??

28000 * 50k each jpeg  = approx 140 meg, blobs get stored exact byte size, not OLE bloating which is caused by Access creating a bitmap duplicate of any image file inserted into an OLE field.

Alan

Another issue to consider is that if you need to upgrade/upsize your DB to SQL Server. I wonder if the blob fields will translate from Access to SQL perfectly. Either way, if you use the blob, I would still have a backup of the orginal file.

Below is what I am used to hearing about blobs. The comes right from Microsoft.

Following is the tip cut from MSDN regarding to the handling of  text and
image  :

It’s from the article Top Ten Tips Accessing SQL Through ADO and ASP

Tip 3: Avoid Blobs
Blobs (Binary Large Objects) are stored in SQL Server as either text or
image datatypes. SQL Server does not store the Blob data on the data page
along with the other fields in the row. Instead, SQL Server maintains a
pointer to the Blob data. The data itself is stored in 2KB pages linked
through 16-bit text pointers, meaning that there are really about 1800 bytes
available for actual data storage if the column is not NULL. If the column
is explicitly set to NULL, the storage size will be 0, since there is no
need for the text pointers. This essentially means that storing data in
Blobs will increase your storage requirements in 2KB increments.
While storage size alone may not be a deterrent, there are also functional
limitations with Blobs. For example, if you use a WHERE clause to search on
a text column, you are limited to using the LIKE operator. This can be a
very time consuming and will add a lot of overhead to your application.
Also, when you’re working with large quantities of data, it may become
necessary to read the data in chunks, rather than pulling it out of a column
all at once.
Before using a Blob field, consider the alternatives. If you want to store
images in the database, you may find it more appropriate to store the images
outside of the database, and simply maintain URLs that point to the images
within the table. If you are storing lots of text data, you may find that
rather than using a Blob field, you can denormalize the table and break the
data into varchar(255) fields.
If you still require a Blob field despite the storage overhead and
limitations in functionality, there are a few caveats. If you’re using a
forward-only cursor (the default), you should retrieve the Blob fields from
left to right, and to the right of any scalar values you include in your
SELECT statement. If your underlying table definition looked like this
CREATE TABLE MyTABLE(
Field1        Identity(0, 1),
Field2        Text,
Field3        Int,
Field4        Image
)
retrieve your records using the following SELECT statement:
SELECT Field3, Field2, Field4 FROM MyTable
There is another point to be aware of concerning presentation. If you will
be retrieving images from your database through ASP, you will need to
manipulate the HTTP header information. If you were to retrieve a GIF image
from the database, you’d need to first clear out any existing HTTP header
information, then set the ContentType to Image/Gif. See the article
“Delivering Web Images from SQL Server,” by Scott Stanfield (MIND, July
1998), for a complete discussion of how to do this effectively.
To put additional text on the returned page, you need to create a separate
page to host the image. Figure 8, RetrieveImage.asp, demonstrates how you
can retrieve an image from Pubs. Notice how this page doesn’t write any text
with the Response object. Since I would like to provide some text with the
image, I created an additional page, ShowImage.asp (see Figure 9). Another
page is necessary because once you have set Response.ContentType to
Image/Gif, you cannot write text to the ASP page. ShowImage.asp actually
displays the image by referring to the RetrieveImage.asp in the IMAGE tag’s
SRC argument.
<%
    Option Explicit
    Dim cnnPubs
    Dim rstPub_Info

    ‘ Clear existing HTTP header info
    Response.Expires = 0
    Response.Buffer = TRUE
    Response.Clear

    ‘ Set the HTTP header to an image type.
    Response.ContentType = “image/gif”

    Set cnnPubs = Server.CreateObject(“ADODB.Connection”)

    cnnPubs.Open “pubs”, “sa”

    Set rstPub_Info = cnnPubs.Execute(“SELECT logo FROM pub_info WHERE _
                                       pub_id=’1389'”)

    Response.BinaryWrite rstPub_Info(“logo”)
    Response.End
%>
Figure 8 RetrieveImage.asp
<HTML>
<HEAD>
<TITLE>Show the Image</TITLE>
</HEAD>
<BODY>
<H2>Presenting the Logo:</H2>
    <!— This page contains a link to the image so that you can display
    the text. The RetrieveImage.asp page won’t allow you to write out text
    since you have set the ContentType to image/gif” —>

    <IMG SRC=”RetrieveImage.asp”>

</BODY>
</HTML>
Figure 9 ShowImage.asp

This two-page trick comes in handy for operations such as providing dynamic
banners while minimizing work for your server. Simply create a static HTML
page, then reference the ASP page through your IMG tag. The ASP page would
be responsible for choosing which banner to display. Figure 10 shows the
output of ShowImage.asp.

Hi WebsiteNation

Have no probs with blobs and SQL server here, been doing it for some time now.
MS SharePoint stores shared files as Blobs in a SQL catalogue.


heres a script for one of my tables that contain pics
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tbl_Customers_Pics]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tbl_Customers_Pics]
GO

CREATE TABLE [dbo].[tbl_Customers_Pics] (
      [ID] [int] IDENTITY (1, 1) NOT NULL ,
      [CusID] [int] NULL ,
      [sFilename] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [sFileExtension] [nvarchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [oPicture] [image] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO



script for populating an image control on form in an ADP
Dim sPath As String
sPath = putBLOBInFile(Me.CusID)  
Dim sPath As String
Me.ImageX.Picture = sPath


script for updating the SQL catalog with images
  Dim sPath As String
 
  If Nz(Me.ImageX.Picture, "") = "" Then
    Exit Sub
  End If
 
  sPath = Me.ImageX.Picture
  ImgCusBLOB.Picture = getBLOBFromFile(Me.CusID, sPath)


Alan

o.k.
Is there a simple method to IMPORT 28,000 jpg files into the  blob database ?
Interesting reading bythe way.
Thanks
fordraiders

Hi WebsiteNation

LOL - pet subject here...

I use the Response.Binary write method with  the proxy page on my site to extract all images on the site from an MDB
The reason I did this was because the images I was storing were scanned documents, which I didnt want left lying around in some filesystem.

Also straight from Microsoft:
http://www.microsoft.com/resources/documentation/sql/2000/all/reskit/en-us/part3/c1161.mspx
Database or File System
Web applications often have graphics associated with tabular data. For example, real estate Web sites typically include photographs of homes for sale. On company intranet sites, client databases can contain image files of client products. For such applications, a common design question involves whether the images should be stored in the database or in a file system. In most cases, the best choice is to store the images in the database together with the other data.

Storing the images in a database is the better choice if the application in which the images will be used count on the benefits of a database system. The benefits of storing the images in the database include:

• Scalability. Although file systems are designed to handle a large number of objects of varying sizes, file systems usually are not optimized for a huge number (tens of millions) of small files. Database systems are optimized for such cases.

Availability. SQL Server has availability features that extend beyond those provided by the file system.

• SQL Server replication is a set of solutions that allow you to copy, distribute, and potentially modify data in a distributed environment.
 
• Log shipping provides a way of keeping a stand-by copy of a database in case the primary system fails.


Alan
 
 

yes it easy to pack 28000 images into a database catalogue, may I suggest we start with the table design.

tblPics

ID - AutoNumber - PK
Filename - text - 255
FileExtension - text - 4
Picture - OLE Object
Size - Number
Type - Text - 50
Created - DateTime

Do you have sub-folders in your images folder?

Alan

When you have the table done create a stored query called spJetPackBlob

PARAMETERS pFileName Text ( 255 ), pFileType Text ( 255 ), pDateCreated DateTime, pDateLastAccessed DateTime, pFileSize Long, pFileExtension Text ( 255 ), pFileBinary LongBinary;
INSERT INTO tblPics ( Filename, FileExtension, Picture, [Size], Type, Created )
SELECT [pFilename] AS Expr1, [pFileExtension] AS Expr2, [pFileBinary] AS Expr3, [pFileSize] AS Expr4, [pFileType] AS Expr5, [pDateCreated] AS Expr6;



Alan

From a security standpoint, it is much easier. I'll have to experiment with this. The only problem I have is that one of the DBs I use is being limited to a 50 MB quota. So, I'll have to buy more space :)
Steve

one sub image subfolders.
C:\iCart\Pics


Alan,
I'm not using sql or msde.
Just access 2000
I guess "stored query" as "Standard Query"  ??

Thanks

yes thats right I try to create  parameter queries that are ready for upsizing so I adopt sql naming conventions.

fordraiders,

sorry I forgot to mention the references
Microsoft Scripting Runtime
ActiveX Data Objects 2.6

ADODB.Stream is not supported in ADO versions prior to 2.6
You can get 2.6 from here:
Microsoft Data Access Components (MDAC) 2.6 SDK
http://www.microsoft.com/downloads/details.aspx?familyid=cf5bf48d-9bbb-4ca2-9b03-4ee000db37ff&displaylang=en
There are later versions but this one has 13 meg of help and samples with it.


Good luck
Gotta crash out here now.


Alan

ps... it will take some time to append 28000 jpegs, suggest you incorporate a counter or a progress meter so you know where you are up to in the entire operation

Alan,
I'm sure the code will work but again, I'm on Access 2000 not sql

me too this works in ac2000

Alan,
Do I need to create a table or does the code create one for me ?
The appears to demand a table in structure already ?

fordraiders

hi fordraiders


yes create this table


tblPics

ID - AutoNumber - PK
Filename - text - 255
FileExtension - text - 4
Picture - OLE Object
Size - Number
Type - Text - 50
Created - DateTime

OOPS,
Sorry, I had my table backwards...
I forgot to create the first table specified...
I see the append procedure now...

Thanks
Its pumping...

forgot to put the meter though.....
Will let you know,
Thanks for being up late....

Alan,
Worked great!
One question though.

Is there a way to only use the filename
.Parameters.Append .CreateParameter("pFileName", adVarChar, adParamInput, 255, sFileName) <---
But do not add the extension has it is  specfied later in the table appending

.Parameters.Append .CreateParameter("pFileExtension", adVarChar, adParamInput, 255, sFileExtension) <------

That way I can also use the  "FileName" field in the table "tblPics" as another key..
Thanks

yes just modify your parameter query not to expect that param and rem that line out

or change it to this:
.Parameters.Append .CreateParameter("pFileExtension", adVarChar, adParamInput, 255, "")

I wouldnt make the filename a unique index, the cool thing about blobs is that you can many files of the same name in one table, with a unique ID. There are good reasons for the Type field and the Extension field, you will find later that it will be handy to have this info when doing bulk extractions.

How long did it take?
Did your db only increase in size equivalent to the collective size of the data you were appending?




Alan

Hi Ford


Download Sample Access 2000 mdb demonstrating appending bulk jpegs to a Jet mdb as blobs using the ADO 2.6 stream (36 kb)
http://www26.brinkster.com/alzowze/blobs.asp

Also has an update meter.

Alan

your link will not let me in....

forbidden....

took about 3 minutes to import

hmmm....

I probably had it locked.

Have modified the link it is now on the right hand side of the blobs page, please try again
http://www26.brinkster.com/alzowze/blobs.asp


Alan

got it

Thanks

Re: took about 3 minutes to import

That not too painfull for 28 thousand files.   :)

What about the bloat factor, compared to the total bytes of images imported?
Allowing for the other fields being populated did you see any significant bloat beyond the total bytes of images?


Alan

Well,
The bloating is much better..
I could not get 28,000 storing as a straight ole object.
Clipped out at 6,000 . Hit the 2gig limit..

28,610 jpg using the Blob method. 225,944 kb

Using this method allows for plenty of jpg's .
besides it also allows my users to load jpgs when ever possible.

I figure I will store approx 100,000 jpg by the time i'm done...
which should put me at 1.2gb  .  So plenty of space..

 Thanks
again

got to hit the sack....
Thanks for all the help.
Very grateful !!!
Alot to ponder and read and learn....

Cool !!!

thanks for the points.

Just finished developing a procedure to display thumbnails of images stored as blobs in a Microsoft web browser control embedded in an access form. You can choose the number of columns you want from a dropdown and when you click on the thumbnail browser navigates to display image full size. Also has controltip text showing image properties.


Will try to post a sample to my site in next day or so.

Alan

Alan,
Consider this,

Lets say, I have a listbox.
In this listbox I have approx 200 numbers.
The numbers have an association key to image files.

For argument sake, I select all items in listbox, or select maybe 10.

Of the ones I select. these only get published to the browser, dependant on the number of columns I choose display in each row.
Your example is very close to what my application in development is.

Thanks
good stuff
fordraiders

keep me posted,
staspe@insightbb.com

alan,
Can you open the blobs again...
System crash ......lost database

Thanks
fordraiders

Thanks