使用VBA在Access 2010中创建一个动态表 [英] Using VBA to create a dynamic table in Access 2010

问题描述

我有一个Access 2010数据库与VBA模块，不会对数据的一些统计分析。统计分析的结果不能由SQL生成，但它们可以psented表格格式$ P $。现在，我可以运行在立即窗口中VBA函数，它会遍历所有的结果，并使用Debug.Print（）写入到终端。

I have an Access 2010 database with a VBA module that does some statistical analysis on the data. The results of the statistical analysis cannot be generated by SQL, but they can be presented in tabular format. Right now, I can run the VBA function in the Immediate window and it will loop over the results and write them to the terminal using Debug.Print().

我想有这个功能提供给访问的其余部分的结果，这样我可以从结果表中创建查询和报告。因此，我正在寻找的是如何把我的函数变成动态表 - 这实际上并不存储数据的表，但存储运行，并填写表中的数据动态每当表使用VBA函数

I'd like to have the results of this function available to the rest of Access so that I can create queries and reports from the table of results. So what I'm looking for is how to turn my function into a "dynamic table" -- a table that doesn't actually store data, but stores the VBA function that runs and fills in the table data dynamically whenever that table is used.

我已经花了相当多的时间看，通过生成表查询动态创建表或在VBA中使用的DDL，但所有这些例子中使用SQL从现有的记录创建新表。我不能使用SQL生成的结果，所以我真的不知道怎么的结果强制成一个对象，Access将识别。问题的部分原因是，我只是不与Access VBA术语非常熟悉，知道我应该寻找。

I've spent quite a bit of time looking at creating tables dynamically via MAKE TABLE queries or using DDL in VBA, but all of these examples use SQL to create the new table from existing records. I can't use SQL to generate the results, so I'm not really sure how to coerce the results into an object that Access will recognize. Part of the problem is that I'm just not familiar enough with Access VBA terminology to know what I should be looking for.

感谢您的帮助。

推荐答案

我用下面的，如果我不希望使用DDL和SQL查询code ...

I use following code if I don't want to use DDL and SQL Query...

Set dbs = CurrentDb        
Set tbl = dbs.CreateTableDef("tbl_Name")
Set fld = tbl.CreateField("Filed1", dbText, 255)
tbl.Fields.Append fld
Set fld = tbl.CreateField("Field2", dbText, 255)
tbl.Fields.Append fld
Set fld = tbl.CreateField("Field3", dbInteger)
tbl.Fields.Append fld
Set fld = tbl.CreateField("Field4", dbCurrency)
tbl.Fields.Append fld
dbs.TableDefs.Append tbl
dbs.TableDefs.Refresh

如果你想添加一条记录，你可以做

and if you want to add a record you could do

Dim dbs As DAO.Database
Dim rs As DAO.Recordset

Set dbs = CurrentDb
Set rstVideos = dbs.OpenRecordset("tbl_name")

rs.AddNew    
rs("field1").Value = "TEST "   
rs("field2").Value = "TEXT"   
rs("field3").Value = 1991
rs("field4").Value = 19.99

rstVideos.Update

这篇关于使用VBA在Access 2010中创建一个动态表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！