Changing the SQL statement for a Data Environment Command programmatically
Posted on 2004-03-30
I am using a Data Report to display data from an Access Database. The Data Report gets its data from a Data Environment Command object.
I need to customize which records are viewed, based on which records the user is viewing. I would like to change the SQL statment for the Command object, before using the Data Report to view the data from the Command object. First, i'm not sure how to reference the SQL statement other than dataEnvironment.Commands.Item(n).CommandText = strSQL which works, but it is ugly.
But the main problem is that after I have changed this value, the report no longer recognizes the Command object. it says "The Microsoft Jet database engine cannot find the input table or query 'commandName'. Make sure it exists and that its name is spelled correctly".
The worst part is, that when I step through the commands in debug mode, it works, once. The second time it gives me the same error message, if I haven't restarted the application in between.
Any suggestions? It seems like a VB bug to me. If it can't be changed, can I avoid using the Data Environment altogether and have the reports refer to an ADO data control instead, and how would I do that?