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

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-27-2017, 10:51 AM   #1
arnelgp
Newly Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 5,681
Thanks: 52
Thanked 1,849 Times in 1,766 Posts
arnelgp is a jewel in the rough arnelgp is a jewel in the rough arnelgp is a jewel in the rough arnelgp is a jewel in the rough
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.

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

__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Reply

Tags
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 04:12 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


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