Results 1 to 5 of 5
  1. #1
    jhg6308 is offline Novice
    Windows 10 Office 365
    Join Date
    Jul 2021
    Posts
    16

    Parameter in values() clause of temporary QueryDef?


    I can create a query in the query designer with the following SQL:

    Code:
    INSERT INTO tmpUsersPendingRecalc values ([mbr]);
    and when I run it I get prompted for the value of [mbr] and the value is inserted as expected.

    Also, I can execute the query from VBA:

    Code:
        Dim qdfSetMemberId As QueryDef
        Set qdfSetMemberId = baseDb.QueryDefs!Query4
       
       ' Enumerate the parameters 
       Dim p As Parameter
       For Each p In qdfSetMemberId.Parameters
           Debug.Print p.Name & "=" & p.Value & "  " & p.Type
       Next
        
        qdfSetMemberId!mbr = memberId
        qdfSetMemberId.Execute
    The loop just enumerate the defined parameters, and confirms that there is one and its name is "mbr".

    However, when I try to create the exact same QueryDef dynamically in VBA as:

    Code:
        Dim qdfSetMemberId As QueryDef
        Set qdfSetMemberId = baseDb.CreateQueryDef(, "insert into tmpUsersPendingRecalc values([mbr]);")
        
        ' Enumerate the parameters 
        Dim p As Parameter
        For Each p In qdfSetMemberId.Parameters
            Debug.Print p.Name & "=" & p.Value & "  " & p.Type
        Next
        
    >>> qdfSetMemberId!mbr = memberId
        qdfSetMemberId.Execute
    the enumeration loop sees no parameters and the code fails at the indicated line with 3265 Item not found in this collection.

    The target table has only one column, and I get the same result if I explicitly give the column name.

    That tells me that when creating a QueryDef at runtime in VBA, it doesn't see that there is a parameter (or maybe the engine hasn't compiled the query yet).

    Is there a way to dynamically create a QueryDef with parameters?

    (Note, I don't generate the sql via string concatenation due to the risk of SQL injection)

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    9,713
    This is vaguely familiar. I'll suggest that your query def sql statement has to include the PARAMETERS predicate/declaration (not sure which to call it) within the sql statement itself.

    If you create a test query and right click on the query design background (the area where the tables are shown) you'll get what looks like the standard property sheet, except it is the property sheet for a query. There you will find the place to define the query parameters. If you define a parameter and then switch back to the sql view you can see how the parameters are defined in the sql. Alternatively you can research "ms access query parameters" to see the syntax. So to define a query def with parameters should be no more involved than properly constructing a sql statement with the Parameters declaration/definition.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    Iím telling everyone it's good to eat dried grapes. Itís all about raisin awareness.

  3. #3
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    1,337
    You refer to the parameter?
    Along the lines of
    Code:
    Dim strSQL As String
    strSQL = _
     "INSERT INTO Comments (ProjectID, Owner, Comment, Date) SELECT p0, p1, p2, p3"
    With CurrentDb.CreateQueryDef("", strSQL)
        .Parameters(0)=Me.ProjectID
        .Parameters(1)=Me.Newowner
        .Parameters(2)=Me.NewComments
        .Parameters(3)=Me.newdate
        .Execute dbFailOnError
    End With

    Another way is using Eval()

    Code:
    Private Sub Command82_Click()
        dim db as dao.databases
        dim p as parameter
        set db=currentdb
    
        
        with db
            with .querydefs("qryDeleteRecsFromTempImport")
                for each p in .parameters
                    .value = eval(.name)
                next
                .execute
            end with
            with .querydefs("qryImportClaimsFromCYBER")
                for each p in .parameters
                    .value = eval(.name)
                next
                .execute       
                if .RecordsAffected <> 0 Then
                    msgbox .RecordsAffected & " Claims successfully imported from CYBER."   
                else
                    mgbox "No Claims were imported from CYBER."
                end if
            end with
        end with
    end sub
    Not my code, but arnelgp's on another forum.

    HTH
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?search_query=debug+access+vba



  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    9,713
    The parameters clause I was thinking of

    Code:
    sql = "PARAMETERS [myField] Text; SELECT [myTable].*, 1 AS CountIt FROM myTable]"
    Set qdf = CurrentDb.CreateQueryDef("", sql)
    qdf.Parameters(0) = "3"
    Set rs = qdf.OpenRecordset
    That will evaluate the single parameter and produce a temporary querydef on which to base the recordset.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    Iím telling everyone it's good to eat dried grapes. Itís all about raisin awareness.

  5. #5
    jhg6308 is offline Novice
    Windows 10 Office 365
    Join Date
    Jul 2021
    Posts
    16
    Quote Originally Posted by Micron View Post
    The parameters clause I was thinking of

    Code:
    sql = "PARAMETERS [myField] Text; SELECT [myTable].*, 1 AS CountIt FROM myTable]"
    Set qdf = CurrentDb.CreateQueryDef("", sql)
    qdf.Parameters(0) = "3"
    Set rs = qdf.OpenRecordset
    That will evaluate the single parameter and produce a temporary querydef on which to base the recordset.
    That was the first thing I tried. No change.

    I have since moved on and used a table-type recordset and .addNew() instead.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. QueryDef
    By test1000 in forum Access
    Replies: 2
    Last Post: 06-29-2017, 12:46 PM
  2. Replies: 10
    Last Post: 11-25-2014, 06:35 PM
  3. QueryDef
    By crowegreg in forum Programming
    Replies: 2
    Last Post: 09-18-2012, 09:31 AM
  4. Replies: 3
    Last Post: 02-23-2012, 06:29 PM
  5. Parameter Values
    By Darkladymelz in forum Queries
    Replies: 12
    Last Post: 01-19-2012, 08:45 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums