Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Nottingham, UK, Nottinghamshire, England
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Coding a Union query in VB6 (VB6 and Access)

    I have a Union query in an Access database (SELECT a FROM b WHERE c <= [param1] UNION SELECT d FROM e WHERE f <= [param1]) and this works well.

    In a VB6 project which has a Data Environment connected to the database, I can add a command to the DataEnvironment which references the Union query as a Stored Procedure. Fine, so far. But how do I use the command? Let's say it's called qryUnion.

    In code, I can code 'DataEnvirontment1.qryUnion strDate' though it objects to the number of parameters(!). Also, I don't seem to be able to find rsqryUnion anywhere (even in Intellisense) and therefore cannot reference the returned data (in fact I only want the RecordCount).

    Has anyone any idea how to do this? I've not used Union queries before, though the one in Access works fine.

    Thanks in advance.

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

    Re: Coding a Union query in VB6 (VB6 and Access)

    I don't have VB6, but my guess would be that the problem is not the ynion query, but the parameter - the Data Environment probably doesn't know how to handle a parameter query. You could assemble the SQL string in code (with the parameter filled in, for example from the value of a text box in your form), and pass this SQL string to the Data Environment.

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

    Re: Coding a Union query in VB6 (VB6 and Access)

    You don't actually have a parameter query in SQL terms, so I'm not surprised it doesn't work from VB6. You need the parameters clause at the beginning of the query, which tells the dataenvironment how many parameters there are. Then you need to populate those parameters in code. Are you using ADO? You don't show how you're creating or populating the recordset, so more specific answers would be pure guesswork, but if you get a parameter error, you aren't going to get a recordset back anyhow.
    Charlotte

  4. #4
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Nottingham, UK, Nottinghamshire, England
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Coding a Union query in VB6 (VB6 and Access)

    Thanks, Hans. Passing parameters to the DataEnvironment proved beyond me for a Union query (the Data Environment didn't seem to want to accept a Union query in a command), and in the end I got what I wanted by using an ADODC data control and passing the SQL in directly (using the parameter value I wanted). Somewhat frustrating but I got there! I only wanted the record count and in Access I had a query that gave me that from another (union) query, but of course passing a parameter through from VB6 to this query to pass to the first query seems not to be possible!

    Thanks again.

  5. #5
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Nottingham, UK, Nottinghamshire, England
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Coding a Union query in VB6 (VB6 and Access)

    Thanks, Charlotte. I was using a Data Environment command to reference a union query in the Access database (since I couldn't get a command to accept the union SQL at all), but couldn't figure out how to pass the parameter. I had 'DataEnvironment1.MyCommand MyParameter' but VB6 objected and also refusewd to provide a recordset in Intellisense. In the end I reverted to an ADODC data control and passed the union SQL into the RecordSource directly including the parameter value. It seems a tad slow but it works.

    Thanks again.

Posting Permissions

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