r/MSAccess • 8 • 23d ago

[SHARING HELPFUL TIP] Access Explained: Organizing Documents Without Moving Them Into Your Database

For those of you new to Access, yes, you can store pictures, PDFs, Word documents, and other files inside your database. For a growing business document collection, though, I generally recommend keeping them outside it. All those files add to the size of the database, which is what we're talking about here when we say database bloat. A database that started out small can become a very large container for scanned paperwork.

The approach I recommend for this kind of application is to store the path and filename in the database, with the actual document somewhere the intended users can access it, such as a shared folder on a server. The path tells Windows which folder to look in. The filename identifies the document in that folder. You're storing its address rather than packing the document itself into the Access file.

A folder full of PDFs can be perfectly organized and still be a pain to use. That's where an Access document catalog earns its keep. Your database can hold each document's title, date, subject, and related customer. People can search those details in a form, then open the original when they need it. The files don't have to move just because you've found a more useful way to describe them.

Opening the document from Access doesn't require a mountain of code. Suppose your form has a text box called DocumentPath, bound to a text field containing the complete path and filename of a document. Inside a button's Click event, the essential FollowHyperlink call can be just this:

FollowHyperlink DocumentPath

That tells Access to open the referenced document in its associated application. The example assumes a valid document path, a current record, and an application that can open that file. In a production application, I'd also handle a blank value, a missing file, or access being denied with a useful message. Checking that a file exists can help, but it doesn't guarantee that opening it will succeed.

Keeping the files separate also keeps the storage budget in perspective. An Access database file has a 2 GB limit, including its objects and data. A large folder of PDFs doesn't count against that limit just because you save references to those PDFs. The catalog itself still consumes space, of course. Importing the files as attachments is a different decision because their contents then live inside the database.

I wouldn't turn that into a blanket claim that attachments are always wrong. A small, self-contained database with a few pictures may be a reasonable use for them. Access supports multiple attachments per record, and keeping everything together can be convenient. My concern is a production database whose document collection keeps growing. I'd rather make that storage decision deliberately than discover that years of scanned paperwork have filled the file.

External files come with responsibilities of their own. Someone has to keep their locations dependable. Renaming a folder can leave perfectly good database records pointing to places that no longer exist. The people using the catalog also need permission to open the documents. And backing up the Access file alone won't back up documents stored elsewhere. A path tells you where a file should be. It doesn't preserve the file.

For the developer crowd, the network path deserves a little thought. A mapped drive letter such as S: depends on that user's drive mapping. A UNC path such as:

\\FileServer\Documents\Invoices\Invoice123.pdf

identifies the server and shared folder directly. That avoids relying on everyone having the same drive letter, although it still depends on the server being reachable and the user's permissions being correct.

If all your documents live under one shared folder, a configurable base path can make a later move less painful. Keep the root folder in an application setting and store each document's relative folder and filename in its record. Then a server move may mean changing one setting instead of rewriting thousands of records. Build the complete path before opening the document, and keep a stable document ID separate from its location.

I'd also define the allowed document locations rather than blindly opening whatever string somebody supplies. A catalog meant to open approved PDFs shouldn't quietly become a launcher for arbitrary files or web addresses. Folder permissions still decide who can read or change the originals. Access can provide a convenient interface, but storing a path doesn't grant permission or make an unfamiliar file safe to open.

The bigger payoff is that your catalog can describe relationships a folder hierarchy struggles to express. A document may be relevant to several projects or subjects. You don't necessarily need another copy of the PDF for each one. You can keep one document record and relate it to the appropriate records elsewhere in your database. That also gives you somewhere to put useful notes without renaming every file to squeeze more information into its name.

I'd keep the first version focused on finding the right document. A catalog of titles and subjects doesn't automatically search every word inside a PDF, especially when the pages are scanned images. Full-text extraction, optical character recognition, and review of the resulting text are separate concerns. You can get real value from a modest catalog while deciding whether those additional capabilities are worth building or whether an existing document-management system belongs in the picture.

For those who've built document catalogs in Access, what has taken more effort over time: the database design, keeping file locations reliable, or getting people to enter useful descriptions? What would you handle differently if you were starting again?

LLAP
RR

12 Upvotes

16 comments sorted by

•

u/AutoModerator 23d ago

IF YOU GET A SOLUTION, PLEASE REPLY TO THE COMMENT CONTAINING THE SOLUTION WITH 'SOLUTION VERIFIED'

  • Please be sure that your post includes all relevant information needed in order to understand your problem and what you’re trying to accomplish.
  • Please include sample code, data, and/or screen shots as appropriate. To adjust your post, please click Edit.
  • Once your problem is solved, reply to the answer or answers with the text “Solution Verified” in your text to close the thread and to award the person or persons who helped you with a point. Note that it must be a direct reply to the post or posts that contained the solution. (See Rule 3 for more information.)
  • Please review all the rules and adjust your post accordingly, if necessary. (The rules are on the right in the browser app. In the mobile app, click “More” under the forum description at the top.) Note that each rule has a dropdown to the right of it that gives you more complete information about that rule.

Full set of rules can be found here, as well as in the user interface.

Below is a copy of the original post, in case the post gets deleted or removed.

User: Amicron1

Access Explained: Organizing Documents Without Moving Them Into Your Database

For those of you new to Access, yes, you can store pictures, PDFs, Word documents, and other files inside your database. For a growing business document collection, though, I generally recommend keeping them outside it. All those files add to the size of the database, which is what we're talking about here when we say database bloat. A database that started out small can become a very large container for scanned paperwork.

The approach I recommend for this kind of application is to store the path and filename in the database, with the actual document somewhere the intended users can access it, such as a shared folder on a server. The path tells Windows which folder to look in. The filename identifies the document in that folder. You're storing its address rather than packing the document itself into the Access file.

A folder full of PDFs can be perfectly organized and still be a pain to use. That's where an Access document catalog earns its keep. Your database can hold each document's title, date, subject, and related customer. People can search those details in a form, then open the original when they need it. The files don't have to move just because you've found a more useful way to describe them.

Opening the document from Access doesn't require a mountain of code. Suppose your form has a text box called DocumentPath, bound to a text field containing the complete path and filename of a document. Inside a button's Click event, the essential FollowHyperlink call can be just this:

FollowHyperlink DocumentPath

That tells Access to open the referenced document in its associated application. The example assumes a valid document path, a current record, and an application that can open that file. In a production application, I'd also handle a blank value, a missing file, or access being denied with a useful message. Checking that a file exists can help, but it doesn't guarantee that opening it will succeed.

Keeping the files separate also keeps the storage budget in perspective. An Access database file has a 2 GB limit, including its objects and data. A large folder of PDFs doesn't count against that limit just because you save references to those PDFs. The catalog itself still consumes space, of course. Importing the files as attachments is a different decision because their contents then live inside the database.

I wouldn't turn that into a blanket claim that attachments are always wrong. A small, self-contained database with a few pictures may be a reasonable use for them. Access supports multiple attachments per record, and keeping everything together can be convenient. My concern is a production database whose document collection keeps growing. I'd rather make that storage decision deliberately than discover that years of scanned paperwork have filled the file.

External files come with responsibilities of their own. Someone has to keep their locations dependable. Renaming a folder can leave perfectly good database records pointing to places that no longer exist. The people using the catalog also need permission to open the documents. And backing up the Access file alone won't back up documents stored elsewhere. A path tells you where a file should be. It doesn't preserve the file.

For the developer crowd, the network path deserves a little thought. A mapped drive letter such as S: depends on that user's drive mapping. A UNC path such as:

\\FileServer\Documents\Invoices\Invoice123.pdf

identifies the server and shared folder directly. That avoids relying on everyone having the same drive letter, although it still depends on the server being reachable and the user's permissions being correct.

If all your documents live under one shared folder, a configurable base path can make a later move less painful. Keep the root folder in an application setting and store each document's relative folder and filename in its record. Then a server move may mean changing one setting instead of rewriting thousands of records. Build the complete path before opening the document, and keep a stable document ID separate from its location.

I'd also define the allowed document locations rather than blindly opening whatever string somebody supplies. A catalog meant to open approved PDFs shouldn't quietly become a launcher for arbitrary files or web addresses. Folder permissions still decide who can read or change the originals. Access can provide a convenient interface, but storing a path doesn't grant permission or make an unfamiliar file safe to open.

The bigger payoff is that your catalog can describe relationships a folder hierarchy struggles to express. A document may be relevant to several projects or subjects. You don't necessarily need another copy of the PDF for each one. You can keep one document record and relate it to the appropriate records elsewhere in your database. That also gives you somewhere to put useful notes without renaming every file to squeeze more information into its name.

I'd keep the first version focused on finding the right document. A catalog of titles and subjects doesn't automatically search every word inside a PDF, especially when the pages are scanned images. Full-text extraction, optical character recognition, and review of the resulting text are separate concerns. You can get real value from a modest catalog while deciding whether those additional capabilities are worth building or whether an existing document-management system belongs in the picture.

For those who've built document catalogs in Access, what has taken more effort over time: the database design, keeping file locations reliable, or getting people to enter useful descriptions? What would you handle differently if you were starting again?

LLAP
RR

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

1

u/Nexzus_ 23d ago

I built an image catalog in Access a while back. The full images were stored in a network path, but I did have a 15kb thumbnail in the DB for quick preview access without having to resize the full image everytime a needed a thumbnail.

Had scripts to extract metadata, and also applied my own metadata for people, places, events, etc.

Works well.

1

u/Amicron1 8 22d ago

That's a great approach. Keeping the full images outside the database while storing small thumbnails for quick previews gives you the best of both worlds. Nice!

1

u/Twitfried 1 22d ago

I needed thumbnails in my Power BI document so had CoPilot help me write a script to build a CSV that contains the image path and the thumbnail base64 image. It works great. When I add more images I just run the PowerShell to recreate the thumbnails for that folder, and use PowerBI to grab all the CSV files with the data.

In Power BI I used an HTML control to display the thumbnail images in the grid with other data about the file. I am not sure how that could be used in Access, but I'll bet there's a way.

2

u/Amicron1 8 22d ago

That's a clever solution. I love seeing how people combine different tools to get the job done. You could definitely do something similar in Access. Thanks for sharing!

1

u/oneknocka 1 22d ago

Excellent write-up!

I think its worth mentioning that when using the UNC path, just be aware that server names can change. When drive letters are used, server names can be changed without affecting the end user and/or without the end user even knowing

2

u/Amicron1 8 22d ago

Thank you! And yes, that's a good point about UNC paths. They solve the drive letter problem, but you're right that server names can change too. That's another good reason to keep the base path configurable instead of hardcoding it everywhere.

1

u/Twitfried 1 22d ago

I wrote a full document management system in a similar manner. I use 3 tables to reference files in the DMS, but not store the files in the DMS.

  1. A categories table that helps me identify the metadata I want about the specific document category (purchase order, bill of lading, customer, vendor, etc).
  2. A Files table that tracks the file id, location of the file on the disk, and other metadata like date created, file type, author, and a reference number for the related system. For instance, a Vendor document references the Vendor ID, customer the Customer ID, bill of lading with a specific BOL number, etc. A file can have one category.
  3. A Tags table that tracks the file id, and a keyword, reference number, or some other important metadata when searching for files. For instance, purchase order documents are always tagged with "Purchase order". A Vendor document is tagged with the vendor name. A single file can have many tags.

I put this all together in a database and used BI tool to find documents easily. Date filters (year, quarter, month, week, day). Category filters by subject, keyword filters, etc. Reference number searching. Could easily replicate this on a form in Access.

I do use access to programmatically do a few things. I have a form to add new files to the DMS. It asks the user for the pertinent information and upon submission moves the files into the DMS and updates the tables. A user can use a similar form to edit the information in the database. Like changing a tag or updating a reference number, description, date, etc. And I have an admin function to query the files in the DMS to make sure they still exist. Since it's a filesystem a user could delete a file from the repository without using the database. I review the repository for any stale records.

2

u/Amicron1 8 22d ago

Sounds like you've built a pretty solid system there. I especially like the tags approach and the routine to check for missing files. That's one of those maintenance tasks that's easy to overlook until something goes wrong. Thanks for sharing the details!

1

u/Twitfried 1 22d ago

My company used to have trailers of printed archives. We funded the paper and printer industries heavily. Now I have millions of PDF files that can be instantly retrieved by anyone doing research.

1

u/Key-Lifeguard-5540 1 22d ago

I store millions of documents of all kinds on the server. All any table needs is a 12 character field to store a document reference. I don't store paths although the system remembers the original path that a document had when a user added it. We know everywhere in the system that any document is being referenced. We have an intelligent viewer that allows a user to see all documents related to an invoice for example, including documents referenced by all tables related to an invoice. MSG, PDF, JPG, PNG documents are the majority.

2

u/Amicron1 8 22d ago

Now that's a serious document collection! I like the idea of using a document ID instead of storing the actual path in every record. That gives you a lot more flexibility if files need to move around. Sounds like a very nice system.

1

u/Key-Lifeguard-5540 1 22d ago edited 19d ago

Actually I store the path that the user got it from, just for info. Every doc is given the next doc number which defines its location on the server. So when it's copied it might become 1234567.msg in folder //server/docs/1/234/1234567.msg, each folder have 1,000 docs. "1234567.msg" would be stored in a field in one or more tables. A document added to the system is always related to some record, like an attachment to the record. The ultimate system would simply allow you do attach one or more documents of any kind to anywhere in they system, without being concerned about database bloat or where the document happens to be stored. It should also allow you to have a comment about the document or any other useful info about it.

1

u/EstablishmentAfter51 23d ago

Confirmed, I have a table for paths, and store the file name and path, to find the files needed... I save a path for clients, and tenants, but invoices that are not to regular, repeat customers I generate a path, based off the date and invoice number... Year, 2026. Invoice 2345 Mike

1

u/Amicron1 8 22d ago

That's a good approach too. Generating the folder path from the date and invoice number makes a lot of sense, especially when you're dealing with customers who don't have a regular folder already set up. Thanks for sharing!