Transform and Pivot in Access 2016

First I tried this.

SELECT StudentID, [Questionnaire #] as QuestionnaireID, 'Q01' as Question, [Q01] as Response FROM Random_data_generator UNION ALL
SELECT StudentID, [Questionnaire #] as QuestionnaireID, 'Q02' as Question, [Q02] as Response FROM Random_data_generator UNION ALL
SELECT StudentID, [Questionnaire #] as QuestionnaireID, 'Q03' as Question, [Q03] as Response FROM Random_data_generator UNION ALL
SELECT StudentID, [Questionnaire #] as QuestionnaireID, 'Q04' as Question, [Q04] as Response FROM Random_data_generator UNION ALL
SELECT StudentID, [Questionnaire #] as QuestionnaireID, 'Q05' as Question, [Q05] as Response FROM Random_data_generator UNION ALL
SELECT StudentID, [Questionnaire #] as QuestionnaireID, 'Q06' as Question, [Q06] as Response FROM Random_data_generator UNION ALL
SELECT StudentID, [Questionnaire #] as QuestionnaireID, 'Q07' as Question, [Q07] as Response FROM Random_data_generator
ORDER BY StudentID, Question;

But I couldn't figure out how to do it dynamically for a random number of answers. So I blew the dust off my VB books and ended up with this which I'm sure is poorly coded and will make folks wince. But does work.

Dim db As DAO.Database
Set db = CurrentDb
Dim qdf As DAO.QueryDef

ColumnCount = CurrentDb.TableDefs("Random_data_generator").Fields.Count
ColumnCount2 = ColumnCount - 2
Dim QueryString As String
Dim QueryEntry As String
Dim counter As Integer
Dim counterEntry As String
Dim QueryTest As Recordset
Dim QuestionnaireNum As String
counter = 1


Do While counter <= ColumnCount2

 counterEntry = Format(counter, "00")

    QueryString = "SELECT StudentID, [QuestionnaireNum] as QuestionnaireID, 'Q" & counterEntry & "' as Question, [Q" & counterEntry & "] as Response FROM Random_data_generator UNION ALL " & vbCrLf
    counter = counter + 1
    QueryEntry = QueryEntry + QueryString

 Loop

 counterEntry = Format(counter, "00")

 QueryString = "SELECT StudentID, [QuestionnaireNum] as QuestionnaireID, 'Q" & counterEntry & "' as Question, [Q" & counterEntry & "] as Response FROM Random_data_generator ORDER BY StudentID, Question;"
 QueryEntry = QueryEntry + QueryString

 On Error Resume Next
 DoCmd.DeleteObject acQuery, "tempQry"
 On Error GoTo 0

 Set qdf = db.CreateQueryDef("tempQry", QueryEntry)

 DoCmd.OpenQuery ("tempQry")