Need your opinion - store/retrieve Word and Excel files in Access 2003

[ShanVel]

Folks I need your help; just wanted to get your opinions here.

I work in a small engineering group and we have lots of reference documents in word and excel which we typically use for any projects. Currently, all these files are stored in Lotus Notes database. Unfortunately, they are pulling the plug on Notes license starting this fall. Therefore, I have been asked to see if there is a way we can store these files in Access as a repository and query the database whenever we need some information.

In order to avoid the database size getting too big, my thought was to store the .xls/.doc files as an “OLE object” data type, keep the files in local hard drive and create a link in a form to give the user to retrieve the information.

Do any of you have any suggestion on what is the best way to handle this?

Your input is highly appreciated.

Shan.

 

[Pat Hartman]

What you are talking about isn't an OLE object type. OLE objects are stored within the .mdb. Your best bet with A2003 or earlier is to store only the path names to the documents and leave the documents in external directories.

With A2007, I understand that the bloating caused by storing OLE objects in the database has been controlled but you might want to verify that.

 

[ShanVel]

Thanks Pat.

Is there any other way of handling this problem other than using OLE object type? Also, I don't have A2007, but A2003.

Shan.

 

[The_Doc_Man]

The trick is not that the files are stored within the DB but that their references (paths) are stored. It takes some doing, but here is one viewpoint.

1. Set aside a folder on your computer that serves this database to others. Make this folder the place where you keep your documents.

2. In the DB, keep only the file name and path and type. Types will be .DOC for MS Word or .XLS for MS Excel, so you can look at the last three characters of the file spec to figure out which item it is.

3.. Build a form that lets you see the document references. That form will probably include a sub-form with the actual file names and a parent form for the project specifics.

4. In the sub-form, in the part that displays the name of the file you might wish to see, have an OnDblClick event. If you double-click on the file spec, have the code look at the spec and create an application object for either WORD or EXCEL, giving it that file spec as the file to open.

5. The person opening the file must remember to close the file when done with it.

You can read up on VBA and Application Objects, double-click event code, and building form/subform structures by browsing this forum and Access Help.

If you choose to take this approach, just remember that book, "Everything I Needed to Know I Learned in Kindergarten." Here, the things you need to know are: If you open it, close it. If you take it out, put it back. Leave it as neat as it was when you found it.

 

[ShanVel]

The_Doc_Man: Thank you very much for your detailed tips. I am fairly experienced with VBA and other access objects and let me give a try.

I have another question. Once I have built the database and running I would like to add search functionality. The user should able to do a search with some key words by typing in a text box of a form and be able to search either in the Excel or Word files and then open up that file to view. Is it possible in Access 2003?

Shan.

 

[The_Doc_Man]

Now you are getting into a little issue based on an "old programmer's rule."

Access won't tell you anything you didn't tell it first, or tell it how to find.

If you have a keyword list and you manually enter those keywords in a table with links to the documents, yes. Piece of cake.

If you don't have a keyword list then searching each document for that word is going to be time consuming and hellishly slow, getting worse by the moment as you add more documents to the database.

The "right" way to do it is to predefine a list of keywords for each document in a keyword table. This keyword table might be as simple as

tblKWDSRH
DocumentID, foreign key to the document table
TheKeyWord, text, the keyword to be sought.

You make this a many/one relationship to a document. You enter the list when you enter the document. (Might be able to do this with a tabular sub-form as a control on the master document entry form.)

Any other approach is going to be overwhelmed by the search issues including this fact: Access is dumber than a box of rocks. If it reads a document, there is no guarantee that the words you seek will be relevant to the document's title. The word could just as easily have been in the document one time as a passing reference only. I.e. suppose your keyword was ASPIRIN and someone mentioned in a diary entry that they had a headache so took some aspirin. But the diary has nothing to do with aspirin. A search of the document would provide the hit, but there would be no relevance to that hit.

 

[ShanVel]

The_Doc_Man: Thank you very much and I have to tell you that I need to shift gears between working on A2003 and doing my regular engineering functions. So, my apology, it took a while to check the response on the forum.

The keyword table looks like a great idea and the way to proceed. I will share this idea with users in our group and may be back to this forum for help not often but if I need.

Again, appreciate your time for the detailed response.

Shan.

 

[odin1701]

I agree - keywords in a table will be a much better solution.

You can always update to add additional keywords or documents, relatively easily.

 

[David Eagar]

1. Set aside a folder on your computer that serves this database to others. Make this folder the place where you keep your documents.

I have found from experience that this works best if the folder path is kept as short as possible (eg C:\Folder\document) and the folder is close to where the database resides

I assume this is due to 'network issues' (always a good fallback when you can't find anything else to blame)

 

[ShanVel]

Thank you everyone for all your inputs and let me give a try.

Shan.

 

[Excellent]

Hello Shan,

I'm just curious - why is your list of files being kept in a database?

My partner does a similar thing, but in a spreadsheet, with the option of including text descriptions beside the essential path/type etc data, and thus with full search functionality. Although you still have to prepare an entry, it is more fluent than a Keyword list/table.

Although it is easier for people to "tinker" with it if they choose, which may be a concern, it works perfectly, and saves loads of directory searches, desktop icons etc, plus it is very quick and easy to set up and operate.

I'm interested to learn if a database approach gives you more functionality than the spreadsheet method, and how/why.

Cheers,

Excellent.
Victoria, Australia