Running a query on a Table of Attachments for Reference

[twolfe13]

I'm trying to set up a query that references a table of attachments. I have many fields that I want to associate with images or CAD files. I've set up a table that includes a list of fields from various tables that I can search with a query to get more information. (I'm open to other suggestions on the best way to give fields long descriptions and pictures.)

I've set up another table that just includes an ID and figures.

I've linked my two tables via an attachment ID that descibes each attachment in my table.

I can run a query (or form or report) on these two tables and prompt the user to enter a field they want more information about. It generates a description of the field and an attachment. Is there a way to display multiple attachments?

For example, I want the table with the descriptions to include multiple attachment IDs so that when i run my query it returns multiple attachments. I know I could add multiple attachments to each record in my table of attachments, but that isn't what i necessarily want to do since that would mean attaching the same figure to the table multiple times. I also don't want to reference physical locations where figures exist since I want the database to be self-contained and moveable.

Also...speak slowly. I'm new to this whole MS Access thing.

 

[strive4peace]

rather that using an attachment data type, I would recommend storing Path and Filename in a text field -- then use the Image control and Unbound Object Frame to display them.

I often copy the file to attach to a directory that I put below the database location with all the images/attachments for a database so the original file can get moved or deleted with no problem.

> "new to this whole MS Access thing"

On learning Access, it is important to start with table design and relationships. The first thing to do in building a database is set up the structure to hold your information. Most people do this wrong.

Here are some videos on planning a database that would be beneficial:

Learn Access on YouTube Playlist
Learn Access By Crystal - YouTube

The "Learn Access" series takes an idea from concept to application.

* Perspective -- put Access into perspective with other applications such as Excel, etc
* Planning a Database -- what to do before you start -- how to get your mind ready and some Naming Guidelines
* Planning a Database -- Naming Conventions, Fieldnames, Key fields, Relationships
* Planning a Database -- plan fields and relationships for contact information
* Build a Database -- create a new database, tables, and relationships
* Tip: Enforce Referential Integrity on Access Relationships
* Tour through the Access Defaults
* Tip: Requery a Query
* Tip: Add Save As Icon to Quick Access Toolbar
* Tip: Where is My file?

Each tutorial has links in the video description to more free information

There are lots of other video tutorials on YouTube. I have several playlists with work done by others. Even videos for older versions are good to watch -- gain different perspectives while you are planning. Look on my channel:

Learn Access ... - YouTube

The most important thing to get right with any database you build is to get to know the data well so you can correctly design your tables and relationships. This is called data structure, and it comes before knowing anything specific about Access.

Once you watch the videos, read this free book:

Access Basics by Crystal
Free 100-page book that covers essentials in Access

Pay careful attention to the chapters on Normalization and Relationships. There are lots of screen shots, so print it out, get cozy in your favorite chair, lay in bed, wherever it is comfortable for you to learn -- and enjoy~

When you re-read the book (which you should, several times), be sure to visit each link -- there are many great free resources. The more you submerse yourself, the faster you will learn.

 

[twolfe13]

Crystal, thanks for the suggestion. However, I really want the images and files to be embedded within the database if possible. I had considered this method of linking, but I want to be able to share my database with other database managers easily. These users are under different networks that have no way of accessing a central file repository.

Also, it seems that if I was to use a file path for these images and files, that these paths are absolute, not relative. So sharing them with another person would mean that they would have to extract the database to an identical absolute location or change the file paths for all entries. I don't really want to go down that road.

I feel fairly confident with regard to building tables and relationships. Moderately confident with building queries, forms, and reports. Low confidence in the worlds of VBA and SQL.

 

[james_lankford]

Crystal, thanks for the suggestion. However, I really want the images and files to be embedded within the database if possible.

Access 2010 specifications - Access - Office.com

[TABLE="class: collapse, width: 700"]
<tbody>[TR="class: trbgodd"]
[TD]Total size for an Access 2010 database (.accdb), including all database objects and data[/TD]
[TD]2 gigabytes, minus the space needed for system objects.

[/TD]
[/TR]
</tbody>[/TABLE]

 

[twolfe13]

Thanks, James. I'm aware of the limitations and the reasons for using external links. I've already started including attachments into my database and I'm up to a whopping 40 MB, so I don't think the 2 GB limit is going to be a problem. If it becomes a problem in the future, I'll cross that bridge when I come to it or split my database.

Question still stands.

 

[strive4peace]

> "really want the images and files to be embedded within the database if possible"

I wouldn't suggest that ... the Attachment data type is depreciated in Access 2013.

> " paths are absolute, not relative"

to render a file, this is true. However, you can store relative paths. In my applications, paths starting with "\" are relative.

> "have to extract the database to an identical absolute location or change the file paths for all entries"

this is not true.

Here is code to adjust for relative path:

      If Left(sPathFile, 1) = "\" Then
         sPathFile = CurrentProject.Path & sPathFile
      End If

here is code to strip current FE directory to create a relative path

   If Left(varFile, Len(CurrentProject.Path)) = CurrentProject.Path Then
      sFile = Mid(varFile, Len(CurrentProject.Path) + 1)
   End If

 

[amerifax]

>>strive4peace <<
I'm trying to do something similar. I have approximately 9000 records in a directory that are images. I have a table that I want to create an attachment to. After a long clumsy process I was able to create a table that contains the filename's that equals a link in a second table. So each table shares a common name, or if not, I need to create a record, with the name of the file that was one of the original 9000. With my limited knowledge of access this is where I have an issue.

I also took note to the fact that I can control the location of the images, which is a plus. My only other consideration was file size. I cannot see that as an issue since it's nothing more than a link. Am I correct?

Bob

 

[HiTechCoach]

My only other consideration was file size. I cannot see that as an issue since it's nothing more than a link. Am I correct?

Bob,

Yes, that is correct. You avoid all the database bloat issues by not storing the files in the database.

Check out this FAQ: The Access Image FAQ

 

[amerifax]

Thanks
Bob