Solved

Import Ole  jpg  files into a  database table

Posted on 2004-09-28
37
3,817 Views
Last Modified: 2012-05-05
Access 2000

I have 28,300 .jpg files in a folder    c:\iCartEnt\Pics


I have a database called  "MyData"
I have a table called   "MyPics"

I need these jpg file imported into the database  table...?

Is this possible ?

Thanks
fordraiders
0
Comment
Question by:fordraiders
  • 16
  • 16
  • 4
  • +1
37 Comments
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12174379
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
0
 
LVL 1

Expert Comment

by:WebsiteNation
ID: 12174520
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
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 12176731
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

0
 
LVL 3

Author Comment

by:fordraiders
ID: 12179608
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




0
 
LVL 1

Expert Comment

by:WebsiteNation
ID: 12179688
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.
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 12179751
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



0
 
LVL 1

Expert Comment

by:WebsiteNation
ID: 12179908
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.
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 12179929
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

0
 
LVL 3

Author Comment

by:fordraiders
ID: 12179946
o.k.
Is there a simple method to IMPORT 28,000 jpg files into the  blob database ?
Interesting reading bythe way.
Thanks
fordraiders
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 12180075
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
 
 
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 12180189
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

0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 12180222
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
0
 
LVL 1

Expert Comment

by:WebsiteNation
ID: 12180265
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
0
 
LVL 3

Author Comment

by:fordraiders
ID: 12180372
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
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 12180398
yes thats right I try to create  parameter queries that are ready for upsizing so I adopt sql naming conventions.

0
 
LVL 26

Accepted Solution

by:
Alan Warren earned 500 total points
ID: 12180847
Perhaps test this on folder containing a few files only first.



Private Sub Command0_Click()

  Dim objFso As Scripting.FileSystemObject
  Dim objFolder As Folder
  Dim objFile As Scripting.File
  Dim objFiles As Scripting.Files
 
  Dim objStream As ADODB.Stream
  Dim objCmd As ADODB.Command
 
  Dim sFolderPath As String
  Dim sFileName As String
  Dim sFileType  As String
  Dim sFileExtension As String
 
  Dim dDateCreated As Date
  Dim dDateLastAccessed As Date
 
  Dim lFileSize As Long
 
  Dim oFileBinary
 
  sFolderPath = "C:\Documents and Settings\Alan\My Documents\My Pictures\Als\"  ' Your folder path
 
 
  Set objFso = New Scripting.FileSystemObject
  Set objFolder = objFso.GetFolder(sFolderPath)
  Set objFiles = objFolder.Files
 
 
  For Each objFile In objFiles
   
    If objFile.Type = "JPEG Image" Then
      sFilePath = sFolderPath & objFile.Name
      Set objFile = objFso.GetFile(sFilePath)
     
      sFileExtension = objFso.GetExtensionName(sFilePath)
     
      With objFile
        sFileName = .Name
        sFileType = .Type
        dDateCreated = .DateCreated
        dDateLastAccessed = .DateLastAccessed
        lFileSize = .Size
      End With
     
      Set objStream = CreateObject("ADODB.Stream")
      objStream.Type = adTypeBinary
      objStream.Open
      objStream.LoadFromFile sFilePath
      oFileBinary = objStream.Read
      objStream.Close
      Set objStream = Nothing
     
      Set cmd = CreateObject("ADODB.Command")
      With cmd
        .CommandText = "spJetPackBlob"
        .CommandType = adCmdStoredProc
        .Parameters.Append .CreateParameter("pFileName", adVarChar, adParamInput, 255, sFileName)
        .Parameters.Append .CreateParameter("pFileType", adVarChar, adParamInput, 255, sFileType)
        .Parameters.Append .CreateParameter("pDateCreated", adDBTimeStamp, adParamInput, 8, dDateCreated)
        .Parameters.Append .CreateParameter("pDateLastAccessed", adDBTimeStamp, adParamInput, 8, dDateLastAccessed)
        .Parameters.Append .CreateParameter("pFileSize", adInteger, adParamInput, 4, lFileSize)
        .Parameters.Append .CreateParameter("pFileExtension", adVarChar, adParamInput, 255, sFileExtension)
        .Parameters.Append .CreateParameter("pFileBinary", adLongVarBinary, adParamInput, 2147483647, oFileBinary)
        Set .ActiveConnection = CurrentProject.Connection
        .Execute , , adExecuteNoRecords
      End With
    End If
  Next
 
 
  On Error Resume Next
  Set objFso = Nothing
  Set objFolder = Nothing
  Set objFile = Nothing
  Set objFiles = Nothing
 
  Set objStream = Nothing
  Set objCmd = Nothing


End Sub



Alan
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 12181021
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

0
 
LVL 3

Author Comment

by:fordraiders
ID: 12182276
Alan,
I'm sure the code will work but again, I'm on Access 2000 not sql
0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 26

Expert Comment

by:Alan Warren
ID: 12186175
me too this works in ac2000
0
 
LVL 3

Author Comment

by:fordraiders
ID: 12186724
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
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 12186739
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
0
 
LVL 3

Author Comment

by:fordraiders
ID: 12186781
OOPS,
Sorry, I had my table backwards...
I forgot to create the first table specified...
I see the append procedure now...

Thanks
Its pumping...
0
 
LVL 3

Author Comment

by:fordraiders
ID: 12186784
forgot to put the meter though.....
Will let you know,
Thanks for being up late....
0
 
LVL 3

Author Comment

by:fordraiders
ID: 12186885
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


0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 12186905
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
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 12186995
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
0
 
LVL 3

Author Comment

by:fordraiders
ID: 12187039
your link will not let me in....

forbidden....

took about 3 minutes to import
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 12187070
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
0
 
LVL 3

Author Comment

by:fordraiders
ID: 12187073
got it

Thanks
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 12187098
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

0
 
LVL 3

Author Comment

by:fordraiders
ID: 12187139
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
0
 
LVL 3

Author Comment

by:fordraiders
ID: 12187158
got to hit the sack....
Thanks for all the help.
Very grateful !!!
Alot to ponder and read and learn....
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 12187165
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
0
 
LVL 3

Author Comment

by:fordraiders
ID: 12190546
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



0
 
LVL 3

Author Comment

by:fordraiders
ID: 12190556
keep me posted,
staspe@insightbb.com
0
 
LVL 3

Author Comment

by:fordraiders
ID: 14714524
alan,
Can you open the blobs again...
System crash ......lost database

Thanks
fordraiders
0
 
LVL 3

Author Comment

by:fordraiders
ID: 14714616
Thanks
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

707 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

18 Experts available now in Live!

Get 1:1 Help Now