Report runs out of Memory with .jpg images

[zdas04]

I've got a database with 1400 records in it. Each record is associated with a .jpg file whose file name is the same as the primary key of the database. These .jpg files are really small (about 30k/picture) vector drawings from Macromedia Freehand.

Using some code (see below) I got from microsoft.com I can display the drawings on individual report and form pages very effectively.

The problem comes with a report that is five pages per primary key. The second page is the drawing and it looks like it is working really well until it gets to record 1170 when I get a message "2114 Microsoft Office Access doesn't support the format of the file C:3004512345.jpg or the file is too large. Try converting the file to BMP or GIF format". Then I have to say "OK" on that record and each of the next 5,000 records to get to the end and display the report (I did it once to see if it would really make me hit that button 4,000 times, after that I ALT-CNTL-DEL out and crash Access).

The report opens, and you can step through the first few hundred records very quickly. Does anyone see anything in the code that could prevent this memory contamination?

Thanks

David

Option Compare Database
Option Explicit

Public Function DisplayImage(ctlImageControl As Control, strImagePath As Variant) As String
On Error GoTo Err_DisplayImage

Dim strResult As String
Dim strDatabasePath As String
Dim intSlashLocation As Integer

With ctlImageControl
    If IsNull(strImagePath) Then
        .Visible = False
        strResult = ""
    Else
        If InStr(1, strImagePath, "\") = 0 Then
            ' Path is relative
            strDatabasePath = CurrentProject.FullName
            intSlashLocation = InStrRev(strDatabasePath, "\", Len(strDatabasePath))
            strDatabasePath = Left(strDatabasePath, intSlashLocation)
            strImagePath = strDatabasePath & strImagePath
        End If
        .Visible = True
        .Picture = strImagePath
        strResult = "Image found and displayed."
    End If
End With
    
Exit_DisplayImage:
    DisplayImage = strResult
    Exit Function

Err_DisplayImage:
    Select Case Err.Number
        Case 2220       ' Can't find the picture.
            ctlImageControl.Visible = False
            strResult = "Can't find image in the specified name."
            Resume Exit_DisplayImage:
        Case Else       ' Some other error.
            MsgBox Err.Number & " " & Err.Description
            strResult = "An error occurred displaying image."
            Resume Exit_DisplayImage:
    End Select
End Function

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)

    Dim outFilename As String
    outFilename = "c:\BP_Spcc\CombinedData\" + API + ".jpg"
    
    Me!txtImageNote = DisplayImage(Me!ImageFrame, outFilename)

End Sub

 

[mp9]

I don't expect it will help in the slightest, but your code can be streamlined slightly, thus (untested); plus use a String for strImagePath in the declaration of DisplayImage, rather than a Variant, uses less memory:

Option Compare Database
Option Explicit

Public Function DisplayImage(ctlImageControl As Control, strImagePath As [b][u]String[/u][/b]) As String
On Error GoTo Err_DisplayImage

With ctlImageControl
    .Visible = Not IsNull(strImagePath)
    If .Visible Then
        If InStr(1, strImagePath, "\") = 0 Then
            ' Path is relative
            strImagePath = CurrentProject.Path & "\" & strImagePath
        End If
        .Picture = strImagePath
        DisplayImage = "Image found and displayed."
    End If
End With
    
Exit_DisplayImage:
    Exit Function

Err_DisplayImage:
    Select Case Err.Number
        Case 2220       ' Can't find the picture.
            ctlImageControl.Visible = False
            DisplayImage = "Can't find image in the specified name."
            Resume Exit_DisplayImage:
        Case Else       ' Some other error.
            MsgBox Err.Number & " " & Err.Description
            DisplayImage = "An error occurred displaying image."
            Resume Exit_DisplayImage:
    End Select
End Function

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)

    Dim outFilename As String
    outFilename = "c:\BP_Spcc\CombinedData\" + API + ".jpg"
    
    Me!txtImageNote = DisplayImage(Me!ImageFrame, outFilename)

End Sub

http://www22.brinkster.com/accessory/

 

[zdas04]

Thanks for the idea mp9, but it stopped 55 pages sooner with the "String" than it did with the "Variant".

David

 

[kaosnhavoc]

I encountered the same problem - it appears that Access when writing detail report lines never releases memory acquired to copy the image when it is preparing the page. I found on the average that the amount of virtual memory acquired by Access to print a single image was about 10-20 times the original image size which is partially Access's memory allocation algorithms and partly plain dumb overhead. To display a 2MB jpg image triggered a 20MB expansion in VM size. Once you close the report, the memory is released so it isn't a memory leak but it certainly doesn't handle denial of requests for additional memory at all properly.

In any case, the problem can be avoided by scripting the printing of the document so that it is printed a page at time using ranges 1-1 , 2-2, 3-3, ... n-n or in your case 2-5, 6-10, 11-15, ..., n-n+5 until all pages have printed.

Print preview will still produce memory problems but I needed hard copy. Micro$oft claim to have fixed this in Access 2007 but I wouldn't bet on it.

I'm pretty sure that forcing Access to release the image object and re-allocating the image object on each record will not solve the problem as most of the additional memory requests are probably not tied to the image object.

 

[zdas04]

Thanks for that. I really wanted the automatic page n of m capabilities, but I think I'll just change my numbering scheme to print the report in 10 chunks each with it's own page number prefix.

When is Access 2007 supposed to be a real product?

David

 

[mp9]

If you need a "page n of m" you can fudge it, even if opening the report a page at a time m times. You the openReport method's OpenArgs parameter to pass in a "page n of m" string and then pick that up in a text box in the report footer.

http://www22.brinkster.com/accessory/