Building a Searchable Document Index in Microsoft Access (Word Docs, PDF Files, Text Files, etc.)

welcome to another tech help 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 catherine 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 catherine if you've watched my images video you know that we really shouldn't store files inside of our access databases there's a ton of reasons why and i cover them in that imaging video but basically access even though it has an attachment data type it's 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 and then you can make a button to simply click on it and open up that document and you can copy the text that's in that document and store that in your access database because access is really good with storing text information so let me show you how to do all of this in today's video okay 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 databases 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 how to use the like keyword you should know parameter queries you should know how to get a value from an open form and 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 all right this video is only about 20 minutes long it teaches you all the basics you don't need to know a lot of vba just a little tiny bit but you got to know where to put it and this video explains all that all of these videos that i just mentioned are all free they're on my website they're 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 okay so here i am in my blank tech help free template this again is a free download you can go grab a copy of 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 and i'm going to put that on my desktop along with my database i want the documents to be under the folder that the database is sitting in and you'll see why in just a few minutes and in my documents folder what do i got in here i 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's got a line that says this is the text version this is the pdf version and of course i've got the word document says this is the word version in it okay so let's minimize this and go back to our database file so the first thing i need to store my document index is a table so let's create a table to store all my information in we'll make the first field the doc id that's my auto number then how about a name for the document short text and then how about the file name file name that'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 but we also do want to put the documents text in here right so doc text and we'll make that long text okay now long text and short text are a little bit different i explain the difference between them and i access beginner one 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 okay but you can't do some things with long text but it's good for just a you know taking a bunch of information just dropping it in there they're great for notes or things like this all right so let's save this table as doc t my document table primary key yes that's my auto number and let's put some data in here all right now the doc name you can type in yourself the doc file name is going to be the name of the actual file itself in the folder which is going to be this stuff okay now 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 gonna say all my documents are in the same folder it keeps things easier okay so i'm gonna put in here my pdf file so let's come over here i'll put this as the pdf version all right and the file name is just pdf file dot pdf okay document text we'll get to in a minute okay then we got the text file and the word file alright so i'll put in here the text version this will be the text file dot txt and then of course we got the word doc which is word file.docx all right 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 so i've got a continuous form right there let's copy and paste that guy ctrl c ctrl v we'll copy and paste doc f this will be my document form design view come into here okay so 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 and let's first bind this form to the table so let's go into data and we'll set the record source equal to doc t so now this form gets its data from my document table all right and we can bring in our fields we'll put them right here so go to add existing fields i don't need the id only time you really need the id is if you want to read it from somewhere else like 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 auto numbers are not for you they're for access to make relationships inside your database you shouldn't care what that document id is all right click drag drop them over here i'm just going to delete these labels okay now i don't need this unbound field let's get rid of that let's slide the document name over to the left right there okay and we'll put our file name next to it let's put it right there we don't need it to be that long let's go about that big now the document text i'm going to put in the footer click and drag and drop it right down there all right 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 all right bring this over like there we're going to leave a little bit of room here for a button and this label is going to be the name of the document all right copy paste we'll slide it over here this is going to be the file name okay let's save what we got save it and close that we can close that let's open it back up again okay looks pretty good so far all right 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 and let's give it a different color let's make it um oh i don't know let's go that how's that look looks pretty good now why do i have this down here in this level here well this is a big field and i want this to be in my nice short list so 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 all right now if i'm on the pdf version i could come down here and type in this is from the pdf file okay and if i go to the next one all right this is the text file if 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 right but it's just showing you this field only down here so this is the way i like to handle notes fields usually okay 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 that we can put right here next to each file we can click on and open up that document okay and this is why i wanted you to watch that intro to vba class real simple one line of code we're gonna put a couple other ones but you 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 and i'm going to put in here just the word open and let's make it a little bit smaller let's go in here 11 points kind of big let's go nine point and i'm going to make this button nice and small like right there how's that look okay all right just like that and let's change this button's name give it a good name i don't like command eight i don't want alex yelling at me for having bad button names open button open btn okay now let's put the buttons code in here now i know the file name is in this box okay and the folder that that file is going to be in is going to be in the documents folder underneath the current folder okay now the current folder is my desktop so for me it's c users amakur i don't know it's emicron's my company name it kind of shrunk it up amakur desktop documents that's the location of that file but i don't want to keep having to type that in all right 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 okay so how do we do that let's go back to our database all right on our button right click build event this brings us into the code builder right there right if you get a little window popped up it says what builder do you want always pick the code builder and that was all explained to my intro to vba class okay so now right inside of here in my open button click this is the subroutine that runs when we click on the open button right we're going to use the follow hyperlink command you might have seen in previous videos i call it as you can type in application.follow hyperlink but you really don't need the application.part just follow hyperlink is enough address is where you want to go now you can actually follow a hyperlink to a website you can follow a hyperlink to a file okay so we're going to use a file now i could come in here and put in c colon backslash whatever desktop backslash text file dot text all right but i don't want to do that i want to replace that path with the current path that the database is in so that is current project dot path all right that's a special variable that represents the folder that your database is in okay now after that i got the folder i want to tack onto that backslash documents right now i'm in the documents folder under the database folder and then where's my file name well that's doc file name that's the field in my form and 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 file name or the one line of code that we need to make this work save it ctrl s come back to your database we're going to close this down and reopen it okay now if i click on this open boom there it is it loads that file okay if i click on this guy click and it open on a different screen hang on there it is just had to copy it over it'll open up whatever this 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 open on a different screen i got four monitors on my desktop so i just had to copy it over here and then the same thing with our word document this will work with pretty much any document type by the way there's the word document you could put excel files in here you could put whatever you want in here okay now how do we get that text into here well that's just a matter of opening the document all right there it is click somewhere in here hit control a that'll select all the text in pretty much every program that i know ctrl c copy right close that come back here and then just paste it in control v and there's all the text that was in that document do the same thing with the text file click ctrl a ctrl c close it come down here paste it in and finally the word document open it up and where it opened up minimize that time right ctrl a copy close it and paste it in now you've got the text of each of those documents in your database and now this is searchable now 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 all right but as you can see if you don't have that many documents it's very easy to just copy and paste it and 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 all right you can do all kinds of search stuff out there but you really if you want to use this text for something or to build a more powerful search index yourself you can and you can do it all inside your access database all right 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 okay how do we do this now right click build event now this guy currently is doing just status hello world but i'm going to say do command dot open form dot f that's it that's all you need to open a form from a button see once you know the little tiny little bit of eb code that's a whole lot faster than running the wizard right 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 right so now and come over here let's close this save changes yes open it back up again i got a button up here on 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 all right i'm going to slide you over here save it there if you move a form and then save it ctrl s it'll save that form's position so the next time i open it up it opens up in the same spot and that handy now i said this was searchable alright how can we search it well let's start with a query create query design bring in your document t and 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 right there's doc x or doc t dot dot text and then field zero i don't want to see it twice what we do is we simply just turn off the second one's show box that'll hide it okay i cover all this in my access beginner course by the way so now the criteria down here is what you put in here if you want to find stuff right you watch the query criteria video and i could put in here something like you know pdf like that but now it's just going to look for f 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 shift f2 what you have to do now is let me slide this down there we go we go like and then star and then pdf star those wild characters says any number of characters here any number of characters there but it's got to have pdf in the middle of it somewhere all right so that's my like wildcard search criteria now when i run that there's the record with the pdf in the document text do that that's your wildcard search okay but i don't want the user to have to you know have multiple queries to run for every different thing so i'm going to use i'm going to use a parameter in here instead so 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 so 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 and there's how you do a parameter query wild card search and now if i run this and i type in txt it uses that as your search criteria okay but i also don't want my users interfacing directly with my queries so i'm not going to put that there instead i want to get a value from an open forum maybe this text box right here let's do that instead and 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 got currently set to a date so we'll get rid of the control source and we'll get rid of the format and we'll name this thing let's call it maybe search text how's that so the name of that box is now search text okay it's search text on the main menu form so i'm going to get that value whatever the user puts in there and i want to have that be the query criteria so instead of enter the search text i'm going to replace this with forms main menu f search text and that's that other video that i wanted to watch which is getting a value from an open form name see i wanted to watch all that stuff so you knew what i was doing here all right okay so let's close this let's save this guy now let's save this as my uh how about doc search queue my document search queue i'm going to close that all right now come back out here if you haven't already save this reopen it okay 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 all right it's still you're technically still editing and it's dirty but now if i come over here and run doc search queue look at that it gets that value for the search parameter off of that form field see and if i run it again right txt gotta hit tab and then open the query okay there's your txt version and yes if it seems like i'm jumping around a little bit that's because this is a tech help 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 okay but now i also don't want to open up this query and use this every time i want to search through text so 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 docs search queue because and i'm going to show you this because i like you watch if i open this up and i hit documents with no parameters in there you see everything because what is the parameter become if that box is blank it becomes like star blank star or just star okay it ignores it okay 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 there's probably the word word in here somewhere okay uh what do i got in the word file okay docx if i put that in there let's put in here doc x i because i just copied my uh my bio off my web page i betcha microsoft word is in yup there it is right there word okay and yes 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 ready for some bonus material wait wait hold on time for some bonus material okay now catherine you wanted to do it so that you could have documents that were basically stored in each customer's record okay so all we have to do now is make this guy relational so if you haven't watched my relationships video go watch this guy okay basically what we're going to do is we're going to come over here to the document table okay we're going to go design view we're going to add a customer id okay that's going to be a number of type long integer default values zero now everybody who's currently in here you're gonna have to give them a customer id all right or or not they don't have to have a customer id if you wanna just see everybody okay i'll go one two three so there's a document for my first three characters customers right one two and three okay all right 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 could put it over here maybe a combo box i've already got a combo box that's linked to customer id it's not my order form if you haven't watched my order my invoicing video go watch that too that explains how i made this form all right that's my invoicing video again you'll find links to all this down below but in the invoicing video we made an order form right and the order form's got a combo box right here that we can pick the customer id so i'm just going to borrow this guy no sense i'm reinventing the wheel right copy come over here and then we're just going to paste that in there and slide it over here slide to the right all right delete that and we gotta kind of resize it a little just a teeny bit just a width a bit there we go we'll copy that label copy paste slide you there and call this customer and customer id is optional okay and if you look in the customer id combo box you'll see it's also got a default value which is right here forms customer f customer id so as long as the customer form is open it will get its value from that form okay so now if i close this save changes yes close this close this if i still open up my documents form you'll see who the customer for that document is okay and if i clear this and do that you'll see everybody's in there now okay but if i want to add another document i can come down here and put a different dodge just put in here abc.text even though it doesn't exist that's okay and you can still pick a customer over there okay 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 well go to the customer form this is bonus bonus material wait wait hold on this bonus bonus i put the second bonus behind it so it's two bonuses but you get the point all right so we'll make a button over here design view i'll just copy one of these guys copy paste right slide it there we'll call this documents or just docs all right whatever you want to call it give it a name we don't want alex yelling at us so not command 30 right we'll call this one we can call this one doc button here too you can have a doc button on multiple forms okay right click build event here we are in the doc button click i want to do command open form doc f but i only want to show records where the customer id equals the customer id on this form so it's going to be comma comma comma chameleon right now comma comma comma because we don't need those parameters we just need the where condition where customer id equals customer id what that does is a little string concatenation it takes the current customer id on the customer form and it says customer id for this record has to be the same as customer id 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 ross documents same close that come over here to a different person james kirk documents there's jimmy kirk's document and notice the next record the blank record is getting its default value now from this form you can add more documents in here and they'll automatically go on jim kirk see how's that did you learn something today i hope you learned some 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 okay that we i know we've covered this before it'll take two seconds i've already got the code in the code vault which can copy it in all right then the new stuff here comes the cool stuff we're going to make a load text button okay 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 i mean you can if you want to but you don't have to all right it'll open up word it'll read in the text from that document and then copy it and paste it right in here automatically so 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 vbcode automatically with one click okay it's cool stuff and this will work with any document type that word can handle word can read text files obviously word documents it can read pdf files so you can use this to index your pdf files all right lots of cool stuff and if you want to learn even more than that i cover a lot more in my access developer level 39 class will 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 record sets 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 tech help 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 tech help videos one free beginner class each month and more gold members get access to download all the sample databases that i build in my tech help 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 tech help questions to me and you'll get one free expert class each month after you've finished the beginner series platinum members get all the previous perks plus even higher priority for techhub 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 to 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 that will 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 tech help videos are going to keep coming as long as you keep watching them i'll keep making more and they'll always be free if you enjoyed this video please give me a thumbs up and post any comments you may have i do try to read and answer all of them as soon as i can make sure you subscribe to my channel which is completely free and click on the bell icon to select all to receive notifications when new videos are posted make sure you click the show more link down below the video to find additional resources and links you'll see a list of other videos additional information related to the current topic free lessons and lots more youtube no longer sends out email notifications when new videos are posted so if you'd like to get an email every time i post a video click on the link to join my mailing list even if you don't want to become a member feel free to donate to my tip jar your patronage is greatly appreciated and will help keep these free videos coming i got puppies to feed now if you have not yet tried my free access level 1 course check it out now it covers all the basics of microsoft access it's over 4 hours long and i just updated it for 2021 you can find it on my website or on my youtube channel i'll include a link below that you can click on and also if you like level one level two is just one dollar yep that's all one dollar and it's free for all members of my youtube channel at any level even supporters want to have your question answered in a video just like this one visit my tech help page on my website you can send me your question there while you're on my site feel free to stop by the access forum lots of good conversations happening there be sure to follow my blog find me on twitter and of course youtube once again my name is richard ross thank you for watching this tech help video brought to you by accesslearningzone.com i hope you enjoyed this video and that you learned something today i'll see you again soon you

Channel: Computer Learning Zone

Views: 17,571

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, Microsoft Word Automation, Convert PDF to Text, Convert DOCX to Text, VBA to Copy Text to Database, Browse and Pick a File

Id: KAufKs6_CSU

Length: 31min 6sec (1866 seconds)

Published: Mon Jul 25 2022