Automated OLE Linking

[Lightning]

Can anyone help with setting up an automated process for linking word documents to a form?

I have a database that tracks legal processes. I now need to link court orders (MS Word documents) to the relevant case. I can link these directly using Insert|Object without problems. However, the staff that will be doing this all the time are computer-phobes, and I would like to make the process as easy as possible for them.

I have been through my reference library, but everything talks about automating and manipulating documents that are already linked. Nothing shows anything about automating the actual linking process itself. Presumably other people have needed to do this. Is it so simple that I'm completely overlooking it, or am I just being slow?


The word documents will always be in the same sub-folder, and will have names similar to "RT01415".

Any help greatly appreciated.

Thanks in advance
Lightning

 

[waynerad]

Sorry,
this is not an answer to your problem, I have alomost the same problem as you. I would like to know if you have had any success in solving it?? If you do could you give me a email? That would be greatly appreciated. I will do the same for You.
vwradford@hotmail.com

 

[Aivars]

Hi, Lightning!

I think better way is using hyperlinks for other app opening from Access form. Create hyperlink field and put into this field file's location string (full path with disk file name) for each record. Then create textbox on form with Data source as this hyperlink field. Now you'll open file by simple mouse clicking on this field.

Aivars

 

[Lightning]

Thanks Aivars, but I can't do it like that. But the opening of the documents from the database is not the problem - automating the initial linking of the document is.

These documents are sent twice weekly from the local Court to the Office running this database. The people who are operating the database need to have the process of linking the files to the database to be as simple as possible.

Does anyone have any suggestions for automating the actual linking process?

Lightning

 

[Aivars]

Hi, Lightning!

I have DB wich is designed for pictures archiving special for any Tek-Tips forum's member. There are codes for table updating. Maybe you would take some ideas in these? Procedure find all MS Office files of selected folder. Then add (or rewrite) file Names with full paths into table "FileList". On form is created progress bar. Also it is updating in this procedure:

Private Sub Form_Timer()
'Update table "FileList"
On Error GoTo Err_Form_Timer
Dim fs, f, s
Dim strSQL As String
Dim rst As Recordset
Dim i As Long
Dim i0 As Long
Dim strFolder As String
Dim strFileName 'As String
Dim lngSubFolderCount As Long
Dim blnImageOnly As Boolean
Dim strFoundFile As String
Dim lngUpdate As Long
Dim btyAddToTable As Byte
Dim lngFileId As Long

Dim frm As Form
Dim ctl As Control

strFolder = "C:\AiLa\MySampleDB" 'Folder Name in what are located documents
DoCmd.Hourglass True
With Application.FileSearch
.NewSearch
.LookIn = strFolder 'File search folder
.SearchSubFolders = True 'If True then search in subfolders, too
'.FileName = strFileName 'File name mask

'All office file types
.FileType = msoFileTypeOfficeFiles

If .Execute() > 0 Then
If DCount("FileId", "FileList&quot > 0 Then

btyAddToTable = MsgBox("Do you want to add file names of selected folder?" & vbLf & vbLf & _
&quot;For adding file names to existing list click <Yes>&quot; & vbLf & _
&quot;For removing existing file names from table click <No>&quot; & vbLf & _
&quot;Click <Cancel> if you want to cancel file list update operation!&quot;, vbExclamation + vbYesNoCancel + vbDefaultButton3, &quot;File List&quot

End If
Select Case btyAddToTable
Case 0, vbNo
'Delete all records from table &quot;FileList&quot;
DoCmd.SetWarnings False
DoCmd.RunSQL &quot;DELETE FileList.* FROM FileList;&quot;
DoCmd.SetWarnings True
Case vbCancel
GoTo Exit_Form_Timer
End Select
'Open recordsed for adding files properties into table
strSQL = &quot;SELECT FileList.FileID, FileList.FileNameHyp, FileList.FileName, FileList.FileType, FileList.FileCreatedTime, FileList.LastModifiedTime, FileList.LastAccessedTime, FileList.FileSize, FileList.InsertDate FROM FileList;&quot;
Set rst = CurrentDb.OpenRecordset(strSQL)

IsFilesOnList = True
'Progress bar updating
Me.lblWait.Visible = True
Me.txtPercents.Visible = True
DoCmd.Hourglass True
i0 = .FoundFiles.Count

Me.prbProgressBar.Max = i0 'Set ProgressBar max value
Me.prbProgressBar.Visible = True

For i = 1 To i0
'Updates ProgressBar
Me.prbProgressBar.Value = i
Me.txtPercents = Round(i / i0 * 100, 0) & &quot;%&quot; 'Function works only in Ac2000
Me.Repaint
'Set 'FileId' for new record
If Not IsNull(DMax(&quot;FileID&quot;, &quot;FileList&quot) Then
lngFileId = DMax(&quot;FileID&quot;, &quot;FileList&quot + 1
Else
lngFileId = 1
End If
'Updates table &quot;FileList&quot;
strFoundFile = .FoundFiles(i)
If btyAddToTable = vbYes Then
rst.FindFirst &quot;FileNameHyp like '*&quot; & strFoundFile & &quot;*'&quot;
If Not rst.NoMatch Then
'If found change only arguments
rst.Edit
Else
rst.AddNew
rst!FileID = lngFileId
rst!FileNameHyp = &quot;#&quot; & strFoundFile & &quot;#&quot; 'Separators &quot;#&quot; are for hiperlink field in such case
rst!FileName = Dir(strFoundFile)
lngUpdate = lngUpdate + 1
End If
Else
rst.AddNew
rst!FileID = lngFileId
rst!FileNameHyp = &quot;#&quot; & strFoundFile & &quot;#&quot; 'Separators &quot;#&quot; are for hiperlink field in such case
rst!FileName = Dir(strFoundFile)
lngUpdate = lngUpdate + 1
End If
rst!FileType = FileArg1(strFoundFile, &quot;Type&quot
rst!FileCreatedTime = FileArg1(strFoundFile, &quot;Created&quot
rst!LastModifiedTime = FileArg1(strFoundFile, &quot;Last Modified&quot
rst!LastAccessedTime = FileArg1(strFoundFile, &quot;Last Accessed&quot
rst!FileSize = FileArg1(strFoundFile, &quot;Size&quot
rst!InsertDate = Now()
rst.Update
NextFoundFile:
Next i
GoTo EndOfListUpdate
Else
MsgBox &quot;There are not files on directory ''&quot; & strFolder & &quot;''&quot;
GoTo Exit_Form_Timer
End If
End With

EndOfListUpdate:
If lngUpdate > 0 Then
MsgBox &quot;There was added &quot; & lngUpdate & _
IIf(i - lngUpdate - 1 > 0, &quot; and updated &quot; & i - lngUpdate - 1, &quot;&quot & _
IIf(blnImageOnly, &quot; image &quot;, &quot;&quot & _
&quot; file names of folder ''&quot; & _
strFolder & &quot;'' &quot; & IIf(lngSubFolderCount > 0, &quot; of &quot; & lngSubFolderCount & &quot; subfolder(s) &quot;, &quot;&quot & _
&quot;into table ''FileList''&quot;, vbInformation, &quot;File list creating&quot;
Else
MsgBox &quot;There was not any file added.&quot;, vbInformation, &quot;File list creating&quot;
IsFilesOnList = False
End If

Exit_Form_Timer:
DoCmd.Hourglass False
DoCmd.Close
Exit Sub

NotComplete:
MsgBox &quot;Invalid folder name&quot;, vbExclamation, &quot;File list creating&quot;
Resume Exit_Form_Timer

Err_Form_Timer:
If Err.Number = 70 Then 'Permission denied
Resume Next
Else
MsgBox &quot;Error No &quot; & Err.Number & vbLf & Err.Description, , Me.Name & &quot;: Sub Form_Timer&quot;
Resume Exit_Form_Timer
End If

End Sub
'--------------------------
Function FileArg1(FileSpec, strArgType As String) As Variant
'This function return one file property
On Error GoTo Err_FileArg1
Dim fs, f, s
Set fs = CreateObject(&quot;Scripting.FileSystemObject&quot
Set f = fs.Getfile(FileSpec)

Select Case strArgType
Case &quot;Type&quot; 'File Type
FileArg1 = f.Type
Case &quot;Created&quot; 'File Created
FileArg1 = f.DateCreated
Case &quot;Last Modified&quot;
FileArg1 = f.DateLastModified
Case &quot;Last Accessed&quot;
FileArg1 = f.DateLastAccessed
Case &quot;Size&quot;
FileArg1 = f.Size
End Select

Exit_FileArg1:
Exit Function

Err_FileArg1:
Resume Exit_FileArg1

End Function

Aivars