Coding a Union query in VB6 (VB6 and Access)

Topic

New Reply

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.

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote