[SQL] INSERT Function acts like it runs in Access VBA code but the table is blank

I built to function to populate a temp table with data. When the Form_Load event triggers for the form, it calls the function and should populate the table. The data set is small, only 23 records.

Public Function fnBuildScoreData()
Dim strSQL As String
strSQL = "INSERT INTO tmpQuestion ( QuestionID, CategoryID, PossibleScore, Question, ShortDesc, QuestionOrder) SELECT tblQuestions.QuestionID, " & _
"tblQuestions.CategoryID, tblQuestions.PossibleScore, tblQuestions.Question, tblQuestions.ShortDesc, tblQuestions.QuestionOrder " & _
"FROM (tblQuestions LEFT JOIN tblCategory ON tblQuestions.CategoryID = tblCategory.CategoryID) LEFT JOIN tblScores ON " & _
"tblQuestions.QuestionID = tblScores.QuestionID WHERE (((tblQuestions.EndDate) Is Null)) ORDER BY tblQuestions.QuestionOrder"

If RunADOSQLAction(strSQL) = False Then
fnBuildScoreData = ACTION_FAILED
GoTo ExitFunction
End If

End Function


I run the INSERT statement through a function that essentially runs the SQL code and returns whether it successfully excuted the code. Each time it returns a SUCCESS. I also tried DoCmd.RunSQL (strSQL) with my SQL string and still the table is empty.

Here is the other code:

Public Function RunADOSQLAction(strSQLIn As String) As Boolean
Dim cnnTemp As ADODB.Connection


If Left(strSQLIn, 6) = "SELECT" Then
MsgBox "You cannot use this function to process a SELECT statement.", vbOKOnly, "RunADOSQLAction"
RunADOSQLAction = False
Else
Set cnnTemp = New ADODB.Connection
Set cnnTemp = CurrentProject.Connection
cnnTemp.Execute (strSQLIn)
RunADOSQLAction = True
End If
End Function


I'm stumped on this one. Not sure why it's not running when executing the VBA code call?

Continue reading...