Performance issues with OLEembedded files

[faeryfyrre]

Hi Guys,
I'm in need of some advice. I have a database which stores Employee Commission information. For audit purposes i need to store some emails as proof of approval by Employee's supervisors. Now each record needs to store 10 approval emails and there will be approx 200 records added annually.
This database must run in a multiuser-network environment and i was wondering whether the size of these records is likely to slow Access down noticably?

Now i could just store the emails in a central folder on the server and link to them but this organisation is renown
for changing it's directory structure almost yearly and all my links to the emails would break (meaning i would have to write a routine that fixes broken links). Therefore i feel that Embedding is the preferable option but i am concerned with performance issues.

Your incite is appreciated.

Alec Doughty
Doughty Consulting P/L

"Life's a competition. Play hard, but play fair"

 

[Lightning]

Hi Alec

This sounds like a case of "Damned if you do and Damned if you don't"

If you store the docs in the Db, you get Db bloat and the attendant performance issues.

If you store the docs separately you run the risk of losing the links on some (indeterminate) regular basis.

In that situation, I would probably go with the linked option, on the basis that I could do something to resolve a broken link, but I can't do anything about Access performances. Although depending upon how quickly the docs accumulate, this may not be a problem for some time. And compacting the Db on a regular basis should also help.

I would also (if possible) store the documents in a subfolder of the folder where the Db is stored. That way, most people would (should?) realise that there is some link between the Db and the docs, and not move them around separately.

Good Luck

HTH
Lightning

 

[faeryfyrre]

Thinking about it i feel that there would definitely be some bloat. There would at a minimum be 2000 emails embedded per year at an average size of 20K without attachment (emails are saved in Outlooks .msg format), leading to perhaps 40MB per year just in emails being input into the database.

I'm leaning toward agreeing with you Lightning, linking would be preferable.



Alec Doughty
Doughty Consulting P/L

"Life's a competition. Play hard, but play fair"