Attching files

[Andy Kim via AccessMonster.com]

Can anyone help with providing an option to attach files such as MS Word or
Excel document to a database record and be able to view without having to
store the document as an OLE object? I want to be able to link the darabase
record to many related files.

Thanks,

[Rick Brandt]

Andy Kim via AccessMonster.c om wrote:

Can anyone help with providing an option to attach files such as MS
Word or Excel document to a database record and be able to view
without having to store the document as an OLE object? I want to be
able to link the darabase record to many related files.

Thanks,
What I do...

Add a ComboBox whose RowSource is a table where you store the paths to the files
attached to the current record. UNION the RowSource so it can have "< Add New" as the first choice.
In the AfterUpdate method of the ComboBox check to see what was chosen. If an
existing attachment then use Shell or FollowHyperlink to open it. If "< Add New" was chosen then display the FileOpen dialog which the user can use to select

a new file for attaching. The path returned is then stored into the table used
as the RowSource for the ComboBox and the ComboBox is Requeried.

In my case the file path table also has a description field which I prompt the
user for with an InputBox and that is the column actually displayed in the list.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[Br]

Andy Kim via AccessMonster.c om <fo***@nospam.A ccessMonster.co m> wrote:

Can anyone help with providing an option to attach files such as MS
Word or Excel document to a database record and be able to view
without having to store the document as an OLE object? I want to be
able to link the darabase record to many related files.

Thanks,

Sure. I simply store the full path name of the file in a text field
(using a file dialog to choose your file). I have a table allowing
multiple file "attachment s" per employee (displayed in a Listbox).

I then have some code to open the file (should work with any file
extension recognised by Windows and if the program accepts a file to
open in the command line when callling it). The code calls a function
that gets the program associated with the file extension.

eg. (This is a bit messy but should give you the idea)

Private Sub btnOpen_Click()

'get program associated with file extension (pass file and folder
seperately)
ProgPath = fFindEXE(Me![FileList].Column(1), Me![FileList].Column(2))

'strip null characters if any??
For i = 1 To Len(ProgPath)
c = Mid(ProgPath, i, 1)
If c <> Chr(0) Then
NewProgPath = NewProgPath & c
End If
Next

'strip spaces from start/end of string
If Len(NewProgPath ) > 0 Then NewProgPath = Trim(NewProgPat h)

'remove /n being added on Win95c computers??
If Mid(NewProgPath , Len(NewProgPath ) - 1, 1) = "/" Then NewProgPath =
Left(NewProgPat h, Len(NewProgPath ) - 2)

FilePath = Me![FileList].Column(2) & Me![FileList].Column(1)

'construct as if writing a shortcut, adding quotes etc
FullPath = Chr(34) & NewProgPath & Chr(34) & Chr(32) & Chr(34) &
FilePath & Chr(34)

'open program/file
r = Shell(FullPath, vbNormalFocus)

End Sub
--------------------------
Const cMAX_PATH = 260
Const ERROR_NOASSOC = 31
Const ERROR_FILE_NOT_ FOUND = 2&
Const ERROR_PATH_NOT_ FOUND = 3&
Const ERROR_BAD_FORMA T = 11&
Const ERROR_OUT_OF_ME M = 0

Private Declare Function apiFindExecutab le Lib "shell32.dl l" _
Alias "FindExecutable A" _
(ByVal lpFile As String, _
ByVal lpDirectory As String, _
ByVal lpResult As String) _
As Long

Function fFindEXE(stFile As String, _
stDir As String) _
As String
'Usage Example:
' ?fFindEXE("test .xls","c:\temp" )
'
On Error GoTo fFindEXE_err

Dim lpResult As String
Dim lngRet As Long

lpResult = Space(cMAX_PATH )
lngRet = apiFindExecutab le(stFile, stDir, lpResult)

If lngRet > 32 Then
fFindEXE = lpResult
Else
Select Case lngRet:
Case ERROR_NOASSOC: fFindEXE = "Error: No Association"
Case ERROR_FILE_NOT_ FOUND: fFindEXE = "Error: File Not
Found"
Case ERROR_PATH_NOT_ FOUND: fFindEXE = "Error: Path Not
Found"
Case ERROR_BAD_FORMA T: fFindEXE = "Error: Bad File Format"
Case ERROR_OUT_OF_ME M: fFindEXE = "Error: Out of Memory"
End Select
End If

fFindEXE_exit:
Exit Function
fFindEXE_err:
MsgBox Err.Description , 48, "Error in fFindEXE()"
Resume fFindEXE_exit
End Function
--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response