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.
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 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
RE: Attachment data type makes table too big
-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 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.
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
-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
-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
RE: Attachment data type makes table too big
-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
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
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
RE: Attachment data type makes table too big
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
-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
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
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
-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
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
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?
RE: Attachment data type makes table too big
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
CODE --> VBA
-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
-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
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
RE: Attachment data type makes table too big
RE: Attachment data type makes table too big
-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