INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Attachment data type makes table too big

Attachment data type makes table too big

(OP)
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

RE: Attachment data type makes table too big

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.... smile

Have fun.

---- Andy

RE: Attachment data type makes table too big

(OP)
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

RE: Attachment data type makes table too big


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.

RE: Attachment data type makes table too big

(OP)
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

RE: Attachment data type makes table too big

(OP)
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

RE: Attachment data type makes table too big

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.

RE: Attachment data type makes table too big

(OP)
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

RE: Attachment data type makes table too big

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.

RE: Attachment data type makes table too big

(OP)
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

RE: Attachment data type makes table too big

Yes.

RE: Attachment data type makes table too big

(OP)
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

RE: Attachment data type makes table too big

(OP)
*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

RE: Attachment data type makes table too big

(OP)
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

RE: Attachment data type makes table too big

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"

RE: Attachment data type makes table too big

(OP)
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

RE: Attachment data type makes table too big

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.

RE: Attachment data type makes table too big

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.



RE: Attachment data type makes table too big

(OP)
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

RE: Attachment data type makes table too big

(OP)
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

RE: Attachment data type makes table too big

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.

RE: Attachment data type makes table too big

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.

RE: Attachment data type makes table too big

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.

RE: Attachment data type makes table too big

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

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

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close