Display MS Word Documents in MS Access

[tobypsl]

Hi - I am using Office 2003 and would like to know how to display MS Word files in MS Access forms.

One of my forms is based on a table that has, as one of it's fields, the filepaths to MS word documents. Each record in the table has a unique MS Word file related to it.

What I would like to do is set up the form such that the MS Word file is displayed within a control on the form.
So for example the table may look like this:

ID Document
1 C:\Documents and settings\user\my documents\file1.doc
2 C:\Documents and settings\user\my documents\file2.doc
3 C:\Documents and settings\user\my documents\file3.doc

etc etc

So when the form is on record 1 it displays, in a sub form or control box, the file1.doc, when it is on record 2 it displays file2.doc etc etc.

Displaying a fixed MS Word file is simple enough, any ideas on how to do the above appreciated.

It is also important (since there are around 25000 records inthe table and therefore 25000 word docs) that whatever solution I use does not 'bloat' the database ie. I don't want the MS Word files saved in the Access DB.

 

[fneily]

I haven't displayed Word docs in Access because they're usually too big. Maybe you could use one of these ideas:

Example of opening Microsoft Word documents from an Access Database

http://www.databasedev.co.uk/downloads.html

Using Hyperlinks in Microsoft Access Applications

http://www.informatik.uni-bonn.de/I...steme/SS01/Access/BLDAPPS/CHAPTERS/BA21_2.HTM

Also, here's some code where you pass the filename from a textbox to a function:
Private Sub Procedure_Click()
DoCmd.Hourglass True
GetInstrPro Instpro:=Me![calibration information.Procedure]
DoCmd.Hourglass False
End Sub

Sub GetInstrPro(Instpro)
Dim appWord As Word.Application
Dim wd As String
On Error Resume Next
wd = "c:" & "\" & Instpro & ""
AppActivate "Microsoft Word"
If Err Then
Shell "c:\Program Files\Microsoft Office\Office\Winword /Automation", vbMaximizedFocus
AppActivate "Microsoft Word"
End If
On Error GoTo 0
Set appWord = GetObject(, "Word.Application")
With appWord
.Visible = True
.Documents.Open Filename:=wd
.ActiveDocument.ShowSpellingErrors = False
End With
Set appWord = Nothing
End Sub

 

[Remou]

You could use an unbound control to display the document based on a file path. This would not cause bloat. In Access 2007, I believe it is possible to store objects more efficiently. Finally, it is possible to use BLOBs

http://support.microsoft.com/kb/194975/en-us.

Public Function DisplayDoc(ctlDocControl As Control, strDocPath As Variant) As String
  On Error GoTo Err_DisplayDoc
  
  Dim strResult As String
  Dim strDatabasePath As String
  Dim intSlashLocation As Integer
  
  With ctlDocControl
      If Trim(strDocPath & " ") = "" Then
          .Visible = False
          strResult = "No document name specified."
      Else
          If InStr(1, strDocPath, "\") = 0 Then
              ' Path is relative
              strDatabasePath = CurrentProject.FullName
              intSlashLocation = InStrRev(strDatabasePath, "\", Len(strDatabasePath))
              strDatabasePath = Left(strDatabasePath, intSlashLocation)
              strDocPath = strDatabasePath & strImagePath
          End If
          .Visible = True
          .Enabled = True
          .Locked = False
          ' Specify what kind of object can appear in the field.
          .OLETypeAllowed = acOLELinked
          ' Class statement--optional for Excel worksheet.
          .Class = "Microsoft Word Document"
          .SourceDoc = strDocPath
          ' Range statement--optional for Excel worksheet.
          '.SourceItem = "R1C1:R7C4"
          ' Create the linked object.
          .Action = acOLECreateLink
          ' Optional size adjustment.
          .SizeMode = acOLESizeZoom
          strResult = "Document found and displayed."
      End If
  End With
      
  Exit_DisplayDoc:
      DisplayDoc = strResult
      Exit Function
  
  Err_DisplayDoc:
      Select Case Err.Number
          Case 2101       ' Can't find the picture.
              ctlDocControl.Visible = False
              strResult = "Can't find document."
              Resume Exit_DisplayDoc:
          Case Else       ' Some other error.
              MsgBox Err.Number & " " & Err.Description
              strResult = "An error occurred displaying document."
              Resume Exit_DisplayDoc:
      End Select
  End Function

Usage:

  Private Sub Form_Current()
     DisplayDoc Me.oleShowDoc, Me.txtFileName
  End Sub

The document can be opened by double-clicking on the ole control.
From:

http://wiki.lessthandot.com/index.php/Display_Word_Document_In_Form

 

[PHV]

Have a look at the FollowHyperlink method.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[tobypsl]

Hi fneily, Remou and PHV - thanks for your inputs. I have tried the suggestions by Remou and PHV so far.

The unbound OLE contraol does not generate an error but the control disappears leaving a blank space on the form as soon as I open the form. This looked like the most promising method as it opens the doc on a control on the form allowing me to continue using controls on the form.

Then I tried the FollowHyperlink method suggested by PHV and this generates a run-time 490 error - cannot open specified file. (it also prompts with a "hyperlinks can be harmful etc" warning message - which wuold be good to not have to click through each time).

I haven't yet tried the code suggested by fneily as there appears to be an issue with the file locations or something else so I anticipate that not workin guntil the problem is sussed.

For Additional Info:

The DB currently resides on a J: Drive - which is a networked location for shared access files.

The docs I was opening are on the C: local drive of the computer I work on. I can open any of these docs without a problem directly from my computer.

When I changed to testing with docs on the same drive as he DB - using a relative file path - there was a lengthy delay, egg timer pause, then the same Run Time error 490 message.

I then copied the form and table to a new DB on my local drive and tried it fro there - resulting in the same error message.

So I am wondering if there is an issue with either the location of the DB relative to the files I am trying to open or permissions or something else ?

Any ideas welcome as the methods proposed look to be acceptable solutions to the initial question.

 

[CaptainD]

The DB currently resides on a J: Drive - which is a networked location for shared access files.

Where I work, my "J" drive might be "K" drive on another computer depending on how many drives they have access to and how they are mapped.

You might find it will work better if you use
\\ServerName\PublicShareFolder1\PublicShareFolder2\FolderYouWantName path

 

[Remou]

The code I posted will set the visible property of the control to false if the file is not found, this is the section:

          Case 2101       ' Can't find the picture.
              ctlDocControl.Visible = False
              strResult = "Can't find document."
              Resume Exit_DisplayDoc:

You can use Dir to check if the file exists.

Dir "C:\Docs\A.doc" will return an empty string if the document does not exists. This should help you to isolate the problem, however, it seems likely that CaptainD has already spotted the problem.

 

[tobypsl]

the problem turned out to be that, when testing the code, I did not append filetypes to the filenames I used in the test table. When the filetype is appended eg. file1.doc OR file2.rtf they open fine with both methods tested.

I think the filenames referred to in the actual DB have different filetypes eg .doc and .rtf etc however the table with the filenames also has the filetype so this shouldn't be an issue.

Remou - your code does exactly what I wanted. The files are all variable length - so I am wondering is there a way to put scrollbars on the unbound control ? otherwise I will have to actually open some of the docs anyway.

 

[Remou]

You should be able to open the document by double-clicking the control. There are various size modes you can use:

.SizeMode = acOLESizeZoom

Or Zoom on the property sheet should show the whole document. the other choices are Stretch and Clip. Stretch is not much use for documents and Clip will show a portion of the document.