How to query number of attachments from Attachment

Question

One of my users has a Microsoft Access database and in the table he has an attachment field. In one of his queries, he wants to return the number of attachments that the field contains. I have tried to get this to work to no avail. I've tried creating a VBA module and passing into it the field but it errors on me. I've tried creating the parameter as a DAO.Recordset, DAO.Field, Attachment, etc.

I've also tried querying the field like this [MyField].AttachmentCount.

Answer 1

I don't have 2007 with me currently to test this, but this article explains how to access the attachments with LoadFromFile and SaveToFile.

See if you can access the count like so (with DAO)... obviously use your table names.

 '  Instantiate the parent recordset. 
   Set rsEmployees = db.OpenRecordset("YourTableName")

  ''' Code would go here to move to the desired record

   ' Activate edit mode.
   rsEmployees.Edit

   ' Instantiate the child recordset.
   Set rsPictures = rsEmployees.Fields("Pictures").Value 

   Debug.Print rsPictures.RecordCount'' <- SEE IF THIS GIVES YOU THE COUNT

EDIT: Sorry for the delay on this; I haven't had a chance to look at it.

I think this should be a solution for you. I tested it in Access 2010 and it works.

PART 1 - Create a generic function to get the attachment count for any field in any table. Place this code inside a module.

Function AttachmentCount(TableName As String, Field As String, WhereClause As String)
    Dim rsRecords As DAO.Recordset, rsAttach As DAO.Recordset

    AttachmentCount = 0

    Set rsRecords = CurrentDb.OpenRecordset("SELECT * FROM [" & TableName & "] WHERE " & WhereClause, dbOpenDynaset)
    If rsRecords.EOF Then Exit Function

    Set rsAttach = rsRecords.Fields(Field).Value
    If rsAttach.EOF Then Exit Function

    rsAttach.MoveLast
    rsAttach.MoveFirst

    AttachmentCount = rsAttach.RecordCount
End Function

PART 2 - Use the custom function in your Access query.

SELECT Table1.ID, AttachmentCount("Table1","MyAttach","[ID]=" & [ID]) AS [Num Attach]
FROM Table1;

Parameter 1 is the table where your attachments are, parameter 2 is the field in your table where the attachments are, and the last parameter is a WHERE clause for your table to select the right record.

Hope this helps!

UPDATE

This SQL query also worked for me:

SELECT t.ID, Count(t.MyAttach.FileName) AS [Num Attachments]
FROM Table1 AS t
GROUP BY t.ID;

Answer 2

adds all attachments from selected field into a dataset which allows you to then count it

    OleDbConnection connect = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='db/Adb.accdb'"); //set up connection
    //CL_ID is a fk so attachments can be linked to users
    OleDbCommand sql = new OleDbCommand("SELECT at_ID, [at_Name].[FileData], [at_Name].[FileName], [at_Name].[FileType] FROM Attachments WHERE at_ID =1;", connect);
    //adding sql to addapter to be ran

    OleDbDataAdapter OleDA = new OleDbDataAdapter(sql);
    //attempting to open connection
    try { connect.Open(); }
    catch (Exception err) { System.Console.WriteLine(err); }

    DataSet Set1 = new DataSet();
    OleDA.Fill(Set1); //create and fill dataset
    connect.Close();

    Set1.Tables[0].Rows.Count;

Answer 3

I found the AttachmentCount function above failed when the attachment field contained no objects/attachments. To actually get a return value of 0 out of the function in this case I added three lines of code to give the below:

Function AttachmentCount(TableName As String, Field As String, WhereClause As String)
    Dim rsRecords As DAO.Recordset, rsAttach As DAO.Recordset

    On Error GoTo Handler

    AttachmentCount = 0

    Set rsRecords = CurrentDb.OpenRecordset("SELECT * FROM [" & TableName & "] WHERE " & WhereClause, dbOpenDynaset)
    If rsRecords.EOF Then Exit Function

    Set rsAttach = rsRecords.Fields(Field).Value
    rsAttach.MoveLast
    rsAttach.MoveFirst

    If rsAttach.EOF Then Exit Function

    AttachmentCount = rsAttach.RecordCount

Handler:
    Exit Function

End Function

Thank you so much for the original function! If it hadn't been for you I'd still be scratching my head as to how to obtain the attachment count.