Using VBA to create a dynamic table in Access 2010

Question

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().

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.

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.

My declaration is just "Public Function GenerateSchedule" . It has three code blocks: the first pulls the data I need from the database using a query and processes the RecordSet into an array. The second block performs the statistical analysis on the array, and the third prints the results of the analysis to the terminal. I'd like to replace the third block with a block that provides the results as a table that is usable by the rest of Access.

Answer 1

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("Field1", 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