docmd.RunSQL breaks from function

[MLogue9]

SOLVED - I was missing a quote and an error handler in parent function was hiding syntax error. I don't know why error escalated there, you see I commented the one out in this function.



I hate how Windows 8 takes a century to list and sort files. I figure I'd be better off listing files into an access table to help organize massive numbers of surveillance videos. Anyhow I got everything set up, but the thing simply stops and exits function at the docmd.runSQL line. I have breakpoints all around it and verified this is where it breaks out.

Here is example of one SQL command:
INSERT INTO tClips (directoryShort, directory, filename, fileSize, dateMod, dateDD, dateMM, dateYY, camera) VALUES ( "15", "c:\video_surv2\15", "150929-a.jpg", "223348", "2015-10-01 22:07:30", "1", "10", "2015", "150929-a);"

Here is the function in question:

Private Function FillDirToTable(colDirList As Collection _
    , ByVal strFolder As String _
    , strFileSpec As String _
    , bIncludeSubfolders As Boolean)
  Dim a
  Dim strCamera As String
   Dim dateMod As Date
   Dim strDirectoryShort As String
   Dim directory As String
   
   Dim dateDD, dateMM, dateYY As Integer
   
    'Build up a list of files, and then add add to this list, any additional folders
    'On Error GoTo Err_Handler
        Dim strErrMsg As String
    Dim strTemp As String
    Dim colFolders As New Collection
    Dim vFolderName As Variant
    Dim strSQL, strFilENP, strDateMod, strDateCreate, strSize As String
      Dim strMsg As String
    'Add the files to the folder.
    strFunction = "FileList/FDTT"
    strFolder = TrailingSlash(strFolder)
    strTemp = Dir(strFolder & strFileSpec)
    strDirectoryShort = GetRightFolder(strFolder)
    
    Do While strTemp <> vbNullString
         gCount = gCount + 1
         SysCmd acSysCmdSetStatus, gCount
       strFilENP = strFolder & strTemp
       
       dateMod = ShowFileInfo(strFilENP, "M") 'get file mod date
       strDateCreate = ShowFileInfo(strFilENP, "C") 'get file CREATE date
       strSize = ShowFileInfo(strFilENP, "S") 'get fiel size
       strTemp = Trim(strTemp)
       dateDD = Day(dateMod)
       dateMM = Month(dateMod)
       dateYY = year(dateMod)
       a = Split(strTemp, ".")
    strCamera = a(0)
    
       
         strSQL = "INSERT INTO tClips " _
          & " (directoryShort, directory, filename, fileSize, dateMod, dateDD, dateMM, dateYY, camera) " _
          & " VALUES ( """ & strDirectoryShort & """" _
          & ", """ & strFolder & """" _
          & ", """ & strTemp & """" _
          & ", """ & strSize & """" _
          & ", """ & dateMod & """" _
          & ", """ & dateDD & """" _
          & ", """ & dateMM & """" _
          & ", """ & dateYY & """" _
          & ", """ & strCamera & ");"
DoCmd.SetWarnings (warningson)
       DoCmd.RunSQL strSQL
        
        'colDirList.Add strFolder & strTemp
        strTemp = Dir
    Loop
    If bIncludeSubfolders Then
        'Build collection of additional subfolders.
        strTemp = Dir(strFolder, vbDirectory)
        Do While strTemp <> vbNullString
            If (strTemp <> ".") And (strTemp <> "..") Then
                If (GetAttr(strFolder & strTemp) And vbDirectory) <> 0& Then
                    colFolders.Add strTemp
                End If
            End If
            strTemp = Dir
        Loop
        'Call function recursively for each subfolder.
        For Each vFolderName In colFolders
            Call FillDirToTable(colDirList, strFolder & TrailingSlash(vFolderName), strFileSpec, True)
        Next vFolderName
    End If
Exit_Handler:
strMsg = strFunction & "Sucessful exit."

    Call CStatus(strMsg, 218)
    Exit Function
Err_Handler:
    
   'Error Handling
Exit Function
err_hand:
strErrMsg = strFunction & "> " & Err.Number & " " & Err.Source & " " & Err.description
Call CStatus(strErrMsg, 518, Err.Number, Err.description, Err.Source)
    
    strSQL = "INSERT INTO Files " _
    & " (FName, FPath) " _
    & " SELECT ""  ~~~ ERROR ~~~""" _
    & ", """ & strFolder & """;"
    CurrentDb.Execute strSQL
    
    Resume Exit_Handler
End Function

[June7]

I use a single apostrophe instead of doubled quote marks because I find it easier to read.

    strSQL = "INSERT INTO Files " _
    & " (FName, FPath) " _
    & " SELECT '~~~ ERROR ~~~'" _
    & ", '" & strFolder & "';"

[isladogs]

You are wrongly treating all fields as text

The dateMod field needs date delimiters.
Several fields including the other 3 dates are actually number fields and need to be handled as such.
The final value strCamera is missing an end quote

[ssanfu]

In VBA, you MUST explicitly declare variable types. For instance, in this line

Dim dateDD, dateMM, dateYY As Integer

only "dateYY" is declared as an integer and "dateDD" and "dateMM" are declared as Variants.

Same with this line

Dim strSQL, strFilENP, strDateMod, strDateCreate, strSize As String

only "strSize" is declared as a string and "strSQL", "strFilENP", "strDateMod" and "strDateCreate" are declared as Variants.

This line

DoCmd.SetWarnings (warningson)

does nothing. The correct syntax/usage is

DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

BTW, I NEVER use "DoCmd.RunSQL". I always use "CurrentDb.Execute sSQL, dbfailonerror".


You have 2 variables that are not declared: "strFunction" and "gCount"


You have

SysCmd acSysCmdSetStatus, gCount

but you do not clear the status bar when the function exits......



The top 2 lines of EVERY module should be

Option Compare Database '<<-- EVERY module should have these two lines
Option Explicit         '<<-- EVERY module should have these two lines

[Micron]

I don't know why error escalated there

In the absence of an error handler in the current procedure, Access goes "up the tree" of any procedures that are active (i.e. those that called subsequent procedures and have not yet terminated) and looks for an error handler. If it finds one, it will take instruction there, which may not be what you want. If it doesn't find one, the error is "fatal". Either way, the result will not usually end well, and in the very least, you could get a custom error message that has no bearing on the current problem. It is odd that you disabled error handling for the posted procedure.

I agree with the advice about warnings. I'd also suggest that procedures like this contain an error handler which in the very least, resumes to an exit point and that point contains all the cleanup stuff: closing recordsets, Set rs = Nothing (etc), and puts any modified db properties back to normal, and of course, an Exit statement to prevent the error handler from executing.