How to manage attached files with access. Options.

Hi all
This week's post is about how to manage attached files with MS-Access and VBA.

Introduction

Sometimes is useful and/or necessary to store attached files in our applications.
Let's see some examples:

• A used car seller may have a database with all the vehicles on sale, and may need to have photos of the cars, to show them to the potential customers before seeing the cars. And also may want to store digitalized the papers of the car.
• A real state business, has a file with each property, and may want to show the photos to the potential customers before visit, to restrict the number of properties to visit. And not only the photos, but also legal papers about the property.
• A doctor, may want to save information about their patients, but sometimes this information is not suitable to be stored in a record, for example a bone scan or the results of some tests, but is useful to attach to the patient file to find it more easily.
• A farmer may need to store data about the traceability of their products, and their suppliers send pdf documents with the information of the products. He must, also, send information to his customers when selling animals.

There are a lot of examples, I'm sure you have been thinking a lot of them while reading the post, and you have found sometimes with similar problems.

Solutions

There are some solutions to store this information using databases in general, and MS Access more specifically. 

MS-Access by itself allows to store the data using a special kind of field for this purpose. I've used it in the past, some years ago, but the results were not the expected, because the file grows exponentially, and it becomes difficult to manage it, it becomes slower and a big amount of space to do the backups is needed.

I'll try to explain how to store data without storing them directly to the data files.

Attached files with VBA

In this point it may be useful to take a look at a previous post, published last feb 10, about how to use an Options dialog box with Access. It may help, and I will use it later in other posts to enlarge today's post.

What is the main idea? The idea is to store the files in specific folders "controlled" by the program.

To control those folders we will use the Options dialog box, with one parameter, may be called, for example, FilesFolder, and will be the name of a folder on the HDD.

We have a table and in this case, each record has an attached file, only one and always one (or zero).

If we have been stored the name of the folder where the files are, we need simply to add a text field to the table, where we will save the name of the file. 

To have access to this file, we will use the GetParam public function to obtain the folder name, and we will concatenate it with a anti-slash and the name stored in the data table.

If you want to use the contents of the file in a form, you may use the FollowHyperlink statement to open this file with the by default program to open it.

What are the real advantages of this system?. Of course, the amount of space needed by the application.
Storing the files in the data table makes this datafile grows exponentially, and like this, we only store a string.

Final note

This has been a very short post to explain one option about how to store this information.

In nexts posts I will develop and release some files with examples.

I hope you like it. I'm working in a post about how to use the explained above to show those files in a carousel of images. I will try to post it this week.

Have fun!