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.
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;
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.
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;
