arnelgp
..forever waiting... waiting for jellybean!
- Local time
- Today, 22:41
- Joined
- May 7, 2009
- Messages
- 19,175
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.
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