How to program the Keywords Searching Function?

[beemomo]

Hi, I am attempting to develop a library system in Ms Access 07 but I face problem with the search function of the system. The search function is just like a search engine, the user type in a word and the system will start looking for the documents which contain the correspondent keyword after the search button is clicked. I have a table name tblDOC which contains the following fields:

[Doc_ID], [Category], [Organisation], [Doc_Code], [File_Name], [Edition], [Doc_Title], [Year], [Author]

Note: [File_Name] is a hyperlink field

The documents are stored in different folders inside C:\Library, and also, the folder may have other subfolders inside to store the document. The documents are .pdf and .doc. file.

My Question is:
How should I write a program in VBA to search the keywords in these documents , and return the search results in the MS Access report? So that when user clicks on the [File_Name] field, the document will be opened.

Thank you and hope to hear from you guys very soon as I am stuck and do not know how to continue.

[nico5038]

I'm afraid that a "dynamic" search will cost too much time to be of any value for a user.
I would index the documents first and store the words in an Access table on forehand with a link to the document row(s) it's used in.

On the other hand I would probably not even try this as when I need to do a file search I just use a specialized tool like a local google search engine or (for textfiles) the FileSearch of TextPad.

Perhaps investigating how to link your aplication with the google desktop search might be a way to go.

Nic;o)

[missinglinq]

How should I write a program in VBA to search the keywords in these documents , and return the search results in the MS Access report?

You don't "write programs in VBA." You develop applications in VBA, that's what the A stands for, "Applications!"

Access is used to develop databases, not search engines, which, as Nic;0) has hinted at, is what you're trying to do here. Nor is Access intended to be used simply as a reporting tool ala Crystal Reports.

Access can link to a txt file or a pdf file, given the proper path, and open them for viewing, given the proper code, but this is not a simple or speedy process.

To think of typing in one or more keywords and having Access search thru multiple folders/subfolders with multiple documents in each, open each and every document and do a word by word search, and then retrieve the target document(s) for viewing, using another application, is not ill advised, but, to be blunt, simply ludicrous.

In programming, as in most professions, the secret to success is to use the right tool for the job at hand! Given a great deal of time and a great deal of experience in a language such as C/C++ or VB6 or VB.Net, you could , I suppose, develop such a program, but you should really give serious thought to the advice Nic;0) has given you.

Linq ;0)>

[beemomo]

Thanks for your replies, nico5038 and missinglinq.

Yes, you guys are so right. Anyhow, I have found a solution which is more direct and simple. Based on the user's requirements, they will only search the keywords from three layers of the documents instead of the whole document, which are - Title, Table of Content and Index.

From there, I will use the two "Memo" fields in my database table to store the keywords which plugged out from Table of Content and index from the pdf document instead of using "Text" field since "Memo" field can store up to 1 gigabyte of characters.

As a result, the searching function is using SQL condition LIKE with wildcard to retrieve the result.

This is the easiest way that I discovered. Thanks for the advices, I really appreciate it much. Cheers!

[missinglinq]

Sounds like a much more sensible approach! Glad we could help!

Linq ;0)>

[Ben33h]

Hi! i know i know.. like what the hell am i doing at an old forum right... well its actually just the right topic that i need help on.. a little bit different, and probably a lot easier!

I've created an access file/database for my mom, for the sake of organizing her recipes. But i want to create a search engine (or even more simpler, a search form)... a search that will only need to search within one database, one file.

Could anyone offer me some help?
thank you!!