locked
How To Run A Parameterized Insert Query From VBA RRS feed

  • Question

  • Hi, in Access 2003 I have created an Insert Query (qryInsertTable1) - something like:

    insert into Table1 (Field1, Field2)
    select Field1, PARAMETER1
    from Table2 T2 inner join Table3 T3 on T2.Field3 = T3.Field3
    where T3.Field4 = PARAMETER2;

    The query works fine.  I now need to call it from VBA code, supplying values for the 2 parameters.  How do I do this?

    I have thought of embedding the SQL into the VBA code, but as I need to call it from multiple places, I think this approach is a better design.

    Thanks for your assistance.

     

    Wednesday, August 24, 2011 4:12 PM

Answers

  • Great Scott:

    Many of my queries are not insert queries but select queries.  Going along with the same idea that you illustrated, I modified the code to allow parameterized SELECT queries instead of insert queries.  Here's a sample of that approach:

    ' ***********************************************************************
    ' Test Select Query with Parameters
    ' ***********************************************************************
    Public Function TestSelectQuery()
    
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim qdf As DAO.QueryDef
    
    ' ***********************************************************************
    ' Set Up DB and Query Definitions
    ' ***********************************************************************
    Set db = CurrentDb()
    Set qdf = db.QueryDefs("qryTestSelectWithParams")
    qdf!Param1 = "A"
    
    ' ***********************************************************************
    'Now we'll convert the querydef to a recordset and run it
    ' ***********************************************************************
    Set rst = qdf.OpenRecordset
    
    If rst.EOF Then
      rst.Close
      Exit Function
    End If
    
    ' ***********************************************************************
    ' Loop Through All The Records Matching Param1
    ' ***********************************************************************
    Do
    MsgBox ("Value is " & rst!Name)
    rst.MoveNext
    Loop Until rst.EOF
    
    rst.Close
    
    Set rst = Nothing
    
    End Function
    
    



    Rich Locus Logicwurks, LLC www.logicwurks.com
    • Proposed as answer by Bruce Song Tuesday, August 30, 2011 7:19 AM
    • Marked as answer by Bruce Song Monday, September 5, 2011 11:37 AM
    Wednesday, August 24, 2011 6:44 PM
  • The sample code below shows how to create the SQL for Text or Integers, includes error handling, and gives an example of how to use the value returned by the Functions.

    'Usage: If ExecuteSQLTextParams(PARAMETER1, PARAMETER2) = False Then
    '     :     'Code to handle failure here
    '     : End If
    Function ExecuteSQLTextParams(PARAMETER1 As String, PARAMETER2 As String) As Boolean

    On Error GoTo ErrHandle

        Dim db As DAO.DATABASE
        Dim strSQL As String
       
        strSQL = "INSERT INTO Table1 (Field1, Field2)" _
            & " SELECT Field1,""" & PARAMETER1 & """" _
            & " FROM Table2 T2 Inner Join Table3 T3 on T2.Field3 = T3.Field3" _
            & " WHERE T3.Field4 = """ & PARAMETER2 & """;"
           
        Set db = CurrentDb
       
        db.Execute strSQL, dbFailOnError
     
        ExecuteSQLTextParams = True 'If we are here Success!

    ExitHere:
        On Error Resume Next
        Set db = Nothing
        Exit Function

    ErrHandle:
        MsgBox "Error " & Err.Number & vbCrLf & Err.Description _
        & vbCrLf & "In procedure ExecuteSQLTextParams"
        Resume ExitHere

    End Function

    'Usage: If ExecuteSQLLongParams(PARAMETER1, PARAMETER2) = False Then
    '     :     'Code to handle failure here
    '     : End If
    Function ExecuteSQLLongParams(PARAMETER1 As Long, PARAMETER2 As Long) As Boolean
       
        On Error GoTo ErrHandle
       
        Dim db As DAO.DATABASE
        Dim strSQL As String

        ExecuteSQLLongParams = False  'Default Value

        strSQL = "INSERT INTO Table1 (Field1, Field2)" _
            & " SELECT Field1," & PARAMETER1 _
            & " FROM Table2 T2 Inner Join Table3 T3 on T2.Field3 = T3.Field3" _
            & " WHERE T3.Field4 = " & PARAMETER2 & ";"

        Set db = CurrentDb
       
        db.Execute strSQL, dbFailOnError
     
        ExecuteSQLLongParams = True 'If we are here Success!

    ExitHere:
        On Error Resume Next
        Set db = Nothing
        Exit Function

    ErrHandle:
        MsgBox "Error " & Err.Number & vbCrLf & Err.Description _
        & vbCrLf & "In procedure ExecuteSQLLongParams"
        Resume ExitHere
    End Function


    Patrick Wood Gaining Access http://gainingaccess.net
    • Proposed as answer by Bruce Song Tuesday, August 30, 2011 7:19 AM
    • Marked as answer by Bruce Song Monday, September 5, 2011 11:37 AM
    Wednesday, August 24, 2011 5:27 PM

All replies

  • Hello Great Scott:

    I have encountered your situation many times.  Queries with parameters can be accomplished in two ways:

    (1) Use VBA to create the query
    (2) Create a query in query builder and in the criteria section, call a function or functions that return a value which is variable

    I like approach 2 in those cases where the query is called from many places.

    Here's how to do it:

    Create Parameterized Queries in QueryBuilder

    To call your conditional query from within VBA you would use code like this (assume your query is called qryInsertNewRecords)

    DoCmd.SetWarnings False
    DoCmd.OpenQuery "qryInsertNewRecords", acViewNormal, acEdit
    DoCmd.SetWarnings True
    
    

    Regards,


    Rich Locus Logicwurks, LLC www.logicwurks.com




    Wednesday, August 24, 2011 4:31 PM
  • Thanks for your help, Rich, looks a useful article.

    In the meantime I came accross a very helpful post from Tom van Stiphout (http://social.msdn.microsoft.com/Forums/eu/accessdev/thread/6e5133c7-ae18-43c1-bbe4-26892153bdb8).  I have solved my problem like this:

        Dim qd As dao.QueryDef
        Dim rs As dao.Recordset
       
        Set qd = CurrentDb.QueryDefs("qryInsertTable1")
       
        ' Set parameter values
        qd!Param1 = Me.Param1
        qd!Param2 = Me.Param2
       
        ' Execute query
        qd.Execute dbFailOnError

     

     

    Wednesday, August 24, 2011 5:01 PM
  • The sample code below shows how to create the SQL for Text or Integers, includes error handling, and gives an example of how to use the value returned by the Functions.

    'Usage: If ExecuteSQLTextParams(PARAMETER1, PARAMETER2) = False Then
    '     :     'Code to handle failure here
    '     : End If
    Function ExecuteSQLTextParams(PARAMETER1 As String, PARAMETER2 As String) As Boolean

    On Error GoTo ErrHandle

        Dim db As DAO.DATABASE
        Dim strSQL As String
       
        strSQL = "INSERT INTO Table1 (Field1, Field2)" _
            & " SELECT Field1,""" & PARAMETER1 & """" _
            & " FROM Table2 T2 Inner Join Table3 T3 on T2.Field3 = T3.Field3" _
            & " WHERE T3.Field4 = """ & PARAMETER2 & """;"
           
        Set db = CurrentDb
       
        db.Execute strSQL, dbFailOnError
     
        ExecuteSQLTextParams = True 'If we are here Success!

    ExitHere:
        On Error Resume Next
        Set db = Nothing
        Exit Function

    ErrHandle:
        MsgBox "Error " & Err.Number & vbCrLf & Err.Description _
        & vbCrLf & "In procedure ExecuteSQLTextParams"
        Resume ExitHere

    End Function

    'Usage: If ExecuteSQLLongParams(PARAMETER1, PARAMETER2) = False Then
    '     :     'Code to handle failure here
    '     : End If
    Function ExecuteSQLLongParams(PARAMETER1 As Long, PARAMETER2 As Long) As Boolean
       
        On Error GoTo ErrHandle
       
        Dim db As DAO.DATABASE
        Dim strSQL As String

        ExecuteSQLLongParams = False  'Default Value

        strSQL = "INSERT INTO Table1 (Field1, Field2)" _
            & " SELECT Field1," & PARAMETER1 _
            & " FROM Table2 T2 Inner Join Table3 T3 on T2.Field3 = T3.Field3" _
            & " WHERE T3.Field4 = " & PARAMETER2 & ";"

        Set db = CurrentDb
       
        db.Execute strSQL, dbFailOnError
     
        ExecuteSQLLongParams = True 'If we are here Success!

    ExitHere:
        On Error Resume Next
        Set db = Nothing
        Exit Function

    ErrHandle:
        MsgBox "Error " & Err.Number & vbCrLf & Err.Description _
        & vbCrLf & "In procedure ExecuteSQLLongParams"
        Resume ExitHere
    End Function


    Patrick Wood Gaining Access http://gainingaccess.net
    • Proposed as answer by Bruce Song Tuesday, August 30, 2011 7:19 AM
    • Marked as answer by Bruce Song Monday, September 5, 2011 11:37 AM
    Wednesday, August 24, 2011 5:27 PM
  • Great Scott:

    Yes... I tested the code provided by your note on code from Tom van Stiphout and it works well!!  In the criteria section of the query builder I included [Param1] and [Param2] underneath the fields I wanted to test.  Another great solution to add to my collection!!!

    Thanks,


    Rich Locus Logicwurks, LLC www.logicwurks.com
    Wednesday, August 24, 2011 6:03 PM
  • Great Scott:

    Many of my queries are not insert queries but select queries.  Going along with the same idea that you illustrated, I modified the code to allow parameterized SELECT queries instead of insert queries.  Here's a sample of that approach:

    ' ***********************************************************************
    ' Test Select Query with Parameters
    ' ***********************************************************************
    Public Function TestSelectQuery()
    
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim qdf As DAO.QueryDef
    
    ' ***********************************************************************
    ' Set Up DB and Query Definitions
    ' ***********************************************************************
    Set db = CurrentDb()
    Set qdf = db.QueryDefs("qryTestSelectWithParams")
    qdf!Param1 = "A"
    
    ' ***********************************************************************
    'Now we'll convert the querydef to a recordset and run it
    ' ***********************************************************************
    Set rst = qdf.OpenRecordset
    
    If rst.EOF Then
      rst.Close
      Exit Function
    End If
    
    ' ***********************************************************************
    ' Loop Through All The Records Matching Param1
    ' ***********************************************************************
    Do
    MsgBox ("Value is " & rst!Name)
    rst.MoveNext
    Loop Until rst.EOF
    
    rst.Close
    
    Set rst = Nothing
    
    End Function
    
    



    Rich Locus Logicwurks, LLC www.logicwurks.com
    • Proposed as answer by Bruce Song Tuesday, August 30, 2011 7:19 AM
    • Marked as answer by Bruce Song Monday, September 5, 2011 11:37 AM
    Wednesday, August 24, 2011 6:44 PM
  • Many thanks, Rich and Patrick.  There are several great solutions here and I for one now have a better understanding of how to address this issue.
    Wednesday, August 24, 2011 10:18 PM