Unanswered: Creating new RS & Table from Existing query
I have a record set of data that I would like to process doing some string aggregation etc and would like to do this in VBA. I have a good idea of how to do this but am unsure of the actual process to run this in Access.
So what I currently have is a query (my query) that returns a set of records. What I want to do is load this in VBA, process it and show the results in another table. I do not need forms or buttons etc so how can I do that say running another query or whatever?
I suppose the steps I want to take are:
1) Load existing query data set into VBA (how do I lood in and store them?)
2) Process and do the concatenation (Do I process one at a time and put them in the new table or process all, hold them in new array and then populate table?)
3) Display table in Access display window (as if i had run a query by going to Datasheet view)
For task 1 I have:
Public Sub LoadRecords()
Dim RS As DAO.Recordset
Set RS = CurrentDb.OpenRecordset("MyQuery")
Depending on the nature of the processing you want to perform on the original data, it would probably be possible to perform the whole operation in one single SELECT... INTO query. What I don't understand is why you need to store the processed data.