Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Aug 2002
    Location
    Grand Rapids, Michigan, USA
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Create new Union Query using ONLY code (2000)

    this is kind of a weird one...

    i have the code for a union query pasted into a text box on a form. With only code, I want to be able to create/open a new query & paste what I have in the SQL view of the query window, then save the query and a new name and exit out. Any thoughts?

    - Andy

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Create new Union Query using ONLY code (2000)

    Why? You can simply create the query from code without pasting the code anywhere if the union query code you have in the textbox is valid. So what are you actually trying to accomplish here?
    Charlotte

  3. #3
    New Lounger
    Join Date
    Aug 2002
    Location
    Grand Rapids, Michigan, USA
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create new Union Query using ONLY code (2000)

    I'm not quite sure how to do that. Here's the scenario...

    I have a client who needs to create many different mailings based on different criteria. To automate this process, I've developed a listbox control that lists available queries that she can combine together as needed. I have a button that goes through all the selected names of the list box & converts it to sql code. I just threw a textbox up there so we could see the final code.

    But really, I want her to be able to store this code as a union query under a new name. It's probably simple for others, but for some reason I'm hitting a wall. If you can give me a sample line of code to save this string as a query, that'd be great.

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Create new Union Query using ONLY code (2000)

    DAO or ADO? And I presume you made sure all the queries in your list have the same data structure, because otherwise the unions wont' work.
    Charlotte

  5. #5
    New Lounger
    Join Date
    Aug 2002
    Location
    Grand Rapids, Michigan, USA
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create new Union Query using ONLY code (2000)

    ADO. And yes, they are identical.

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Create new Union Query using ONLY code (2000)

    Here's a routine to create a view from ADO, and it will work with a union query. Keep in mind that this won't show up in the query tab in a 2000 mdb because it's been added to the Views collection, which isn't visible there. It's there and you can use it from ADO, but it's hidden. I left the strDBPath argument in the routine in case you wanted to try it with a remote database rather than the current db.

    You could call it in your form by something like this:
    <hr>CreateQuery CurrentProject.Path & "" & CurrentProject.Name, _
    Me![txtSQL], "MyNewQuery"<hr>
    <pre>Sub CreateQuery(strDBPath As String, _
    strSQL As String, _
    strQryName As String)
    'Created by Charlotte Foust
    'Appends a new view to the specified database
    'from the SQL string passed
    Dim catDB As ADOX.Catalog
    Dim cmd As ADODB.Command

    <font color=448800>'instantiate the objects</font color=448800>
    Set catDB = New ADOX.Catalog

    <font color=448800>'Define a Command object to contain the query's
    'SQL.</font color=448800>
    Set cmd = New ADODB.Command

    <font color=448800>'Open the catalog. </font color=448800>
    catDB.ActiveConnection = CurrentProject.Connection

    <font color=448800>
    'here's the alternative syntax for a remote database
    'catDB.ActiveConnection = _
    "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & strDBPath
    </font color=448800>

    <font color=448800>'StrSQL must contain only a SELECT statement
    'Set the command's commandtext to the SQL string</font color=448800>
    cmd.CommandText = strSQL
    <font color=448800>'Save the query to the database's Views collection.</font color=448800>
    catDB.Views.Append strQryName, cmd

    <font color=448800>'cleanup and exit.</font color=448800>
    Set cmd = Nothing
    Set catDB = Nothing
    End Sub <font color=448800> 'CreateQuery(strDBPath As String, _
    strSQL As String, _
    strQryName As String)</font color=448800> </pre>


    As an afterthought, if you're populating your list of queries from MSysObjects, it will show up in the list. But I hope your user has sense enough not to build unions on unions or the query will eventually hit its limits.
    Charlotte

  7. #7
    New Lounger
    Join Date
    Aug 2002
    Location
    Grand Rapids, Michigan, USA
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create new Union Query using ONLY code (2000)

    That did the trick!!! Thanks a ton. - Andy <img src=/S/groovin.gif border=0 alt=groovin width=21 height=21>

Posting Permissions

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