使用 VBA 代码绑定或操作 Access 数据表 [英] Data bind or Manipulate an Access Datasheet Using VBA Code

问题描述

我将 Access 2007 用于原型应用程序.我有两个数据库 testUi.accdb(其中

I am using Access 2007 for a prototype application.I have a two database testUi.accdb (which

包含我的所有表单)和 testDb.mdb(包含我的数据库).现在我想在我的一个子表单中使用数据表.有人可以帮我解答这些问题吗?

contain all of my forms) and testDb.mdb (which contain my database). Now I want to use a datasheet in one of my subform. Can anybody help me with these questions?

1. 我可以在没有表格的情况下手动构建数据表吗?
2. 如何通过 VBA 代码使用 DAO 或 ADO 将数据与此数据表绑定
3. 是否可以使用 VBA 代码循环数据表?获取或设置每行的值?
4. 如果以上都不可能，那么替代方法是什么在 Access 中使用像网格这样的数据表?

推荐答案

1) 数据表表单只能绑定到 DAO 记录集对象或 ADO 记录集对象.您可以通过设置 RecordSource 将它们隐式绑定到 DAO 记录集对象，或者您可以在代码中手动完成，如#2 所示.您只能使用代码绑定到 ADO 记录集，然后数据表的某些默认选项(如筛选和排序)将不起作用.

1) Datasheet forms can only be bound to DAO recordset objects or ADO Recordset objects. You can bind them to DAO recordset objects implicitly by setting the RecordSource or you can do it manually in code as shown in #2. You can only bind to an ADO recordset using code and then some of the Datasheet's default options like filtering and sorting will not work.

2) 我知道这过于简化了，但是...在表单的加载事件中:Set Me.Recordset = rs(rs 是您的记录集对象)

2) I know this is overly simplified but... On the form's Load Event: Set Me.Recordset = rs (rs is your recordset object)

3) 您不能像在许多网格控件上那样循环遍历数据表插入行对象和数据.

3) You cannot loop through the datasheet inserting row objects and data like you can on many grid controls.

4) 您可以使用 Microsoft 的 ActiveX 控件，例如 Grid Control 或 ListView Control.

4) You could use Microsoft's ActiveX controls such as the Grid Control or ListView Control.

实现此目的的一种方法是使用制造的 ADO 记录集"，然后将表单绑定到该记录集.这将使用我在 #1 和 #2 中列出的选项.

One way you could possibly accomplish this is to use a "fabricated ADO recordset" and then bind the form to that recordset. This would be using the options I laid out in #1 and #2.

在任何情况下，Access 数据表表单都不会像许多网格控件那样动态添加和配置足够的单元格/列来显示数据.这意味着您需要事先在表单上有足够的文本框，然后使用代码将它们绑定到您的 DAO 或 ADO 记录集中的字段.您不需要的任何列都必须使用代码隐藏.标签标题必须使用代码填写，以便它们制作正确的列标题.简而言之，虽然数据表视图实际上非常强大，但它根本不是网格控件.

In any case, an Access Datasheet form does not dynamically add and configure enough cells/columns to display data like many grid controls do. This means you'll need to have enough textboxes on the form before hand, and then use code to bind them to the fields in your DAO or ADO recordset. Any columns you don't need will have to be hidden using code. Label captions will have to be filled out using code so they make proper column headers. In short, while the datasheet view is actually very powerful, it simply isn't a Grid Control.

如果您曾经使用过 .NET，那么创建一个 AddIn 或 dll 可能会相当简单(虽然不是微不足道的)来为您提供所需的内容.唯一的问题是 .NET 的 DataGridView 不能绑定到 DAO 或 ADO 记录集，因此您必须编写代码来获取 DAO 或 ADO 记录集并将该记录集转换"为 ADO.NET DataTable.或者，您可以编写 is 以便您的 .NET 表单通过获取数据库和查询信息并使用它来检索所需数据来进行数据访问.

If you've ever used .NET it would probably be reasonably simple (although not trivial) to create an AddIn or a dll that would give you what you are looking for. The only problem is that .NET's DataGridView cannot be bound to a DAO or ADO recordset so you'll have to write code to take a DAO or ADO recordset and "translate" that recordset to an ADO.NET DataTable. Alternately you could write is so that your .NET form does data access by taking database and query information and using that to retrieve the desired data.

编辑 1
为了回应您在下面的评论，这里有一些示例代码.我认为您需要创建一个明确的记录集对象，而您在下面的评论中没有这样做.

Edit1
In response to your comments below, here's some example code. I think you need to create an explicit recordset object, which you're not doing in the comment below.

Option Compare Database
Option Explicit

Private Sub Form_Load()
    Dim db As DAO.Database
    Set db = OpenDatabase("E:\Access\Testdb.mdb")
    Dim rs As DAO.Recordset
    Set rs = db.OpenRecordset("Select * from tblMarket", dbOpenSnapshot)
    Set Me.Market1.Form.Recordset = rs
    Set rs = Nothing
End Sub

这篇关于使用 VBA 代码绑定或操作 Access 数据表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！