Using VBA to create a dynamic table in Access 2010

4
votes

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.

3
ms-accessvbams-access-2010
The declaraction 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 useable by the rest of Access. – Soren

3 Answers

8
votes

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
0
votes

I am not sure why you need to put the retrieved data into an array. It seems and extra step. If you can generate the statistics from the array, the same thing should be possible in a query. create another query, using the results query as one recordsource and make your calculations accordingly for the fields that you want created. If we saw what you were trying to do, I think it could be made more simple.

0
votes

This sounds like a disconnected recordset, or maybe "synthetic recordset," which is something ADO can do. I don't use ADO, so can't provide you with instruction, but maybe that will provide you with what you need.

Alternatively, depending on how you want to display it to the users, you might be able to do it native in Access. For instance, if presenting it on a form or report in a listbox is sufficient, then you could write a custom callback function and bind it to the listbox.