How to Make data Environment through coding

[amit mishra]

Dear ones,

The answer you have given (about How To Bind dataReport To an ADO Recordset at Run Time ) is working but as far as I have understood this coding connects different data environment with one single data report by giving name of that data environment at run time in front of datasource as shown bellow (*) at line no 7.


Private Sub Form_Load()
Dim int1 As Integer
DataReport.Show
With DataReport
Set .DataSource = Nothing
DataMember = ""
Set .DataSource = DataEnvironment.rsStudentProfile (*)
On Error Resume Next
With .Sections("section2").Controls
For int1 = 1 To .Count
Item(i).DataMember = ""
Next i
End With
On Error GoTo 0
.Show
End With
End Sub


But My problem is like this :-

I have to access data from different tables, having same field in them

e.g.

Name of Tables Fields
1. marks of_class_1 english, Maths, Science
2. marks of_class_2 english, Maths, Science
3. marks of_class_3 english, Maths, Science

So at one time I want report of class one and other time I want report of class two. I can make different data environment for each class and connect it with data report, with the help of above coding. But for that I have to make about 180 data environments.

What I want is, a way to make data environment at run time so that I could change its contents evry time.

For this purpose I tried to change SQL command of data environment through coding by the following statement in Initialize event. (Syntax is below)

DataEnvironment1.Commands(1).CommandText = SHAPE{SELECT …..FROM … WHERE …… = …..)AS Command1
APPEND [{SELECT …… FROM …….. WHERE ……. = …….} AS Command2 RELATE 'reg_no' TO 'Regno'] AS Command2"



The real SQL command which I am using is below.
DataEnvironment1.Commands(1).CommandText = SHAPE{SELECT `11A2000ROLL`.reg_no, `11A2000ROLL`.Rollno, studentprofile.NAME FROM studentprofile, `11A2000ROLL` WHERE studentprofile.Regno = `11A2000ROLL`.reg_no} AS Command1
APPEND [{SELECT `11A2000marks`.Test1, studentprofile.Regno FROM `11A2000marks`, studentprofile WHERE `11A2000marks`.Regno = studentprofile.Regno} AS Command2 RELATE 'reg_no' TO 'Regno'] AS Command2"


But this statement is giving Syntax error at this place :RELATE 'reg_no' TO 'Regno'] AS Command2"



My data environment is like this


Data Environment
Connection1
Command1
Name
Class
Section
Command2
English
Maths
Science



Am i doing write or is there any other way to it.
Please guide me in this regard

Thanking you in advance


Amit