Results 1 to 8 of 8
  1. #1
    KillGorack is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2022
    Posts
    13

    Question Prepared Select Statement

    I have some experience with prepared statements on the web, and I can get a prepared statement to execute, but the syntax for using it in a normal select eludes me.



    this simple example: I'm just using for figuring out the syntax.

    I'm just guessing at the "Set rst" line.

    Code:
        Dim db As dao.Database
        Dim qdf As dao.QueryDef
        Dim strSql As String
        Set db = CurrentDb
        
        Dim settingParameter As String
        settingParameter = "Upload Path"
        
        strSql = "SELECT settings.str_parameter FROM settings WHERE settings.stg_name = [settingName];"
    
    
        Set qdf = db.CreateQueryDef(vbNullString, strSql)
    
    
        qdf.Parameters("settingName").Value = settingParameter
        Set rst = qdf.OpenRecordset(strSql)
    
    
        dbs.Close
        qdf.Close

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Just use a DlookUp()
    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?sear...bug+access+vba

  3. #3
    KillGorack is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2022
    Posts
    13
    Quote Originally Posted by Welshgasman View Post
    Just use a DlookUp()
    Agreed on this specific example, it's probably a bad example.

    Just looking for the syntax of a prepared query to get a record set back.

  4. #4
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Well I would have thought you would use the recordset on the new query you had just created?, not the sql that it was created with?
    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?sear...bug+access+vba

  5. #5
    KillGorack is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2022
    Posts
    13
    Quote Originally Posted by Welshgasman View Post
    Well I would have thought you would use the recordset on the new query you had just created?, not the sql that it was created with?
    Perfect, always the dumb obvious stuff..

    Code:
        Dim db As dao.Database
        Dim qdf As dao.QueryDef
        Dim strSql As String
        Set dbs = CurrentDb
        
        Dim settingParameter As String
        settingParameter = "Upload Path"
        
        strSql = "SELECT settings.str_parameter FROM settings WHERE settings.stg_name = [settingName];"
        Set qdf = dbs.CreateQueryDef(vbNullString, strSql)
        qdf.Parameters("settingName").Value = settingParameter
        Set rst = qdf.OpenRecordset
    
        Debug.Print rst![str_parameter]
    
        dbs.Close
        qdf.Close

    Thank you!

  6. #6
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    When you open a recordset, if there are any records, then you will always be on the first record?
    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?sear...bug+access+vba

  7. #7
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Just looking for the syntax of a prepared query to get a record set back.
    better to be specific about your object types (rst is not dimmed) and you don't need the querydef unless you want to go that route (normally the value would be picked up from a form control - combo, listbox or textbox)

    Code:
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim settingParameter As String
    Dim strSql As String
    
    
    
    
        settingParameter = "Upload Path"
        
        Set db = CurrentDb
        strSql = "SELECT settings.str_parameter FROM settings WHERE settings.stg_name = '" & settingParameter & "'"
        Set rst = db.OpenRecordset(strSql, dbFailOnError)

  8. #8
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    What's a "prepared" query? If I'm not mistaken, you're creating a temporary query def so it only exists in memory (because of vbnullstring). I've only done this a few times but used "" as the name property. I don't see the point in creating a recordset from such a query when you can just open a recordset based on the sql string you have.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

Similar Threads

  1. Select statement not in same or another tab
    By udigold1 in forum Queries
    Replies: 2
    Last Post: 04-06-2021, 02:22 AM
  2. Replies: 6
    Last Post: 03-30-2018, 10:07 AM
  3. Prepared SQLQuery on my DB java
    By ReignMan in forum Access
    Replies: 1
    Last Post: 01-16-2015, 09:33 AM
  4. Need a select statement
    By gahawy in forum Queries
    Replies: 5
    Last Post: 01-15-2011, 04:02 PM
  5. select statement
    By jellybeannn in forum Access
    Replies: 5
    Last Post: 08-13-2010, 05:21 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