CODEWITHSUNDEEP
ms-accessvba
DVCode
DVCode

Reputation: 171

How to open Attachment from Access Table

I am building a database to house word document and data. In this table is attached a copy of the word document.

How do I use VBA to reference and open a copy of the attached file (word document) from Field1 (default name)?

I've tried to use DAO Recordset, but keep running into bugs. Object references documents on Attachment was of little to no help.

Please let me know if additional information is required. Thank you!

Private Sub Field1_AfterUpdate()
'Outline:
'Macro is on Form_Entry.  After user uploads an attachment into the DB via form,
'I would like to open a copy of the attachment 
'and perform additional actions on the document

'Declarations
    Dim Active_DB As DAO.Database
    Dim Active_Q As DAO.QueryDef
    Dim Active_RS, iRS As DAO.Recordset
    Dim Active_Field As DAO.Field

'Refresh form such new entry is created
    Forms!Entry.Refresh

'Connect DB
    Set Active_DB = CurrentDb
    Set Active_RS = Forms!Entry.Recordset

'Retrieve recordset of the current record entry.
    Active_RS.FindFirst "ID =" & Forms!Entry.ID.Value

'This is where I run into problems.  I am not sure what the command is to open a document.
    Set Active_Field = Active_RS!Field1.Open

    Debug.Print Active_Field.FileName

End Sub

Upvotes: 1

Views: 5408

Answers (1)

DVCode
DVCode

Reputation: 171

Thank you for everyone's help.

Based on the comments, I was able to circumvent the problem by saving and opening a temporary file for processing.

Private Sub Field1_AfterUpdate()
'Declarations
    'Access-Related
    Dim Active_DB As DAO.Database
    Dim Active_Q As DAO.QueryDef
    Dim Active_RST, parent_RST, child_RST As DAO.Recordset
    Dim Active_Field As DAO.Field

    'Word-Related
    Dim app_Word As Word.Application
    Dim Active_DOC As Word.Document

    'Varible Declaration
    Dim str_Dir, str_FName As String


    'Refresh to create record
    Forms!entry.Refresh


    'Initial Assignments
    str_Dir = Environ("Temp") & "\"

    Set Active_DB = CurrentDb
    Set Active_RST = Forms!entry.Recordset


    'Assign Record Set to Current Record
    Active_RST.FindFirst "ID =" & Forms!entry.ID.Value

    'Assign Field value
    Set parent_RST = Active_RST.Fields("Field1").Value
    Set Active_Field = parent_RST.FileData

    'Create Word Application & document Objects
    Set app_Word = CreateObject("Word.application")

    If Dir(str_Dir & parent_RST.FileName) = "" Then
        'If directory does not exist, create SaveToFiles
        'Else open Document
        Active_Field.SaveToFile str_Dir & parent_RST.FileName
    End If
    Set Active_DOC = Documents.Open(str_Dir & parent_RST.FileName)


    Contract_Text.Value = str_Dir & parent_RST.FileName

End Sub

Upvotes: 1

Related Questions