Geek Answers Handbook

How to request the number of attachments in the Attachment field in Microsoft Access?

One of my users has a Microsoft Access database, and in the table it has an attachment field. In one of his queries, he wants to return the number of attachments that the field contains. I tried to get this to work to no avail. I tried to create a VBA module and pass a field to it, but these are errors for me. I tried to create a parameter like DAO.Recordset, DAO.Field, Attachment, etc.

I also tried to request a field like this [MyField] .AttachmentCount.

+5
source share
3 answers

2007 , , , LoadFromFile SaveToFile.

, ( DAO)... , .

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

, . Access 2010, .

1. , . .

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

2. Access.

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

1 - , , 2 - , , - WHERE , .

, !

UPDATE

SQL- :

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

, AttachmentCount , /. 0 , , :

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

! , , .

+3

,

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

More articles:


All Articles