Hyperlinking to external word and excel files from within access

[reb0101]

hey all,
I would very much appreciate any help or ideas on how to do this as I am stumped.
I need to develop an access database to track documents but also link to them.
I’ll explain what it needs to do;

Every day there is a numbered (and titled) Word format document that is sent.
Most, but not all of the time an accompanying excel file is also sent. The excel file is used for updates to the word document of the same name.
Lets say the word document is titled DW101.1.doc and the Excel file is called DE101.1.xls
The first would signify it is a Word document by D (being short for Document) and W for Word.
The 2nd would be D for document and E for Excel, and then a number which is 101.1.

They are saved in a folder on the hard drive.
Each time these documents are recd then the Access Database is opened and info about those documents is entered. The document name, date, subject, number etc.
There will also be 3-4 fields that are also filled out with terms from the content of the document that could be searched by those 3-4 fields.
So as each document is recd, it’s opened and ‘scanned’ for relevant terms and or content.
That content is what will be filled in the search fields and then the subject will be the ‘main’ searchable item.

All this so far I can do with no problem.
It’s this part that’s making me crazy.
What I would really like to do is when the document(s) come in and are saved in the folder, from access you can open a dialog box, browse to that folder, and click in the file to associate it with the entry you are making for it in the database.
That way when a search is done with terms or words or subjects that match that document it will bring up the results of the search as a hyperlink that will open that document.

The people that will be using this are not very computer literate to say the least.
I could create a link to the document but I would have to be in design mode and it won’t show up as a record in a table.
Any ideas would be very much appreciated………… …..

reb0101

[NeoPa]

You can insert links to documents in an OLE field but to search through a document would be more complicated and would involve writing code to process the Word & Excel documents from within your Form or whatever.

Check out this other thread - particularly with regards to the 'blobs'.

http://www.thescripts.com/forum/thread554620.html

[pks00]

Regarding this table that has scanned "relevant terms"

Im assuming there is a link to the documents table and with this u can get to the document
e.g.
tblDocument
DocumentID
DocumentName
etc

tblTerms
TermID
DocumentID (link to tblDocument)
Term


ok, so u have a form based on this (can be continuous or subform)
Im assuming here the files are held on a directory somewhere and its just the docname u store in the db.

SELECT DocumentID, DocumentName, Term
FROM tblDocument, tblTerms
WHERE tblDocument.Doc umentID = tblTerms.Docume ntID

You have a textbox (txtSearch) and a search button (cmdSearch)
on the click event, u can display the results

private sub cmdSearch_Click ()

Me.Filter = "Term Like '*" & Me.txtSearch & "*'"
Me.FilterOn = True

end sub


Now the results displayed, ensure the DocumentName has a click event, one which opens the document using FollowHyperlink
e.g.

private sub DocumentName_Cl ick()
Dim sParent as String 'Here I am defining it locally, ideally u want it held in a table as a config value or constant

sParent = "C:\mydocs\ "
if dir$(sParent & me.DocumentName ) = "" Then
msgbox "Cannot find document " & Me.DocumentName
else
FollowHyperlink sParent & Me.DocumentName
end if
end sub