Renaming Attachment Files (Images) for a Catalogue

[NewAndBaffled]

BACKGROUND

I am putting together a simple database to handle multiple images taken at various stages of car repair (for looking back on later).

5 potential stages (pre-repair, MET, metalwork, paintwork, post-repair), each stage has some pictures taken. These images are important to be easily searched through & are simply attached to a RegNo & JobNo to identify which car repair they refer to.

I have created a simple form, with JobNo, RegNo & 5 Attachment Fields (1 for each stage).

This gets the images attached ok, so I can now see the images & query by RegNo, JobNo, etc.

QUESTION

At the moment, the user has to copy the images taken into directory structure in Windows, handling filenames & directory names manually. this is cumbersome & not really practical.

So, what I'd LIKE to happen is:

1) Pictures are taken as repair occurs (on a tablet, then transferred to PC).
2) Pictures uploaded to the PC into a temp 'dump' directory, with simple link on Windows desktop
3) Office PC user opens Access database & attaches files (images) to correct Job/Reg/Stage. These images are selected from the 'dump' directory using normal 'attachment' button on Access form.
4) On leaving the record (or closing form), the image files are automatically renamed & moved into a specific sub-folder, with the RegNo and JobNo used to create unique directory name.
5) This leaves the 'dump' directory empty, ready for the next batch of images to arrive.

I've got my database working fine, with a form allowing me to attach Image files to each of 5 'stage' fields. So I'm at point 3 above.

It's the automatically renaming the Image files, Moving them into a created sub-directory, that I'm completely stuck with.

I can see how it's done manually, using the Save As option in Attachment handler.

I hope I explained this ok, but please feel free to ask anything else you need.

FURTHER QUESTION

When I move these files, does the Access Database simply 'link' to them, or does it store an additional copy inside the Access database?

This matters because of disk space, there can be up to 100 images a day at peak times.

Thanks for your patience.

 

[GinaWhipp]

Hmm, not exactly the way I would do it...

Why wouldn't you create the directories when the repair job is created? Then you could just copy the files from the temp directory to their respective directories. And, if you have a naming convention you could then link them directly to the record with no issue.

Further question...
No, it does not store a copy in the database nor would you want to? Access has a 2 gig limit and you would reach that quickly with the amount of pictures you are suggesting. If disc space is an issue then get an external drive to store the pictures on.

 

[NewAndBaffled]

Thanks for the reply. This is a strange one, it looked a simple task, but has gotten more complex as it has developed.

I had thought of creating the directories, though my programming skills are very patchy, so took what looked an easier route. The attachments in the DB are very handy, it's a great point of reference to look anything up & browse thumbnails in their relevant 'image section'. I'm still baffled as to where the images 'are', since they transfer with the Db from PC to PC.

I have the Db created, all working fine....in so far as adding attachments is concerned. It works well, for looking up etc.

The prob arrives when the images need to be used for anything else (such as accompanying a quotation to insurance company etc)....they, at present, need to go into attachments & save out the images they want (works, but it's a bit cumbersome for daily use).

I'd thought about having a button on the form, which would write out all the images to directories (created using RegNo & JobNo), so they could sit on a server drive & be accessed by any office/estimating staff.....simply using the Access Database to look up, etc, which is a big problem when they're left to create their own directories etc. This would get more difficult in that it would need to deal with file overwrites automatically (as more files get added to DB as job progresses)....so they would need to be able to click 'export' button multiple times through a job's development.

As it is, it's working....but I need to do something regarding writing these files out again....thinking the 'button' might be the job, if I could work out the code.

I've seen this..... /forums/showthread.php?t=228146 and think I might try it (although it'll be hit/miss with my limited understanding of coding).

 

[GinaWhipp]

Like I said, not the way I would do it because of the size limitations of Access. Hence, have not even looked at any code that writes the attachment field objects out to a directory. That said, the link you posted looks promising I would give it a try...

 

[NewAndBaffled]

Sorry for appearing a bit dumb, this is just outside my normal comfort zone.

The 'attachmnents' method is great for attaching correct images to jobs easily.

It's also good for the user to be able to browse the thumbnails inside the job form, when looking for something queried.

It's just that they cannot access these images outside of the database.

I'm wondering if I could move the attachment images, keeping them in the database as the attachments they are....but then have the actual image files themselves moved into a directory where the user could access them easily (i'm assuming access stores them in a local user directory).

This way I'd have my images stored in a directory, easily accessed outside the Access DB, but also have the thumbnails and the useful 'attachment icons' for easy reference.

I seem to making this way more complicated for myself than it needs to be....I'd thought initially just something to copy the files around & create a logical directory structure.....but when I discovered how Access 2010 handled attachments, it looked very handy for the user to operate & handle queries.

 

[NewAndBaffled]

I think I may have found the solution......back to the drawing board

https:// support.microsoft.com/en-gb/kb/285820

 

[GinaWhipp]

I am not disagreeing with you I am only pointing the size limitation which you will reach quickly storing the images within the database. It is your choice and you appear to have made it.