Attachments

[cjp78]

How can I limit the number of attachments per record to one?

[Rawb]

Maybe I'm just displaying my ignorance of some of the new features of Access 2007, but what do you mean the "number of attachments per Record"?

Are you talking about enforcing a one-to-one Relationship between two tables?

[cjp78]

How can I limit the number of attachments per record to one?

Thanks for taking the time to look at my problem and reply. 2007 supports attachments (text files, pictures etc) being added to a record. The default setting allows multiple attachments per record (ie row in a table). I know this breaks RDB rules of one value per column, but access handles this in a behind the scenes table(s) that is not user accessable. However I want to limit the user to a single attachment only for each record, making sure older attachments are deleted on update.

[Rawb]

Well, in that case, I'd say do it the old way. I'm almost positive there's a way to do what you're asking using Access 2007's builtin capabilities, but it'd probably just be easier to take control of attachments yourself.

Honestly, the best way to handle attachments is to store them outside the database and just create a text field that holds a link to their location. Then, whenever someone adds a new attachment, you just delete the old one and replace the link.

If you want to do everything in Access though, you can also add a field to each Record with a type of "OLE Object" and insert the attachment in there. Adding files in the database like that though generally isn't recommended because it can add a huge amount of bloat to your database (what if someone attaches a 5mb JPG to each of your records?).

Actually, probably the best way would be to have a table for just the OLE Objects. Give it maybe three fields per Record, a unique ID, the OLE Object field and a Text field where the user adds a brief description. Then add a field to your other table with a reference to the ID. That way, you can "link" the same attachment to multiple records without having to store it multiple times.

[cjp78]

Rawb, thanks. I've come to the same conclusion unfortunately....back to VB...