Create new table from dyname recordset

[JonathanB64]

I am using MS Access 2016 and need to create a dynamic form that displays data from my Sql Server data. My stored procedure is a dynamic pivot query so the columns will change. I am thinking of setting the form's recordset to a local MS Access table. The dynamic part is a form with two date fields. This is my code so far:
Sub DynamicAudits()
Dim qdf As DAO.QueryDef, rst As DAO.Recordset
Dim sqlAudit

Set qdf = CurrentDb.CreateQueryDef("")
qdf.Connect = "ODBC;Driver=SQL Server;Server=XXXX;database=CAQA;UID=XXXX;PWD=XXXX;"

' stored procedure with parameter
qdf.SQL = "exec [CIP].[usp_Dynamic_Pivot_Reviews_Param] '" & [Forms]![frm_Audit_Param]![sdate] & "','" & [Forms]![frm_Audit_Param]![edate] & "'"

qdf.ReturnsRecords = True
Set rst = qdf.OpenRecordset

Set Me.Recordset = rst

DoCmd.SetWarnings False
sqlAudit = "Select * Into Audit From " & rst
DoCmd.SetWarnings True

ShowCrossTab

End Sub
Obviously, the sqlAudit string does not work but that is the goal I am working towards.
The function "ShowCrossTab" is the function that will actually build the form based on the new recordset.
So how can I create this new local table from my SQL Server data?

 

[The_Doc_Man]

Obviously, the sqlAudit string does not work ...

...So how can I create this new local table from my SQL Server data?

The problems I see are

(1) Both rst and sqlAudit are defined locally and not passed to ShowCrossTab as any kind of obvious parameter. How will it know what to use for data?

(2) "SELECT * INTO table FROM {a recordset}" doesn't work because SELECT, being SQL, is bulk/wholesale oriented whereas opening a recordset in VBA is a piecemeal method. Either you need to write a loop stepping through the recordset one record at a time and storing the fields, perhaps with a second recordset to the table or perhaps by building a dynamic SQL "INSERT INTO" action query for one record at a time [INSERT INTO () VALUES () syntax]... OR if you could make an actual SELECT query out of the returned recordset, you could put its name in the definition of your sqlAudit string.

(3) Setting "Me.Recordset" to a new value probably doesn't work with a bound form, particularly if the form happens to be open at the time. Because in that case, so is the implied recordset, and open items can't be "diddled" that way. You would have to dynamically change the form's .Recordsource and then do a .Requery in order to have half a snowball's chance in Hell to see the implied records, and if it is a bound form, I'm not sure even that would work.

(4) The "SELECT ... INTO" syntax will work only as long as the implied recordset has well-behaved field names. I mention that only because using "*" syntax hides the names. If ANY PART of the implied SQL server query is an aggregate, it is possible (and we can't tell from here) that the names might be complex.