OLE corruption

[jeffmoseler]

I have a table with about 25 fields, two of which are OLE fields that generally contain CAD documents. I am having alot of trouble with correuption and bloating. The database file grew to around 800mg with only 500 or so records. It then had all kinds of corruption problems and when I reapaired and compressed, it shrink to around 300 mg and ditched a bunch of my images.

My question is this. Is there a better way to do this? Should I create a second databse with the images contained in it and link the tables? Should I create another table with the images cantained in it and put the Key field in the main table? Will this help prevent bloat or corruption? Or will it do nothing at all?

Thanks for the advice!

 

[SydneyGeek]

I've seen advice suggesting that OLE and Memo fields should go in their own tables, and OLE should even go in its own back end.

The problem (mainly) is that Access is very inefficient when storing OLE files, and bloat is common. The size limit on any single Access DB is 2 GB, including the system tables. So, if you push the OLE fields to their own DB you have greater stability for the rest of your data. If you can make it work, you may find to better to link to the CAD drawings rather than import them. Another option could be to upgrade to Access 2007, which has a Attachment data type that reportedly gets around many of the bloat issues. I can't comment from personal experience, not having used the Attachment data type yet.

Denis

 

[jeffmoseler]

Thanks for the advice. I think I am going to try putting them in another database and linking the tables. I was thinking that might slow it down though. We'll see.

I am currently linking the OLE's, but I have not noticed any change in the amount of space the images take up. So I don't think that is necessarily a space saver.

I will look into the 2007 addition. I was just waiting until some of the bugs were worked out or at least until I heard wheather or not it had too many bugs!