Document Index
By Richard Rost
4 years ago
Build a Searchable Document Index in Access
In this Microsoft Access tutorial, I'm going to teach you how to build a document index in Microsoft Access. We will store the names of our files in a table, along with a link to each file. We will then copy the text of that file to Access so that we can search the full contents of each document from inside our database. I'll also show you how to make a button to open each file using the FollowHyperlink command. Then, I'll show you how to make a simple form to search for keywords in the text of each document.
Catherine from Provo, Utah (a Platinum Member) asks: I have to keep track of hundreds of different documents for each client. Is there a simple way I could put these all in a database and possibly search within the text of each document using Access?
Members
Members will learn how to use Microsoft Word Automation to automatically open each document and copy the text directly into the database. We will also see how to browse and pick a file with the FilePicker function.
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
Recommended Course
Links
 Keywords
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, Searchable Document Index, FollowHyperlink, Search Text Box, Assign Documents to Customers, Browse and Pick a File, Microsoft Word Automation, Convert PDF to Text, Convert DOCX to Text, VBA to Copy Text to Database
Subscribe to Document Index
Get notifications when this page is updated
Intro In this video, I will show you how to build a searchable document index in Microsoft Access. You'll learn how to store and manage document locations, display and search through document text, and set up a table and forms for easy data entry without saving files directly in your database. I'll also demonstrate how to create buttons to open documents, copy their text into your database, run wildcard searches using parameter queries, and link documents to specific customers for a simple relational setup.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.
In today's video, I'm going to show you how to build a searchable document index in Microsoft Access. Today's question comes from Katherine in Provo, Utah, one of my platinum members. Katherine says, I have to keep track of hundreds of different documents for each client. Is there a simple way I could put all of these in a database and possibly search within the text of each document using Access?
Well, Katherine, if you've watched my images video, you know that we really shouldn't store files inside of our Access databases. There are a ton of reasons why, and I cover them in that imaging video. But basically, Access, even though it has an attachment data type, is really not designed as a file storage medium. So you don't want to put files inside the database.
But you can put your files in a folder and store the location of those files in your database. Then you can make a button to simply click on it and open up that document. You can also copy the text that's in that document and store that in your Access database. Access is really good with storing text information.
So let me show you how to do all of this in today's video.
Before we get started, I've got some prerequisites for you. Go watch these videos if you have not yet already watched them.
Start off with my blank template video. This explains how I built my blank template, which is the database we're going to be starting with.
Here's the images video. Go watch this to learn how to display images inside your database. This will also pertain to the method we're going to use for regular files.
If you don't know what Access Query criteria are, go watch this video.
You should know what string concatenation is, putting two strings together.
You should know what a wildcard search is and how to use the LIKE keyword.
You should know parameter queries. You should know how to get a value from an open form.
Finally, you should watch my intro to VBA class. There's going to be a teeny tiny bit of VBA involved in today's lesson. Just a little bit. Don't be scared.
This video is only about 20 minutes long. It teaches you all the basics. You don't even need to know a lot of VBA, just a little tiny bit, but you got to know where to put it. This video explains all that.
All of these videos that I just mentioned are free. They're on my website and on my YouTube channel. Go down below the video, and you'll find a little more link. You'll find all the links down below. You can click on them.
Watch those videos, then come on back and we'll cover the searchable document index.
Here I am in my blank TechHelp free template. This again is a free download. You can go grab a copy on my website. I'm going to minimize this for just a minute.
Now I'm going to need some documents. I'm going to make a documents folder. I'm going to put that out on my desktop along with my database. I want the documents to be under the folder that the database is sitting in. You'll see why in just a few minutes.
In my documents folder, I've got three documents: a PDF file, a text file, and a Word file. They've all pretty much got the same thing in them. I just copied my bio off my web page, but each one of them has got a line that says "This is the text version." "This is the PDF version." And of course, I've got the Word doc which says "This is the Word version" in it.
Let's minimize this and go back to our database file.
The first thing I need to store my document index is a table. Let's create a table to store all my information in.
We'll make the first field the DocID. That's my autonumber.
Then a name for the document: short text.
And then the file name: file name. It'll also be short text.
Again, we're not going to store the file itself in here, just the location of the file, the file name.
We also do want to put the document's text in here. So DocText. We'll make that long text.
Long text and short text are a little bit different. I explain the difference between them in my Access Beginner 1 class. Basically, short text can be up to 255 characters. You can do things like put it in a combo box. It's indexable.
Long text is great for storing lots of information, and yes, you can search on it. But you can't do some things with long text. But it's good for just taking a bunch of information and just dropping it in there. They're great for notes or things like this.
Let's save this table as DocT, my document table.
Primary key? Yes, that's my autonumber.
Let's put some data in here.
Now, the DocName, you can type in yourself. The DocFileName is going to be the name of the actual file itself in the folder, which is going to be this stuff.
It's up to you whether or not you want to keep all your files in one folder or if you want to put them in subfolders or different locations. If you want to store the full path to the file name in here, you can. I'm going to say all my documents are in the same folder. It keeps things easier.
So I'm going to put in here my PDF file. I'll put this as the PDF version, and the file name is just PDFFile.pdf.
Document text we'll get to in a minute.
Then we have the text file and the Word file. So I'll put in here the text version. This will be the TextFile.txt. Then, of course, we have the Word doc, which is WordFile.docx. So there's my three different files.
Let's make a form to hold this information. It's better to work with forms. I like to put some sample data in my table while I'm building my database, but we don't want our users interfacing with the tables at all.
I've got a continuous form right there. Let's copy and paste that guy. Control C, Control V, we'll copy and paste. DocF. This will be my document form.
Design view. Come into here.
I'm going to get rid of this ID. We don't need the ID on this form. Let's slide this over to the left.
First, bind this form to the table. Go into data, and we'll set the record source equal to DocT. So now this form gets its data from my document table.
We can bring in our fields. We'll put them right here, so we'll go to Add Existing Fields. I don't need the ID. The only time you really need the ID is if you want to read it from somewhere else. So if you had another form that was based, had values based on this one, you'd want that ID on there, but we don't need to see it. Kind of meaningless at this point. Remember, IDs, autonumbers are not for you. They're for Access to make relationships inside your database. You shouldn't care what that document ID is.
Click, drag, drop them over here. I'm just going to delete these labels.
I don't need this unbound field. Let's get rid of that. Slide the document name over to the left, right there.
We'll put our file name next to it. Let's put it right there. It doesn't need to be that long; let's go about that big.
The document text I'm going to put in the footer. Click and drag and drop it right down there. Shrink that up, and let's slide this over like so. You can make this as big or as small as you want it. I'll do maybe about like that. Bring this over like there.
I'm going to leave a little bit of room here for a button. This label is going to be the name of the document. Copy, paste. We'll slide it over here. This is going to be the file name.
Save what we got. Save it. Close that. We can close that. Let's open it back up again.
Looks pretty good so far. Let's make that document text on the bottom a little bit bigger, so it's a little more prevalent. Maybe slide that down like that. Open this guy up a little more. Let's give it a different color. Let's make it... oh, I don't know. Let's go with that. How's that look? Looks pretty good.
Now, why do I have this down here and this up here? Well, this is a big field, and I want this to be in my nice short list. The way this works is whatever record you're on in the detail section will be displayed down here in the document text. Let's save that.
If I'm on the PDF version, I could come down here and type in "This is from the PDF file." If I go to the next one, "This is the text file." I go back to the PDF one—look at that, see? It says this is still from the PDF file. These are the records up here and this field is still bound to that record, but it's just showing you this field only down here. This is the way I like to handle notes fields usually in my continuous forms.
Now, what I want to do at this point is I don't want to actually have to type the text in there. I want to copy it right out of the document. So let's make a little button we can put right here next to each file. We can click on it and open up that document.
This is why I wanted you to watch that intro to VBA class. Real simple, one line of code—oh, we're going to put a couple other ones, but we really only need one line of code.
Here's my command button. Click on that. Drop it right there. Cancel the wizard, because what we want to do isn't in the wizard. I'm going to put in here just the word "Open." Let's make it a little bit smaller. Let's go in here at 11 points, maybe go 9 point. I'm going to make this button nice and small, like right there. How's that look? Just like that.
Let's change this button's name. Give it a good name. I don't like "Command8." I don't want Alex yelling at me for having bad button names. "OpenBtn."
Now let's put the button's code in here. I know the file name is in this box, and the folder that file is going to be in is going to be in the documents folder underneath the current folder. The current folder is my desktop, so for me it's C:\\Users\\amaker\\... I don't know. "amaker" is my company name. It kind of shrunk it up: Amaker\\Desktop\\Documents. That's the location of that file.
But I don't want to keep having to type that in, so we're going to use a trick to tell Access to look at whatever folder the database is in, and then just go to the documents folder underneath that.
How do we do that? Let's go back to our database.
On our button, right-click, Build Event. This brings us into the code builder right there. If you get a little window popped up that says what builder do you want, always pick the code builder. That was all explained in my intro to VBA class.
Right inside of here in my OpenBtn_Click, this is the subroutine that runs when we click on the open button.
We're going to use the FollowHyperlink command. You might have seen in previous videos I call it as—you can type an Application.FollowHyperlink, which you really don't need the "Application." part—just FollowHyperlink is enough. Address is where you want to go.
You can actually follow a hyperlink to a website. You can follow a hyperlink to a file. We're going to use a file.
I could come in here and put in C:\\Whatever\\Desktop\\TextFile, but I don't want to do that. I want to replace that path with the current path that the database is in. That is CurrentProject.Path. That's a special variable that represents the folder that your database is in.
After that, I got the folder. I want to tack on to that "\\Documents". Now I'm in the documents folder under the database folder. Then where's my file name? Well, that's DocFileName. That's the field in my form. Everything else that's on there you can pretty much ignore; those are all optional parameters.
That's all we really need right there. There's the one line of code that we need to make this work.
Save it. Control S. Come back to your database. We're going to close this down and reopen it.
Now if I click on this "Open," boom, there it is. It loads that file. If I click on this guy, click, and it opened on a different screen—hang on, there it is. Just had to copy it over. It'll open up whatever this is. I don't have Adobe Acrobat on my machine; I just use my web browser. I don't need Acrobat. So it loaded up. I had a web browser window opened on a different screen (I got four monitors on my desktop), so I just had to copy it over here. Then the same thing with our Word document. So it will work with pretty much any document type, by the way.
There's the Word document. You can put Excel files in here. You can put whatever you want in here.
How do we get that text into here? Well, that's just a matter of opening the document. There it is. Click somewhere in here. Hit Control+A—that'll select all the text in pretty much every program that I know. Control+C, copy. Close that. Come back here. And then just paste it in—Control+V. There's all the text that was in that document.
Do the same thing with the text file. Click, Control+A, Control+C, close it, come down here, paste it in. Finally, the Word document. Open it up, Word opened up, minimize that time. Control+A, copy, close it, and paste it in.
Now you've got the text of each of those documents in your database. Now this is searchable.
Is there a way to read that text in automatically, where I can just click a button and have it pull in? Yes, there is. I'm going to be covering that in the extended cut for the members. But as you can see, if you don't have that many documents, it's very easy to just copy and paste it.
Now you can search all of this text and do stuff with it. You can add it to reports or whatever you want to do inside your Access database.
Yes, I know you can search from Windows File Explorer. You can do all kinds of search stuff out there. But if you want to use this text for something or to build a more powerful search index yourself, you can. You can do it all inside your Access database.
Now let's make a button to open this guy from back here.
Design view. Let's just borrow—we can just use the Hello World button. We're not using it for anything else. I'll put it right up here. We'll call this "Documents."
How do we do this? Right-click, Build Event. Now this guy currently is doing just Status Hello World, but I'm going to say "DoCmd.OpenForm DocF." That's it. That's all you need to open a form from a button. Once you know the tiny little bit of VB code, that's a whole lot faster than running the wizard. You can use the wizard—the command button wizard—to make a button to open up a form, but that's faster once you know how to do it.
So now, come over here and let's close this. Save changes. Yes. Open it back up again.
I've got a button up here in my Quick Launch bar to open up the main menu form. That's all that does. There's a macro for that. I cover that in the blank database video.
Let's click on that. There's my documents.
I'm going to slide you over here. Save it there. If you move a form and then save it (Control+S), it will save that form's position, so the next time you open it up, it opens up in the same spot. Isn't that handy?
Now, I said this was searchable. How can we search it? Let's start with the query.
Create, Query Design. Bring in your DocT. We're going to bring in all the fields and also bring in DocText a second time. DocText is my long text field that's got the data in it that we want to search. But I want to bring all these guys back, but I don't want duplicates though. I don't want to see—because if you run it right now, you'll see two copies of that field. There's DocT.DocText and then Field0. I don't want to see it twice. What we do is we simply just turn off the second one's Show box. It'll hide it.
I cover all this in my Access Beginner course, by the way.
Now, the criteria down here is what you put in here if you want to find stuff. You watch the query criteria video. I could put in here something like, you know, PDF like that, but now it's just going to look for records that have exactly the word PDF in it, which it won't find any. So I have to use the LIKE keyword and say LIKE, and then inside here (let me zoom in so you can see this better), you'll LIKE, and then star, and then PDF, star. Those wildcard characters say "any number of characters here, any number of characters there, but it's got to have PDF in the middle of it somewhere." That's my LIKE wildcard search criteria. Now when I run that, there's the record with PDF in the document text. That's your wildcard search.
But I don't want the user to have to have multiple queries to run for every different thing, so I'm going to use a parameter in here instead. I'm going to get rid of "PDF," and I'm going to do this—watch this—close that quote and bracket: [Enter search text] like that, and the star.
What's going to happen is whatever you type in here will get replaced. If you put PDF in there, it'll put it between the two stars. There is how you do a parameter query wildcard search.
Now if I run this and I type in "TXT," it uses that as your search criteria.
But I also don't want my user to interface directly with my queries, so I'm not going to put that there. Instead, I want to get a value from an open form, maybe this text box right here. Let's do that instead. These are all the concepts I taught in all those other videos I wanted you to watch.
So let's put in here "search text," like that, and let's open up the properties for this box. Now that's currently set to a date, so we'll get rid of the control source, we'll get rid of the format, and we'll name this thing—let's call it maybe "SearchText." How's that? So the name of that box is now "SearchText." It's SearchText on the main menu form.
So I want to get that value (whatever the user puts in there), and I want to have that be the query criteria. Instead of [Enter search text], I'm going to replace this with "Forms!MainMenuF!SearchText," and that's that other video that I want you to watch, which is getting a value from an open form name.
See how I want you to watch all that stuff so you knew what I was doing here?
OK. Let's close this. Let's save this guy now. Let's save this as my—how about DocSearchQ, document search. I'm going to close that.
Come back out here. If you haven't already saved this, reopen it. Put something in here like PDF. Now you have to hit Tab at least once, because if you're still sitting on this field, it won't get the value. You're technically still editing and it's dirty. But now if I come over here and run DocSearchQ, look at that. It gets that value for the search parameter off of that form field.
If I run it again—right, TXT, got to hit Tab and then open the query. There's the TXT version.
If it seems like I'm jumping around a little bit, that's because this is a TechHelp video. I cover these concepts a lot slower in my full course, and I go over them in the order that you should learn them, not jumping around like this a little bit.
Now, I also don't want to open up this query and use this every time I want to search through text. Wouldn't it be nice if, when I opened up the documents form, it got that parameter from that query?
All we have to do now is simply change where this form is getting its values from—the DocSearchQ. I'm going to show you this. If I open this up and I hit "Documents" with no parameters in there, you see everything. What does the parameter become? If that box is blank, it becomes LIKE "*" or just "*". It ignores it.
But if I put something in here like PDF and hit "Documents," there you go. Or if I come back out here and put in "word," the other documents might have the word in there. What do I got in the Word file? Docx. If I put that in there—let's put in here "docx." Yeah, because I just copied my bio off my web page. I bet you "Microsoft Word" is in there. Yep, there it is right there: "Word."
In the extended cut, I'm going to show you how you can make another button over here to actually pull in this text from that document file without having to open it up and copy and paste it manually.
But I got one more thing I want to show you. Ready for some bonus material?
Catherine, you wanted to do it so that you could have documents that were basically stored in each customer's record. So all we have to do now is make this guy relational. If you haven't watched my Relationships video, go watch this.
Basically, what we're going to do is we're going to come over here to the document table, go to design view, and we're going to add a CustomerID. That's going to be a number, type long integer, default value zero.
Now, everybody who's currently in here, you're going to have to give them a CustomerID or not. They don't have to have a CustomerID if you want to just see everybody.
I'll go 1, 2, 3. So this document is for my first three customers: 1, 2, and 3.
Now, in your document form, you'll need a way in here to display who the customer is if you want. You don't have to, but we can put it over here. Maybe a combo box.
I've already got a combo box that's linked to CustomerID. It's on my order form. If you haven't watched my order/invoicing video, go watch that too. That explains how I made this form.
That's my invoicing video. Again, you'll find links to all this down below. In the invoicing video, we made an order form, and the order form's got a combo box right here that we can pick the CustomerID.
So I'm just going to borrow this guy. No sense reinventing the wheel. Copy. Come over here and then we're just going to paste that in there and slide it over here, slide to the right. Delete that. We got to kind of resize it a little, just a teeny bit, just the width a bit. Copy that label, copy, paste, slide you there, and call this "Customer."
CustomerID is optional.
If you look in the CustomerID combo box, you'll see it's also got a default value, which is right here: Forms!CustomerF!CustomerID. As long as the customer form is open, it will get its value from that form.
So now if I close this, save changes, yes, close this, close this. If I just still open up my documents form, you'll see who the customer for that document is. If I clear this and do that, you'll see everybody's in there now.
If I want to add another document, I can come down here and put a different doc, just put in here "ABC.txt," even though it doesn't exist. That's okay, and you can still pick a customer over there.
If you want to make it so that you open this up from the customer form and only see that customer's documents, how do you do that? Go to the customer form.
This is bonus bonus material.
We'll make a button over here, design view. I'll just copy one of these guys, copy, paste. Slide it there. We'll call this "Documents" or just "Docs," whatever you want to call it. Give it a name; we don't want Alex yelling at us—so not "Command30." We'll call this one, we can call this one "DocBtn" here too. You can have a DocBtn on multiple forms.
Right-click, Build Event.
Here we are in the DocBtn_Click. I want to DoCmd.OpenForm DocF, but I only want to show records where the CustomerID equals the CustomerID on this form. So it's going to be ,,, (comma, comma, comma) because we don't need those parameters; we just need the WHERE condition: WHERE CustomerID = CustomerID. What that does is, with a little string concatenation, it takes the current CustomerID on the customer form and it says CustomerID for this record has to be the same as CustomerID on the customer form.
Save it. Come back over here. Close it. Go back in your customer form, hit "Documents," and there's just Richard Rost documents. Same. Close that. Come over here to a different person, James Kirk, documents. There's Jimmy Kirk's document. Notice the next record, the blank record, is getting its default value now from this form, so you can add more documents in here and they'll automatically go on Jim Kirk. Same. How's that?
Did you learn something today? I hope you learned something. I hope you thought that was interesting.
In the extended cut for the members, we're going to do some really cool stuff.
First, we're going to make a button where you can click "Pick" and pick a file instead of having to type in the file name. I know we've covered this before; it'll take two seconds. I've already got the code in the Code Vault, which I'm going to copy in.
Then the new stuff—here comes the cool stuff. We're going to make a "Load Text" button. What that's going to do is, in the background, it's going to open up Microsoft Word (you won't even see it open—you can if you want to, but you don't have to). It'll open up Word, read in the text from that document, and then copy it and paste it right in here automatically. With one click, you click the "Load Text" button. It'll open up Word, copy the text, paste it in your database, close Word, and it's all done in VB code automatically with one click.
This will work with any document type that Word can handle. Word can read text files, obviously Word documents, and it can read PDF files, so you can use this to index your PDF files.
If you want to learn even more than that, I cover a lot more in my Access Developer Level 39 class. We'll actually index the individual words inside of that document, and you can see a word count of how many times the word "always" appears, for example. We'll set up an exception table so you can skip words like "always" or "also" and only see the important words. We'll do some more work with the recordsets, and I'll show you how to find records inside of a table. Lots more included in my Developer 39 class.
But the next step: definitely check out my extended cut if you want to learn how to use Word to pull in text from a text file, from a Word document, from a PDF file, and put it automatically in your database. That's in the extended cut.
Silver members and up get access to all of my extended cut videos. Gold members can download the databases that I make in the TechHelp videos, and you get access to my Code Vault and lots more, so check it out.
How do you become a member? Click on the join button below the video. After you click the join button, you'll see a list of all the different membership levels that are available, each with its own special perks.
Silver members and up will get access to all of my extended cut TechHelp videos, one free beginner class each month, and more.
Gold members get access to download all of the sample databases that I build in my TechHelp videos, plus my Code Vault, where I keep tons of different functions that I use. You'll also get a higher priority if you decide to submit any TechHelp questions to me, and you'll get one free expert class each month after you finish the beginner series.
Platinum members get all the previous perks, plus even higher priority for TechHelp questions, access to all of my full beginner courses for every subject, and one free developer class each month after you finish the expert classes.
These are the full-length courses found on my website, not just for Access too. I also teach Word, Excel, Visual Basic, and lots more.
You can now become a diamond sponsor and have your name or company name listed on a sponsors page. They'll be shown in each video as long as you're a sponsor. You'll get a shout-out in the video and a link to your website or product in the text below the video and on my website.
But don't worry. These free TechHelp videos are going to keep coming. As long as you keep watching them, I'll keep making more, and they'll always be free.Quiz Q1. Why is it not recommended to store files directly inside an Access database?
A. It slows down the database and can cause corruption
B. Access databases offer unlimited storage for files
C. It automatically indexes the file contents
D. Files inside Access can be edited directly
Q2. What is the suggested method for storing document information in Access, according to the video?
A. Store the files in a folder and only their path or file name in the database
B. Upload every file as an Access attachment
C. Store only the document name and not the file location
D. Convert all files to images before import
Q3. What additional benefit is provided by copying the text of each document into the Access database?
A. Access can easily search and index the text content
B. It reduces the size of the database
C. It makes the document editing simpler
D. It encrypts the documents automatically
Q4. What is the difference between a short text field and a long text field in Access?
A. Short text fields are up to 255 characters and long text are for large amounts of data
B. Short text fields cannot be indexed
C. Long text fields are for numbers only
D. Short text fields are for images
Q5. In the tutorial, how does the button allow users to open the files from within Access?
A. It uses VBA and the FollowHyperlink command to open files located in the documents folder
B. It directly embeds the file in a hidden subform
C. It copies the file to a temporary folder
D. It emails the file to the user
Q6. How is the documents folder located by the VBA code in the video?
A. By using CurrentProject.Path to get the folder of the database, then adding "\\Documents"
B. By hard-coding the full path (C:\\Users\\...\\Documents)
C. By asking the user to input the folder each time
D. By searching the whole computer for the file
Q7. What is the purpose of using a continuous form for displaying document records?
A. To list all records and show related notes in the footer for the selected record
B. To limit to only one record at a time
C. To display only images, not documents
D. To create separate forms for each document
Q8. What technique is shown for users to add the document text to the database?
A. Manually open the file, Ctrl+A to select all, Ctrl+C to copy, then paste (Ctrl+V) in Access
B. Using an Access macro to automatically import all content
C. Scanning documents into Access using OCR
D. Exporting files from Access to text
Q9. How can users search through the documents' text in Access as demonstrated in the video?
A. By using a parameter query with a LIKE criteria that references an open form text box
B. Only by searching file names in Windows Explorer
C. By setting up a complex relationship with all document tables
D. By exporting the database to Excel first
Q10. What does the parameter [Enter search text] do in the query setup?
A. Prompts the user for input and uses it as the search term
B. Automatically fills in with the first document's name
C. Sorts all documents by date
D. Opens a new form for each search
Q11. Why is it better to get the search value from a text box on a form instead of a parameter prompt?
A. It allows for a more user-friendly interface and faster repeated searches
B. It limits searches to only one document
C. It reduces the database file size
D. It prevents users from making typos
Q12. How does the video suggest opening a form filtered to show only documents related to a specific customer?
A. By passing a WHERE condition with CustomerID to the OpenForm VBA command
B. By manually filtering on the form after opening
C. By creating individual forms for each customer
D. By running an update query first
Q13. What Access feature is used to ensure documents can be related to different customers?
A. Adding a CustomerID field to the document table
B. Creating a separate table for every customer's documents
C. Using only the file name as the relationship
D. Attaching all documents as images
Q14. What will happen if the search text box is empty and the user runs the search?
A. All documents are shown because LIKE "*" matches everything
B. No documents are displayed
C. Only documents with blank text are displayed
D. An error message appears
Q15. What is discussed as a more advanced feature in the extended cut or higher developer courses?
A. Automatically reading document text into Access using Word automation
B. Embedding videos inside Access
C. Disabling all search features for security
D. Encrypting the document table with a password
Q16. What is the recommended way to handle the DocumentID/DocID field on forms?
A. Hide it from the user; it is only for Access's use in relationships
B. Always show it to the users so they can edit it
C. Make it the only searchable field
D. Use it for file names
Answers: 1-A; 2-A; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A; 11-A; 12-A; 13-A; 14-A; 15-A; 16-A
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's video from Access Learning Zone focuses on building a searchable document index in Microsoft Access. This topic came up from a question about how to organize and search hundreds of different documents for each client, specifically whether this can be done easily within Access.
If you have seen my earlier videos on handling images, you know that storing files directly inside your Access database is not recommended. Not only does it lead to oversized databases, but Access is not made for file storage, despite the existence of the attachment data type. Instead, I always suggest storing your files in a folder and saving only their paths or file names in your database. Then, you can set up your Access application to open files with just a click of a button. Another approach that works well is copying the actual text content from each document and saving that text in the database. This allows Access to search through all your document text efficiently.
To follow along with today's lesson, there are several prerequisite topics you should be familiar with. You need to know how to set up a blank template for your Access projects, as this will be our starting point. Make sure you have watched my prior video on displaying images in Access, since the methods there also apply to handling files in general. You should also be comfortable with query criteria, string concatenation, wildcard searches using the LIKE operator, parameter queries, and referencing values from open forms. Finally, a basic introduction to VBA will help, since we will use just a bit of Visual Basic to automate certain tasks. All of these foundational videos are free to watch on my website and YouTube channel. You can find all the relevant links on my website.
Let's get started by preparing a folder to hold our documents. Place this folder in the same directory as your Access database for simplicity. For demonstration, I set up a folder with a PDF, a text file, and a Word document. Each contains a slightly different descriptive line to help identify them.
You need a table to store your document information. The primary fields are an autonumber DocID, a short text field for the document name, another short text field for the file name, and finally a long text field for the document's text content. It is important to note that the file itself is not stored in the database, just its location and any text content you want indexed or searched.
Short text fields hold up to 255 characters and can be indexed, while long text fields are meant for larger amounts of information, such as storing the contents of a whole document. I cover the differences between these types in more detail in my Access Beginner 1 class.
After your table is ready, enter sample data: assign names for each document, specify their filenames, and save the table as DocT. You can decide whether to store just the filenames or full paths if your files are scattered in multiple folders. For most people, keeping all files in a single directory is best.
With the table in place, next create a form to display and edit your document information. I recommend using a continuous form to list multiple records. Set the record source to your document table, bring in the relevant fields, and arrange them as you like. For clarity and usability, you can display the document name and file name at the top and put the document's text content in the footer, so when you click a record, its full text shows below. You do not need to show the DocID to the user; that is for internal use only.
Rather than typing in the text manually, it is much more efficient to copy the contents of each file into the database. Open the file, highlight all text, copy it, then paste it into your form's document text box. If your documents do not change often, this is a quick and easy process, and now your database can search through all this text.
To streamline access, add a button beside each document in your form that, when clicked, opens the associated file. This small bit of VBA uses the FollowHyperlink command to open the file path based on your database's current location. You do not need to hardcode full file paths – use a built-in variable representing the folder where your Access database is stored and tack on the filename. With this method, any document type your system recognizes can be opened from your form: PDFs, Word documents, Excel files, and more.
Searching through document content is a key benefit. Start by building a query using your DocT table. Add the fields you want, including the long text field that holds your document text. To search with wildcards, use the LIKE keyword, wrapping your search word with asterisks to indicate that your target might appear anywhere in the text. Running this query returns any documents containing your specified text, even somewhere in the middle of the content.
To make searching easier for users, use a parameter query so they can enter their desired search text at runtime. An even better enhancement is to add a text box to your main menu form for inputting search words. Then, modify your query so that it gets its criteria from the value of that text box on the main menu form. This way, users do not interact with queries directly; they just type into a search field and get results.
You should always avoid letting users work directly in tables or queries. Your forms and reports are the objects that your users interact with. You can set your search query as the record source for your document form, so when a user enters something in the search box and opens the documents form, only matching documents appear.
If you need to relate documents to specific clients, simply add a CustomerID field to your document table. You can set this up as a number field and use a combo box in your documents form to choose the appropriate customer. If you want to see only the documents for a specific customer, add a button to your customer form that opens the document form filtered to the current customer's records. This filter can be applied using the WHERE condition in the VBA DoCmd.OpenForm statement, referencing the current CustomerID.
This approach lets you expand your database so each customer record can show and search its own set of related documents.
In addition to the main lesson, I have some bonus material for you. In the extended cut available for members, I show you how to add a file picker button so you do not have to type file names. More importantly, we will add a "Load Text" feature that, with one click, opens Microsoft Word behind the scenes, reads in the text from any Word document, text file, or PDF that Word can handle, and automatically places that text into your Access database without copying and pasting. This is all accomplished using VBA automation with Word.
For those interested in advanced indexing, my Access Developer Level 39 course covers how to break down the contents of documents into individual words, track word counts, and filter out common words, giving you a more powerful searchable index.
All of these advanced techniques are available in my extended cut videos for Silver members and up. Gold members can download all of the databases I build in these tutorials and access my Code Vault full of useful code snippets. Platinum members receive full access to all of my beginner courses, download privileges, and developer-level classes after completing prior levels. If you are interested, you can find more details and a breakdown of all membership levels on my website.
Rest assured, my free TechHelp videos will always continue to be available. As long as you keep watching, I will keep making new ones.
A complete, step-by-step video tutorial covering everything discussed here can be found on my website at the link below. Live long and prosper, my friends.Topic List Creating a document table for indexing files
Storing file names and text in Access tables
Understanding short text vs long text fields
Building a continuous form for document display
Adding an Open button to launch external documents
Using VBA FollowHyperlink to open files
Referencing CurrentProject.Path for relative file paths
Manually copying document text into Access
Searching document text using query criteria
Using wildcard LIKE searches in queries
Creating parameter queries for dynamic searching
Referencing values from open forms in queries
Linking form search boxes to query criteria
Changing form record source to filtered query
Adding a CustomerID field for document-client relations
Displaying linked customers using a combo box
Opening document forms filtered by customer
Using VBA DoCmd.OpenForm with WHERE clause for filtering |
