how to get attachment files using union query in ms access-CodePudding

I have two independent tables in ms access now i want to get all data from both tables and show as one table using union query. all is work but the problem is the attachment fields doesn't not loaded and its Empty.

SELECT * 
FROM  Table1
UNION ALL select * from Table2;

--This is my query which gets all records from both table but the attachment type field is empty

CodePudding user response：

This example may be usefull

Set rsChild = rsDocs.Fields("DocText").Value
rsChild.Fields("FileData").SaveToFile newFileName
rsChild.Close

where rsDocs - ADO (or DAO) recordset as query result
DocText - attachment field name
rsChild.Fields("FileData") - stream object
SaveToFile - method of attachment field

rsDocs.Fields("DocText").Value.Fields("FileData").SaveToFile

CodePudding user response：

The below example uses an attachment field named AttachmentField. You can pass the name of the file you're looking through the parameter.

If you run only the SQL, the parameter window will pop-up asking for the parameter value. To search, find the file and open it, you will need to use VBA.

See an example:

PARAMETERS [FileNameParam] Text (255);
SELECT T.ID, T.FileName
FROM (
    SELECT ID, AttachmentField.FileName AS FileName
    FROM Table1
    UNION ALL
    SELECT ID, AttachmentField.FileName AS FileName
    FROM Table2
) AS T
WHERE T.FileName Like '*'   [FileNameParam]   '*'

Dim q As DAO.QueryDef
Set q = CurrentDb().QueryDefs("Query1")
    q.Parameters("[FileNameParam]").Value = "Blank.pdf"
    
Dim r As DAO.Recordset
Set r = q.OpenRecordset(dbOpenSnapshot)

'nothing found
If r.EOF Then
    MsgBox "File not found."
    Exit Sub
End If

Dim filepath As String
    filepath = r![FileName]

'open
Application.FollowHyperlink filepath

'clean up
If Not r Is Nothing Then r.Close
If Not q Is Nothing Then q.Close