Linking records to photos and documents (1 Viewer)

I have an access database (Access 2000 file format) to keep accession records at a small botanical garden. The accession information table, tblAccdata, is linked to tblColdata (Collection information), and tblPlantnam (taxonomic information). I am a newbie—self taught years ago, then forgot everything and am now returning to fix some things up.

I’d like to be able to add links to photos, pdf documents, and maps (maybe just more photos or pdfs) to the accession records. I might also want to link photos to the taxonomic information records. The way that I am thinking of is to create a table, tblLinkedObjects, that has 4 fields, LinkID, Object, Caption, Notes.

Each accession might have any number of objects linked to it (although as a practical limit, I’d say probably not more than 50 at the most). Some accession records might have no objects, some might have 50. A single object might be linked to more than one record.

So here is where I’m not sure. Should I just add 50 fields to the accession information table, and 50 fields to the taxonomic information field, name them LinkedObject1, LinkedObject2, etc.? and then fill with a LinkID however many of those fields as I have objects I want to associate with a record? Am I overlooking a cleaner way to do this?

Any suggestions for better ways to do this would be appreciated.

G'd Afternoon,
I don't think that creating 50 rows beforehand is a good idea. You can count rows in table right? so, before a user attempt to add an object your form should check how many items already exist and respond accordingly.
G'd luck

Hello,

Not sure if this will help you or not but this is how I did attachments to my records. I have a DB that keeps track of all purchases we have made. I attach all receipts and invoices to the particular record. All I did was in my tblpurchases, added a field called Attachment and gave it a data type of attachment. I'm not sure how many attachments it will allow me to put in there but I have done as many as 10 to one record. Never tried to see how many it would allow. Although maybe Access 2000 doesn't even allow this. Not sure. I use 2007.

I think your idea of creating a table for your image objects is the right approach. But I wouldn't create the records pre-preemptively.

If it were me i'd create a subform situation where the master accession is linked to the child object (picture, pdf, document) in a one to many relationship. In this way you could create any number of image object records for an accession record.

You would set up a foreign key in the tblAccData and form a relationship to your tblObject's primary key (ObjectID?). Once this is created you can create a way to add the records on your form. I would use either a subform or a tab-control probably.

I know your original post is a couple days old. Have you figured out what you will do?

fifieldbl said:

All I did was in my tblpurchases, added a field called Attachment and gave it a data type of attachment. I'm not sure how many attachments it will allow me to put in there but I have done as many as 10 to one record. Never tried to see how many it would allow. Although maybe Access 2000 doesn't even allow this. Not sure. I use 2007.

Click to expand...

I'm pretty sure that attachments came about in Access 2007. I can categorically say they aren't in 2000 and they are in 2010 and 2013.

Simply by adding a field of the Attachment type to any table is enough to attach external files to the database. This mechanism is very practical and can be optimized by dividing the database into two: a database for information, and the other (s) for external files.

There is another alternative that offers greater flexibility and has no size limitations.

Continue here: http://www.msaccessdeveloper.pro/#!...n-the-database/c6x8v/55b1978a0cf2f7a6a9311dbf