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 data analysis. Statistical analysis results cannot be obtained 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 cycle through the results and write them to the terminal using Debug.Print ().

I would like the results of this feature to be available to the rest of Access so that I can create queries and reports from the results table. So I'm looking for how to turn my function into a “dynamic table” - a table that does not actually store data, but stores a VBA function that runs, and dynamically populates the table data whenever this table is used.

I spent a lot of time dynamically creating tables using MAKE TABLE queries or using DDL in VBA, but all these examples use SQL to create a new table from existing records. I cannot use SQL to generate the results, so I'm not sure how to force the results to an object that Access recognizes. Part of the problem is that I do not understand enough Access VBA terminology to know what I need to look for.

My expression is simply "Public Function GenerateSchedule". It has three blocks of code: the first extracts the data I need from the database using a query and processes the RecordSet into an array. The second block performs statistical analysis of the array, and the third prints the analysis results on the terminal. I would like to replace the third block with a block that provides the results in a table that can be used by the rest of Access.

Answer 1

I use the following code if I do not 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 that 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 

Answer 2

I'm not sure why you need to put the extracted data into an array. It seems an extra step. If you can generate statistics from an array, the same thing should be possible in the request. create another query using the query of results as one record source and make your calculations accordingly for the fields that you want to create. If we saw what you are trying to do, I think this can be done easier.

Answer 3

It sounds like a disabled record set, or maybe a “synthetic record set” that ADO can do. I do not use ADO, so I can’t provide you with instructions, but perhaps this will provide you with what you need.

Alternatively, depending on how you want to display it to users, you may be able to make it native in Access. For example, if you present it in a form or report in a list, you can write a custom callback function and associate it with the list.