Go Back   Access World Forums > Microsoft Access Reference > Sample Databases

Thread Tools Rate Thread Display Modes
Old 11-27-2017, 10:51 AM   #1
Registered User
arnelgp's Avatar
Join Date: May 2009
Location: somewhere out there
Posts: 6,994
Thanks: 56
Thanked 2,247 Times in 2,156 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
fnAnySQL() parameter query

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)
        If InStr(strSQL, "SELECT") = 1 And InStr(strSQL, "INTO") = 0 Then
            ' Simple select query and not Create table query
            Set fnAnySQL = .OpenRecordset(dbOpenDynaset)
            ' Action queries
            .Execute (dbFailOnError)
        End If
    End With
End Function

"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
The Following 2 Users Say Thank You to arnelgp For This Useful Post:
gleesonc (03-13-2019), Uncle Gizmo (03-13-2019)

delete , parameter , select , sql , update

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Multiple parameter query and empty parameter thewrightdesign Queries 90 11-20-2011 03:34 AM
Enter Parameter Value despite no Parameter in query reklaw Queries 5 09-15-2011 10:34 AM
Parameter query asking for parameter more than once! Trick G Queries 2 11-13-2006 02:35 PM
Capturing Parameter from Parameter Query MTrem Queries 2 07-13-2005 04:47 AM
Programatic parameter query based on parameter queries Cynoclast Modules & VBA 5 11-18-2004 01:24 PM

All times are GMT -8. The time now is 10:37 PM.

Microsoft Access Help
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post

Sponsored Links

Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World