what would scooby do

folks.. simple database, one line of text and a field to import graphics files - jpegs, have stored 23Megs of graphics and the access DB file is 2GB in size

What's going wrong ?

1) Is access pants for storing pictures...
2) Can it be optimized ?
3) Should we use something better ??

ta very much in advance...

Mickle

It's probably the method you've used to store the picture in the database in the first place. A compact and repair should sort the size out back to the normal size it should be.

Code we used to use is:

'************************************************* *************
' FUNCTION: WriteBLOB()
'
' PURPOSE:
' Writes BLOB information stored in the specified table and field
' to the specified disk file.
'
' PREREQUISITES:
' The specified table with the memo field containing the binary
' data must be opened in Access Basic code and the correct record
' navigated to prior to calling the WriteBLOB() function.
'
' ARGUMENTS:
' T - The table object containing the binary information.
' sField - The memo field in table T containing the binary
' information to write.
' Destination - The path and filename to write the binary
' information to.
'
' RETURN:
' The number of bytes written to the destination file.
'************************************************* *************

Function WriteBLOB(t As ADODB.Recordset, sField As String, Destination As String)
Dim NumBlocks As Integer, DestFile As Integer, i As Integer
Dim FileLength As Long, LeftOver As Long
Dim FileData As String
Dim RetVal As Variant

On Error GoTo Err_WriteBLOB

' Get the size of the field.
FileLength = t.Fields(sField).ActualSize
If FileLength = 0 Then
WriteBLOB = 0
Exit Function
End If

' Calculate number of blocks to write and leftover bytes.
NumBlocks = FileLength \ Blocksize
LeftOver = FileLength Mod Blocksize

' Remove any existing destination file.
DestFile = FreeFile
Open Destination For Binary As DestFile
Close DestFile

' Open the destination file.
Open Destination For Binary As DestFile

' Write the leftover data to the output file.
FileData = t.Fields(sField).GetChunk(LeftOver) 'ADO
Put DestFile, , FileData

' Write the remaining blocks of data to the output file.
For i = 1 To NumBlocks
' Reads a chunk and writes it to output file.
FileData = t.Fields(sField).GetChunk(Blocksize) 'ADO
Put DestFile, , FileData
Next i

' Terminates function
Close DestFile
WriteBLOB = FileLength

Exit Function
Err_WriteBLOB:
WriteBLOB = -Err
Exit Function
End Function

We now use a 3rd party control that saves BLOB's directly to the DB.


[Edited by Mickle - 9/18/2003 4:43:21 PM]

Mr.M

rather than storing the image, why don't you store the URL to the image.

chiark

at a guess, you've got 'em defined as an ole object? In which case it puts a ****load of ole wrapper stuff in there.

Try using a different type and writing the data from a file into the database using a module. It's a pain, but I had the same problem...

what would scooby do

It's just for a chap I know - he's no expert (nor am I ) - I'll have a word with him.

It's a single, standalone app with files local so Hyperlink sounds good to me !