如何从Microsoft Access的“附件"字段中查询附件的数量? [英] How to query number of attachments from Attachment field in Microsoft Access?

问题描述

我的一个用户有一个Microsoft Access数据库，并且在表中有一个附件字段.在他的一个查询中，他想返回该字段包含的附件​​数.我试图使它工作无济于事.我尝试创建一个VBA模块并将其传递给该字段，但是它对我来说是错误的.我尝试将参数创建为DAO.Recordset，DAO.Field，附件等.

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.

我也尝试过查询类似[MyField] .AttachmentCount的字段.

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

推荐答案

我目前没有与我一起测试2007，但是

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

看看您是否可以像这样访问计数(使用 DAO )...显然使用表名.

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.

我认为这应该是您的解决方案.我在Access 2010中对其进行了测试，并且可以正常工作.

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

PART 1 -创建通用函数以获取任何表中任何字段的附件计数.将此代码放在模块中.

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 -在您的Access查询中使用自定义功能.

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

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

参数1是附件所在的表，参数2是附件所在表的字段，最后一个参数是WHERE子句，供您的表选择正确的记录.

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.

希望这会有所帮助！

更新

此SQL查询也对我有用:

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;

这篇关于如何从Microsoft Access的“附件"字段中查询附件的数量?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！