fnAnySQL() parameter query

[arnelgp]

i am now shelving my FixSql() function, for the time being to give way to a new function someone shared on this forum (you know who you are).

I modified the function so it can be generic and be easily ported, at least, to any SQL statement.

Option Compare Database
Option Explicit

Public Function fnAnySQL(ByVal strSQL As String, ParamArray p() As Variant)
'
' strSQL must be an Any SQL statement (Select, Insert, Update, Delete)
' parameters should be in format @1, @2, ... to be
' consistent
'
' DO NOT use parameter name which has same name as your fieldname.
' using this will take the fieldname instead of the parameter.
'
' Example:
'
' SELECT Statement:
'   Dim rs As DAO.Recordset
'   set rs = fnAnySQL("SELECT field1 FROM table1 WHERE Names = @1;", "arnelgp")
'
'
' UPDATE Statement:
'   Call fnAnySQL("Update table1 Set field1 = @1;", "arnelgp")
'
'
' DELETE Statement:
'   Call fnAnySQL("Delete table1.* From table1 Where field1 = @1;", "arnelgp")
'
' INSERT Statement:
'   Call fnAnySQL("Insert Into table1 (field1, field2) SELECT @1, @2;", "arnel", "gp")
'
'
    Dim param As Parameter
    Dim i As Integer
    With CurrentDb.CreateQueryDef("", strSQL)
        For i = 0 To .Parameters.Count - 1
            .Parameters(i) = p(i)
        Next
        If InStr(strSQL, "SELECT") = 1 And InStr(strSQL, "INTO") = 0 Then
            ' Simple select query and not Create table query
            Set fnAnySQL = .OpenRecordset(dbOpenDynaset)
        Else
            ' Action queries
            .Execute (dbFailOnError)
        End If
    End With
            
End Function