How To Run A Parameterized Insert Query From VBA

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.

 

Answer 1

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

Answer 2

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

 

 

Answer 3

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

Answer 4

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

Answer 5

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

Answer 6

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.