'Copied from a normal module
'We set a reference to the CurrentDb object once so that we can pass this to the ExecuteSQL function.
'Then the ExecuteSQL does an arDB.Execute sSQL at some point and then uses
'arDB.RecordsAffected to get that number of records.

Public Function ExecuteSQL_GetRecordsAffected(sSQL As String) As Long
    Dim arDB As DAO.Database
    Set arDB = CurrentDb
    ExecuteSQL sSQL, arDB
    ExecuteSQL_GetRecordsAffected = arDB.RecordsAffected
    Set arDB = Nothing
End Function

Public Sub ExecuteSQL(SQL As String, Optional ByRef dbCurrent As DAO.Database)
   Dim SetDbToNothing As Boolean
   If dbCurrent Is Nothing Then
      Set dbCurrent = CurrentDb
      SetDbToNothing = True
   End If
   Dim MultipleStatements As Variant
   Dim SQLele As Variant
   If InStr(SQL, ";") <> 0 Then MultipleStatements = SplitSQLStatements(SQL) Else MultipleStatements = Array(SQL)
   For Each SQLele In MultipleStatements
      If SQLele <> "" Then dbCurrent.Execute SQLele, dbSeeChanges
   Next SQLele
   If SetDbToNothing Then Set dbCurrent = Nothing
End Sub

Public Function SplitSQLStatements(sqlString As String) As Variant
    Dim sqlArray() As String
    Dim currentStatement As String
    Dim inString As Boolean
    Dim char As String
    Dim i As Integer
    Dim statements As Collection
    
    Set statements = New Collection
    currentStatement = ""
    inString = False
    
    For i = 1 To Len(sqlString)
        char = Mid(sqlString, i, 1)
        
        ' Check if we are entering or exiting a string
        If char = "'" Then
            inString = Not inString
        End If
        
        ' If we encounter a semicolon and we are not inside a string, split here
        If char = ";" And Not inString Then
            statements.Add Trim(currentStatement)
            currentStatement = ""
        Else
            currentStatement = currentStatement & char
        End If
    Next i
    
    ' Add the last statement if there is any
    If Trim(currentStatement) <> "" Then
        statements.Add Trim(currentStatement)
    End If
    
    ' Convert the collection to an array
    ReDim sqlArray(1 To statements.Count)
    For i = 1 To statements.Count
        sqlArray(i) = statements(i)
    Next i
    
    SplitSQLStatements = sqlArray
End Function