Right now, I am linking the OLE Objects, which will mostly be Word documents. The database is still in its development stages, so it can go either way.
I don't want to embed the objects because I don't want the database size to swell as lots of records are added. However, I don't know how to make sure the users save all the files in a cental location (the only way to guarantee all users can get to it) for the linking to work. Basically, the users will be scanning Word documents, then linking them into the database.
How many Word documents are you talking about? Hundreds or thousands? And also can you archive the documents? If there get to be too many documents embedded could you copy some to another database?
A possibility is to create a table in a blank database that stores embedded Word documents and a key. Then have your application link to this table. That way you will have 2GB to store Word documents. Then if it gets too big you can copy old ones to another database.
When you link documents, access to them will be slower and you will have to worry about files being moved or deleted.
Thanks for the separate db idea. I'm not sure how busy the database will be, but I think the records will all have 0, 1 or 1+ attachments. I already designed it as a separate table so there can be multiple attachments, and space won't be wasted for records with no attachments.