Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Mar 2002
    Location
    Buenos Aires, Argentina
    Posts
    877
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Create a New Query from VBA (2003)

    I have a form with some controls that let me query tables pretty fast. One of those is a textbox that displays the SQL code of the query shown in a listbox, so I can copy the SQL code, manually create a new query, paste the code, and fine tune it manually. This is also useful for copying the output, which the listbox restricts.
    When a button is pressed, the controls feed the procedure, which forms a public strSQL string, which in turn is used to fill both the textbox (with the SQL code shown as text) and the listbox (via its rowsource property).

    I'd like to automate the last step. That is, I would like to click on a button and have Access create a new query for me (without actually saving it if possible), paste the strSQL string and run the query. Can this be done?

    DoCMD.Openquery seems to work on queries that are already created (and saved).

    Thank you in advance.
    <img src=/w3timages/blue3line.gif width=33% height=2>
    <img src=/S/flags/Argentina.gif border=0 alt=Argentina width=30 height=18> <big><font color=4682b4><font face="Comic Sans MS">Diegol</font face=comic></font color=4682b4> </big>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Create a New Query from VBA (2003)

    If it's the SQL for an action query such as an update query or delete query, you can run the SQL directly using DoCmd.RunSQL (this operates at the Access level) or CurrentDb.Execute (at the Jet Engine level).

    You can't display the records returned by the SQL for a select query directly - you have to create a query and set its SQL property, then open the query, or use the SQL as Record Source for a form or report, and open that form or report.

  3. #3
    5 Star Lounger
    Join Date
    Mar 2002
    Location
    Buenos Aires, Argentina
    Posts
    877
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create a New Query from VBA (2003)

    Thank you, Hans.

    I think I could finally help myself out with this.

    The preliminar code (which is working) is as follows:

    Private Sub cmdAbrirConsulta_Click()

    Dim qdfNew As DAO.QueryDef

    On Error Resume Next
    With CurrentDb
    .QueryDefs.Delete ("z_Temp")
    Set qdfNew = .CreateQueryDef("z_Temp", strSQL)
    .Close
    End With
    DoCmd.OpenQuery "z_Temp"

    End Sub

    Any further suggestions are welcome!
    <img src=/w3timages/blue3line.gif width=33% height=2>
    <img src=/S/flags/Argentina.gif border=0 alt=Argentina width=30 height=18> <big><font color=4682b4><font face="Comic Sans MS">Diegol</font face=comic></font color=4682b4> </big>

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Create a New Query from VBA (2003)

    That's the way to create a query on the fly, setting its SQL.

  5. #5
    5 Star Lounger
    Join Date
    Mar 2002
    Location
    Buenos Aires, Argentina
    Posts
    877
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create a New Query from VBA (2003)

    Many thanks, Hans. I really appreciate your valuable help.

    Have a nice week!
    <img src=/w3timages/blue3line.gif width=33% height=2>
    <img src=/S/flags/Argentina.gif border=0 alt=Argentina width=30 height=18> <big><font color=4682b4><font face="Comic Sans MS">Diegol</font face=comic></font color=4682b4> </big>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •