fnAnySQL() parameter query (1 Viewer)

Status
Not open for further replies.

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:39
Joined
May 7, 2009
Messages
19,169
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.

Code:
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
 
Status
Not open for further replies.

Users who are viewing this thread

Top Bottom