Attachment data type makes table too big 1

[LARiot]

Hi. Any help is appreciated.

I have two tables in my database that use the attachment data type as one of the fields/colums. The problem is that every time an attachment is added the size of the database increases by the size of the attachment. This quickly turns the database from a 1 mb file to 50 MB file. The whole point of using attachments (as opposed to OLEDB) is for the database not to increase in size.

Does anyone have an idea why it's increasing so much?

Thanks in advance.

-Nima
"Pluralitas non est ponenda sine necessitate", i.e., "Plurality is not to be posited without necessity" aka K.I.S.S. (Keep It Short and Simple) -- Ockham's Razor

 

[Andrzejek]

If you store 50 MB of (let's say: avi) file in your DB, how much should your DB increase in size? In your estimation.

If you want to have very small DB, save your attachment on the network, and only keep the location and the name of that attachment in simple text field in your DB.

Just my opinion....

Have fun.

---- Andy

 

[LARiot]

Andy: thanks. I think that the only way is to store the path in a text field. The client has a big need for image files it's gonna hit Access' 2GB limit pretty soon. The DB shouldn't increase to more than 20 or 30 mb tops

-Nima
"Pluralitas non est ponenda sine necessitate", i.e., "Plurality is not to be posited without necessity" aka K.I.S.S. (Keep It Short and Simple) -- Ockham's Razor

 

[MajP]

The whole point of using attachments (as opposed to OLEDB) is for the database not to increase in size.

Yes and no. Yes it stores the object in a much more efficient format (pre 2007) without a lot of additional overhead. Some objects are stored in a compressed format. I think .BMPs for example are stored as .JPG so you will see a tremendous improvement. But if you hav 50Mb of jpgs your database will grow 50mb.

The problem is that every time an attachment is added the size of the database increases by the size of the attachment.

I guess you have not been using Access very long. Prior to Access 2007 if our database grew in size equal to the size of our images/ole objects we would have been jumping up and down for joy. The embedded objects would bloat the database from about 3 to 5 times the size of the object. 50mb would turn into 150 to 250mb or more increase.

 

[LARiot]

I've been using Access for over a decade, just never had to deal with so many images. Now I'm going to have to reprogram everything... ack!

-Nima
"Pluralitas non est ponenda sine necessitate", i.e., "Plurality is not to be posited without necessity" aka K.I.S.S. (Keep It Short and Simple) -- Ockham's Razor

 

[LARiot]

Thanks again for all the feedback. It's much appreciated.

-Nima
"Pluralitas non est ponenda sine necessitate", i.e., "Plurality is not to be posited without necessity" aka K.I.S.S. (Keep It Short and Simple) -- Ockham's Razor

 

[MajP]

However, 2007 also provided the bound image control. For some reason there is hardly anything written on this, but it was a pretty big deal. The bound image control allows you to simply store a path in the table and the bound image control will render the image from a path without any coding. May help. The bigger deal is that this works in a continous form, and I know of no way to code that.

 

[LARiot]

Maj, you're a life saver. I'm going to try that out. If it works, that's going to save me a lot of grief.

-Nima
"Pluralitas non est ponenda sine necessitate", i.e., "Plurality is not to be posited without necessity" aka K.I.S.S. (Keep It Short and Simple) -- Ockham's Razor

 

[MajP]

Yeah it amazes me how little is written on this. For example this person claims to be an expert and has written some access books, but is completely wrong in his response

http://en.allexperts.com/q/Using-MS-Access-1440/2010/11/Access-2007-image-control.htm

If it is a bound image control (Access 2007 and later) there is no need to manually load the control. Simply bind it to a stored path and the image will render. Including a continuous form.

 

[LARiot]

MajP:

Thanks, but how do you bind it to a stored path? Is by specifying the control source to be the text field/column in a table?

-Nima
"Pluralitas non est ponenda sine necessitate", i.e., "Plurality is not to be posited without necessity" aka K.I.S.S. (Keep It Short and Simple) -- Ockham's Razor

 

[MajP]

Yes.

 

[LARiot]

Thanks, it works. I can't believe it was so simple. I just have to add the file open code for the user to change the picture in the form which was already there when the control source was an attachment datatype. Definitely worth the fixing the file size problem. Luckily it's in the code of an older database I created. I'll post that in here once it's up and running.

Thanks again MajP. It's posters like you that make tek-tips the best form on the web.

-Nima
"Pluralitas non est ponenda sine necessitate", i.e., "Plurality is not to be posited without necessity" aka K.I.S.S. (Keep It Short and Simple) -- Ockham's Razor

 

[LARiot]

*forum, not form.

-Nima
"Pluralitas non est ponenda sine necessitate", i.e., "Plurality is not to be posited without necessity" aka K.I.S.S. (Keep It Short and Simple) -- Ockham's Razor

 

[LARiot]

I got it to work on one of the images in my database. But the other image on the form, which the user could change by clicking an option button, won't work because vba doesn't let you access an image's control source. I posted this problem in

http://tek-tips.com/viewthread.cfm?qid=1694810

Any ideas on how to circumvent this problem?

Thanks

-Nima
"Pluralitas non est ponenda sine necessitate", i.e., "Plurality is not to be posited without necessity" aka K.I.S.S. (Keep It Short and Simple) -- Ockham's Razor

 

[MajP]

I think, but I need to see Access which I do not have on this machine. I think you need to do something like

dim BndImgCtl as access.boundimagecontrol ' i am guessing here because not sure what object type is called
set bndImgCtl = me.imgCtlName.object 'name of you control
'now you should have the properties
bndImgCtl.controlsource = "fieldName

 

[LARiot]

BTW, I don't see a bound image control. There's an image control and bound object frame. I using the image control.

-Nima
"Pluralitas non est ponenda sine necessitate", i.e., "Plurality is not to be posited without necessity" aka K.I.S.S. (Keep It Short and Simple) -- Ockham's Razor

 

[MajP]

Yes there is a bound object frame, and unbound object frame, and an image control. After 2007 the image control became bound. In the object browser it is simply an "image".
ex:
dim imgCtl as access.image

And as you found out the control does not expose the control source property. Why that is I have no idea.

 

[PHV]

And what about the Picture property ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MajP]

LARiot
You could do what PHV says if you want to go unbounded. And what you describe it sounds like there is no real need to go bounded. If the image control is bound then the picture property does nothing except sets the default value of the picture. The bound value overrides the picture property.

So you could simply go unbounded and use code to load the picture property by setting it equal to the value in another field. Since you are not using a continuous form there is not a lot of need to go bound. In this case going unbound would be the easiest solution.

 

[LARiot]

The picture property wouldn't work because the source control property overrides it. So I just changed the field. Here's the code if anyone else needs it:

Private Sub imgHouse_DblClick(Cancel As Integer)
    Dim varFile As Variant

    varFile = OpenFile("Please select Image file for this Address")
    If Not varFile = Empty Then
        Me.ImagePath = varFile
    End If
    
    Me.imgHouse.Requery
End Sub

Public Function OpenFile(sTitle As String) As Variant
On Error GoTo Err_Handling
    Dim varFileName As Variant
    
    With Application.FileDialog(msoFileDialogOpen)
        .AllowMultiSelect = False
        .Title = sTitle
        .InitialFileName = "C:\Users\Public\Pictures\Sample Pictures\*.jpg"
        
        .Filters.Clear
        .Filters.Add "JPEG Files", "*.jpg"
        .Filters.Add "All Files", "*.*"
        
        If .Show = True Then
            varFileName = .SelectedItems(1)
        End If
    End With
        
    OpenFile = varFileName
    Exit Function
    
Err_Handling:
    Debug.Print Err.Number & " " & Err.Description
    
End Function

-Nima
"Pluralitas non est ponenda sine necessitate", i.e., "Plurality is not to be posited without necessity" aka K.I.S.S. (Keep It Short and Simple) -- Ockham's Razor