Indexing Database for Documents

[weigoldk]

I found a discussion in this forum that somewhat addresses my issue, but I'd like to expand it and throw it out here again. The discussion was in thread 700-994821 and I read some great tips there.

I need to devise an electronic system for indexing documents. For this particular issue they are all MS Word documents (created over the past 12+years). The documents are all formatted similarly. They consist of a table with a topics in the first column and then a narration (usually just a paragraph or two, but sometimes longer) for each topic in the second column. I need to create an index that is based on the topic. If a person searches or browses to a topic, they need to see all documents that made reference to that topic and they need to be able to link to the narration part.

I used suggestions in thread mentioned above to create a data access page with links to the document name, but I haven't figured out how I can link to the narrative INSIDE the document.

In the end, this is what I visualize. A form (data page or whatever) with the following fields: topic, document title, narrative, physical location of paper document (state requirement to keep paper copies). At the minimum, I need to be able to search on topic and document title.

Please feel free to tell me that I'm headed in the wrong direction here and direct me otherwise. Thanks in advance for your thoughts.

 

[CautionMP]

weigoldk,
It sounds like the [tt]FileSearch[/tt] object is about to become your best friend. It will allow you search for files by name a directory and/or search for contents within an Office file. To see if the concept will work for you:
[ul][li]Open any office application.[/li]
[li]File => Open[/li]
[li]In the Open dialog box, click Tools => Find[/li]
[li]Files of Type: Word Documents, Add to List[/li]
[li]Text or property includes the words: someword, Add to List[/li]
[li]Look In: the directory your Word Documents are in.[/li]
[li]Find Now[/li][/ul]

If that looks like it will work for you the next step is to decide how to use it.[ul]
[li]You could incorporate it into your user interface and let the user search for files at run time.[/li]
[li]You could build a VBA routine that uses the [tt]FileSearch[/tt] object to populate an index table that users could then query to find documents.[/li]
[li]A combination of the two.[/li][/ul]

One note on the narrative, if it contains more than 255 characters it could be a little problematic to store in a Access database. To make a long story kind of, er, eh, long, to store more than 255 characters in an Access database you either have to split your text into chunks of 255 characters and store in multiple fileds or store it in a Memo field. There are some major drawbacks to both methods, it doesn't mean you shouldn't use them, just be aware.

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[weigoldk]

Thanks CautionMP,

Do you have any recommendations on documentation for the use of FileSearchObject in VBA? Or examples of using any such objects in VBA? I'm a real VBA novice.

 

[CautionMP]

weigoldk,
I don't have a really good 'bullet proof' resource for you since everyone's needs/skills are slightly different so I would start with the Help files and suppliment it with:[ul]
[li]Microsoft: Access Modules (VBA Coding) Forum[/li]
[li]VBA Visual Basic for Applications (Microsoft) Forum[/li]
[li]MSDN[/li][/ul]

If you get stuck on something, post your question in one of the above Tek-Tips forums and there are numerous people that will be more than happy to jump in and help out.

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[PHV]

Have a look here:

http://msdn.microsoft.com/library/en-us/vbaof11/html/ofobjFileSearch1_HV05221253.asp

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886