Help on modiying a File Searcher!

[Fipper_SG]

Hello there,



I newly discovered the infinite potential of microsoft access and try to find where this potential can stop!

I found a very interesting File Search Form right here:
http://dfenton.com/DFA/download/Access/FileSearch.html

I often have to put thousands of different files (news article, etc) into a specific folder. Then, i have to log these file to know if they have info concerning specific subject (Canada, France, Politics, etc). I was thinking of using this file searcher to help me in this task.

My question is, do you think it woud be possible to modify this File Searcher to look for keywords logged into a table and then log the results for each keyword into a new table? Or do you think of another way to do this?

For example:
Table ''Keywords'' contains the keywords to be searched:
1 Canada
2 France
3 Politic

When activating the file searcher, it will look for the keywords ''Canada'', ''france'' and ''Politic'' into the files and instead of showing the results, it will log them into their respective tables:
Results for files containing ''Canada'' will be logged into table ''Canada_Results'' (if it doesn't exist, it would create it)
Results for files containing ''France'' will be logged into table ''France_Results'' (if it doesn't exist, it would create it)
Results for files containing ''Politic'' will be logged into table ''Politics_Results'' (if it doesn't exist, it would create it)

Then, to consult the log, people would simply need to open the table!



Do anybody have any idea on how to do this? Of course, if I am able to complete this project I will gladly publish the final result!

[orange]

I think you should step back and try to clearly define the problem. What exactly are you trying to do in plain English?

For example, consider someone with say 10000 (or a million) photos that they can use in different situations. How would you "key word" each photo in order that the appropriate photos could be retrieved when needed, quickly? Suppose if the photos could be retrieved instantly and made available , the person could make significant money.

So the person wants to classify the photos based on the term(s) most often used by the requestor (the guy with the money who is willing to pay).

What sort of terms are involved?
A few ideas:
season of the year, time of day (a time component)
landscape/seascape
animals/people/ nature
economic/political
current/historic
personal/group/social class

instead of a keyword, think in terms of retrieval/classification channel(s).

The more precision in classifying, the better (more relevant) retrieval.

Have you done any anaylsis of your current folder? It sounds very much like we would call a "shoe box". Just dump everything in a shoe box. All the articles are there. So look at each one (every time) to select the article(s) you need. Not very effective, and certainly not the way to design a database for retrieval.

You have to figure out the main topics, secondary topics, attributes, related etc --much like a thesaurus. Classify the articles as they are recorded, and use the classification scheme for retrieval.

Anyway good luck with your project.

[Fipper_SG]

Thanks for the quick response!

I'll try to explain myself better! (being a francophone, english is a huge obstacle when i need to explain what's in my head...)

For the pictures, the search form I linked previously scans the text into the files as well as its properties. So keywords or description of the picturse could be added in the properties of the pictures.

Basically, we store news articles and various kinds of reports/profiles/pictures. These files are stored on a daily basis into a folder named by the current day. So:
Storage Folders\20130213\
Storage Folders\20130214\
Storage Folders\20130215\
and so on...

Currently, I use a database inspired by this Allen Browne's code:
http://allenbrowne.com/ser-59alt.html

This code saves me a lot of time because it automatically lists the files into the table (I modified it to get the hyperlink also), BUT I still have to go over each document to see if they cover specific subjects and then I have to manually write into a column the subjects that the document covers. (Basically, when reading the file, i ask myself, is it talking about a specific country? a specific group? a specific person? When it does, i simply write the word into a ''keyword'' column into the table)

So here I am now. I like how the FileSearch (http://dfenton.com/DFA/download/Access/FileSearch.html) works, but for the keywords that are searched very often, I would like to have the results of the search logged into a table, so it doesn't scan through the files every time (just a daily refresh and then people would just look at the table, a faster alternative).

Do you think it would be better to manualy create the table with an attached form that would have a ''refresh'' button containing the code? And then, how could I tell to log the results into the tables instead of into the list box?

[orange]

Your English est plus meilleur que mon francais. I think you may want to step back and see if you can design a table or a few tables to record info about your articles, profiles. documents, pictures.
Suppose you had a form that was used for identifying the properties of your item; and that form had a number of "categories" or "subjects/sujets".
For each item you could
-identify it as article/document/picture/report/profile
-identify it as Economic/Political/technology/....
-further identify it as tragic/happy/common....
-further identify it as city/country/province/state
-
- this list is designed to capture the various properties/attributes that make sense to you and your colleagues

The only rule is the various subjects/sujets / categories is consistent and there are some rules/regles for their use.

The categoreis/subjects do not necessarily have to be hierarchical. They can be different views/aspects/ attributes.

The whole process depends on unique identification of the article/document/picture. All of the attributes/subject terms are related to that unique number.
So to get all articles and pictures where subject1 =x, subject2=Y and subject3 = H

you would query with something like

Get the file numbers from storage where
subject1 = x and subject2=Y and subject3 = H
and ItemType In(article or Picture)

This would identify the article and pictures -- that is the unique numbers

The issue now is how to relate the unique numbers to you file storage system.

All doable, but requires design and work.

I have recently seen a request for assistance with some one storing 1000's of photos related to various aspects of seeds, flowers etc. They were trying to sort out how to store the photos in the filesystem, and record all the attributes in Access.

Sounds very similar from 30000 feet.

[Fipper_SG]

This is pretty close of how I'm going right now. The problem is, it takes a HUGE amount of time to open every files (we can receive up to 600 files daily) and fill the categories/subjects to the database manually (Up to 3 guys can be tasked to do this all day!)

I'm looking for a way to search directly into the file for specific keywords (much like the explorer search). The main difference is I would like to store the results in some way.

So, a table or query that would be a list of the files containing a certain word.

Here's an example of how it could be used:
Let's say I want to see all the files we have that talks about Canada, I would open the table that list all the files that contains the word Canada. The advantage of having this in an access database over only using the search function in explorer is that people can take notes beside, comments, etc...

[orange]

Are you in Canada?

Also, I have seen many Access articles by David Fenton -- he was a well respected MVP who died in 2011.
His works are often referenced.

Can you provide some sample data?

If your search is so vague as "Canada", you are only looking at 1 attribute. As I said, the better your classification/codification on several attributes, the better your retrieval.

[Fipper_SG]

Yes, in Quebec.

I understand why it is! He seems to be the only one who tried anything like that before!

But I think this way of doing might not be the most efficient... Access is having a very hard time to go through the code when there's close to a 1000 files... It crashes most of the time...

[orange]

Tell me about the files. What format(word/pdf)?? Where are they stored? What is the actual process to do your "Canada" search?

I worked on design and data management of a system before I retired. Now it isn't Access based but try looking for a companies in Quebec using the standard search
at http://strategis.ic.gc.ca/cdncc

then go advanced search and start to narrow the criteria postal code, product etc and see the result
I started with the word Quebec somewhere in any text.( 2720 companies)
Then with detailed search

Search for: All of these words

Look in: All text

Province/Territory: Quebec

City: Montreal

Product: computers

Number of employees: 11 to 50

Exporting or interested in exporting to: United States

Sort Results by: Company name

and isolated 1 company

By using multiple indexes/attributes you can refine your search quickly.

[Fipper_SG]

I understand. However, i don't want to add indexes/attributes to my database. I want to search into the actual files for specific keywords. These keywords are my categories. I don't want to enter them manually.



This is not actually a database. This is more like a File Log... So the specific keywords narrow the log to what I want it to be. (All the files containing the word Canada, all the files containing the word North Korea...)

Am I making any sense?

In fact, my first problem is the biggest one: how can I search file content for keywords through VBA?

[orange]

Did you look at the link I gave?

Your question

how can I search file content for keywords through VBA

is much like a Library.

If you want books about Canada, do you browse the books looking for words in the Titles, or do you go to the indexes/catalogs.

I still need to understand what your files are. Are they word or pdf or???
What is your current process to find the "files" about Canada? step by step

I'm not trying to change your set up. I'm trying to understand what you really are doing?

[Fipper_SG]

The files are mainly ppt, word and pdf. However, we often have xls and txt.

Books are actually a good example. This process would actually go through the entire books and bring everyone mentionning Canada.

Currently, we use a software called DTSearch. It does the job well, but it only gives you the link to the file... It's not an actual log. It's a simple Search engine.

I would like to be able to log the results into a table and then add indexes and attributes.

[orange]

I think we are talking about slightly different things.

Suppose you had a list of subjects and attributes- think of these as a series of checkboxes (just for the moment)
And suppose you assign one of your filenumbers to a "record" .
The record would identify the file and all the attributes that apply to that file.
And suppose you did that for all of your files.

Now if you search by a series of attributes, only the files with those attributes would be identified.
Now since you files are stored electronically (digitized), suppose you have a form(screen display) that listed the file
numbers that were selected. Also suppose on that form was an option to click on the file number and display the
digitized record on the display.

--record has   attribute where X is present-------
Record0000001 XX X  X     X XXX XX X
Record0000002  X    XX X  X      X         
Record0000003    X       XX           X   X
Record0999999    X  XX   XX    X     XX

By searching based on a combination of attributes, you get a list of records with those file numbers.

This is just for concepts.

Comments?

[Fipper_SG]

I'm not sure to understand how this can be used for this project...

[orange]

I don't know either. I still don't understand the underlying issue. If you are really searching through all files based on a single keyword, then the process in my view is not really automated. I was trying to give a hypothetical set up of what you might have if the files (articles etc) were assigned attributes in a consistent and complete manner.

You have access to computers, your files are digitized in a relatively few formats. You need to set up the attribute types and values, and you have to assign attributes to your files, and you have to keep doing it. And retrieval will be very quick and very accurate ( as long as you attribution/codification is consistent).
I'm not suggesting there is no work involved --there definitely is. But if you are having issues with your current system, and it's growing by 600 files per day, what plan/design/concept do you have for a replacement?

I recommend you get more opinions before you do anything.

I'd still like to see what your current process is step by step from creating/getting an article, assigning keywords/attributes, storage, then retrieval. Details.

[Fipper_SG]

I'm sorry, I read back what I wrote yesterday and clearly wasn't making any sense... Let me try again. This is the step-by-step description of what this database is used for:

This database is a ''presentation system'' for files that are put into a storage system. The subdirectories into this storage system are simply the current day (20130213, 20130214...). Nothing more.

My current process goes as follows:
1- I Have a first table that logs the files. It has the following columns:
File Hyperlink, To open the file from the database
File Name
Date imported, To know when the file was added to the database
Details, a quick description of what the file is covering
Comment, to quickly write a comment about the file that can be read to anyone (optional field)

This first table is basically a Read File, a log that can be used to see all the files. This is mainly and almost only used with a date filter on Date Imported (to see the new entries for today, this week). This is the entry point into the database.

Based on the Allen Browne File Listing command (w/ some modifications), File hyperlink, File Name and Date imported are automatically filled for the new records (the new documents added into the storage system) on a simple click of a button.

This is where the time consuming task gets into play. If a file covers a specific subject, normally specific countries, it has to be entered into the details entry as keywords. However, normally, we simply do a quick description that covers all the keywords. (North Korea do a third nuclear test, Syria - Assad is a bad person). The reason for this is because we have people specialised on specific subject, and they don't want to see the hundreds of files they don't need.

Here's a quick example:
''This month we have to summarize the activities of North Korea, Syria, Iran and Mali'', so the files covering the appropriate subject has to be puhed to the specialists that will work on it.

These guys have private tables, the subject tables. In this case, there would be four tables: North Korea, Syria, Iran and Mali. The subject tables are the ''private worlds'' of the specialists and they manage it as they want. They simply have a query that looks for keywords into the Details column of the first table and automatically add the new records to their table. Once pushed to the appropriate subject table, the respective specialists give the files the attributes that they think is the best (where I don't have any control...)

So, basically, the task that I want to facilitate is to fill the details section. When I found this code from David Fenton, I thought: Hey! Could this sort the files for the subject tables automatically?

I'm thinking of using the command that is used in this code that scans through the files for a text string to look for specific keywords for each subject. If there's a positive return for a keyword, it would be sent to the appropriate subject table. For example:

Keywords for North Korea: ''North Korea'', ''Corée du Nord'', ''Kim Jong Il'', Kim Jong Un'', ect...
Keywords for Syria: ''Syri?'', ''Assad''...
Keywords for Iran: ''Iran'', ''Hormuz''...
Keywords for Mali: ''Mali'', ''AQMI'', ''Al-Nusrah''...

Let's say a document talks about Assad's forces retrieving. The keyword Assad woud be positive and the record would be sent to the Syria Subject Table!
Let's say a document talks about tensions between AQMI and Al-Nusrah. The keywords AQMI and Al-Nusrah would be positive and the record would be sent to the Mali subject table!

The problem is this code is HUGE and i can't figure out the code that scan into the files... In fact, I have no idea how this code is working... it is way too complex...