leonjanzen
asked on
Changing the SQL statement for a Data Environment Command programmatically
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.I tem(n).Com mandText = 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?
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.I
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Forget Data Reports, DE and ADODC go directly to using ADO and Crystal Reports. Data Reports, DE and ADO are useless in any production quality applications. You will run into many issues with them.
Let us know if you need help in switching,
Leon