Contact US

Question

Attachment data type makes table too big

LARiot (Programmer)

(OP)

1 Oct 12 15:57

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 (Programmer) · Answer 1 · 2012-10-01 16:03:57

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 (Programmer) · Answer 2 · 2012-10-01 16:43:11

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 (TechnicalUser) · Answer 3 · 2012-10-01 16:45:28

Quote:

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.

Quote:

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 (Programmer) · Answer 4 · 2012-10-01 16:49:58

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 (Programmer) · Answer 5 · 2012-10-01 16:56:42

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 (TechnicalUser) · Answer 6 · 2012-10-01 17:11:09

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 (Programmer) · Answer 7 · 2012-10-01 17:19:54

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 (TechnicalUser) · Answer 8 · 2012-10-01 20:52:29

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/20...

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 (Programmer) · Answer 9 · 2012-10-02 14:47:45

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 (TechnicalUser) · Answer 10 · 2012-10-02 14:56:13

RE: Attachment data type makes table too big

MajP (TechnicalUser) 2 Oct 12 14:56

Yes.

LARiot (Programmer) · Answer 11 · 2012-10-02 15:14:36

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 (Programmer) · Answer 12 · 2012-10-02 15:19:39

*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 (Programmer) · Answer 13 · 2012-10-02 20:00:01

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 (TechnicalUser) · Answer 14 · 2012-10-02 20:11:00

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 (Programmer) · Answer 15 · 2012-10-04 14:34:04

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 (TechnicalUser) · Answer 16 · 2012-10-04 15:37:20

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 (MIS) · Answer 17 · 2012-10-04 15:54:25

And what about the Picture property ?

Hope This Helps, PH.
FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

MajP (TechnicalUser) · Answer 18 · 2012-10-04 17:20:59

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 (Programmer) · Answer 19 · 2012-10-04 17:48:07

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:

CODE --> VBA

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

LARiot (Programmer) · Answer 20 · 2012-10-04 18:27:42

BTW, thanks again for all the help, especially MajP. The result of this that the database went from 53MB (with only test data) down to 2.4 MB. Without this modification it would've hit the 2GB limit in no time.

-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

MeldrethMan (TechnicalUser) · Answer 21 · 2012-10-26 05:47:47

Hi LARiot

This looks just what I need to solve the same problem but I'm getting errors using your code, first for me.ImagePath (Method or datamember not found) then for With Application.FileDialog(msoFileDialogOpen), (-2147467259 Automation error).

Bit behind you on expertise sorry, and must be missing some things that are obvious. It would be a great help if you could explain the steps in a bit more detail. I got as far as inserting an unbound image into my form, then adding your double-click procedure.

MajP (TechnicalUser) · Answer 22 · 2012-10-26 07:15:46

I recommend you start a new thread and be specific on what you want and how you want it to work. LARiot had some very unique requirements that complicated the design. He was trying to change the bound field of an image control at run-time. You may need little or no code depending on what you want.

MeldrethMan (TechnicalUser) · Answer 23 · 2012-10-26 09:03:47

OK, thanks. I did start a thread but in the Forms forum, then found LARiot's here. In case it should be here I'll start a new one with exact details of what I need.

LARiot (Programmer) · Answer 24 · 2012-10-26 19:04:24

Imagepath is a field/column in my table. Since the form's record source is pointing to that table, I can use it in my code.

-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

Come Join Us!

Posting Guidelines

Attachment data type makes table too big

Attachment data type makes table too big

Attachment data type makes table too big

RE: Attachment data type makes table too big

RE: Attachment data type makes table too big

RE: Attachment data type makes table too big

Quote:

Quote:

RE: Attachment data type makes table too big

RE: Attachment data type makes table too big

RE: Attachment data type makes table too big

RE: Attachment data type makes table too big

RE: Attachment data type makes table too big

RE: Attachment data type makes table too big

RE: Attachment data type makes table too big

RE: Attachment data type makes table too big

RE: Attachment data type makes table too big

RE: Attachment data type makes table too big

RE: Attachment data type makes table too big

RE: Attachment data type makes table too big

RE: Attachment data type makes table too big

RE: Attachment data type makes table too big

RE: Attachment data type makes table too big

RE: Attachment data type makes table too big

CODE --> VBA

RE: Attachment data type makes table too big

RE: Attachment data type makes table too big

RE: Attachment data type makes table too big

RE: Attachment data type makes table too big

RE: Attachment data type makes table too big

Red Flag This Post

Red Flag Submitted

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Join Tek-Tips^® Today!

Contact US

Come Join Us!

Posting Guidelines

Attachment data type makes table too big

Attachment data type makes table too big

Attachment data type makes table too big

RE: Attachment data type makes table too big

RE: Attachment data type makes table too big

RE: Attachment data type makes table too big

Quote:

Quote:

RE: Attachment data type makes table too big

RE: Attachment data type makes table too big

RE: Attachment data type makes table too big

RE: Attachment data type makes table too big

RE: Attachment data type makes table too big

RE: Attachment data type makes table too big

RE: Attachment data type makes table too big

RE: Attachment data type makes table too big

RE: Attachment data type makes table too big

RE: Attachment data type makes table too big

RE: Attachment data type makes table too big

RE: Attachment data type makes table too big

RE: Attachment data type makes table too big

RE: Attachment data type makes table too big

RE: Attachment data type makes table too big

RE: Attachment data type makes table too big

CODE --> VBA

RE: Attachment data type makes table too big

RE: Attachment data type makes table too big

RE: Attachment data type makes table too big

RE: Attachment data type makes table too big

RE: Attachment data type makes table too big

Red Flag This Post

Red Flag Submitted

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Join Tek-Tips® Today!

Join Tek-Tips^® Today!