Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations wOOdy-Soft on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Attachment data type makes table too big 1

Status
Not open for further replies.
LARiot

LARiot

Programmer
Feb 7, 2007
232
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
 
Sort by date Sort by votes
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote

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

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.
Click to expand...
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.
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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

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.
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
*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
 
Upvote 0 Downvote
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
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
And what about the Picture property ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
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.



 
Upvote 0 Downvote
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: 
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
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

raywood1
  • Locked
  • Question
Reducing a Massive Linked Table
Replies
4
Views
769
dhookom
dhookom
Anthony343242
  • Locked
  • Question
MS ACCESS UNION ALL with Attachment data type 1
2
Replies
29
Views
7K
Anthony343242
Anthony343242
MrStohler
  • Locked
  • Question
Beginner Q: How to separate data from Excel into related tables and maintain relationship? 1
Replies
5
Views
706
SkipVought
S
jjMedrano
  • Locked
  • Question
UNION ALL with Attachment data type 1
Replies
8
Views
626
Andrzejek
Andrzejek
03Explorer
  • Locked
  • Question
Refreshing a linked table (housed on MS SQL Server) now breaks
Replies
3
Views
577
dhookom
dhookom

Part and Inventory Search

Sponsor

Back
Top