Change SQL

Hello,

The task I am facing problem with, is to pass to a crystal report MYSQL, an SQL statement that I got at runtime.

After reviewing the “Crystal Reports 8.5 the Complete Reference” by “George Peck” I got the following codes, putted in the CRViewer Form, which are working, but not as I wish to.

Option Explicit
Dim Report As New ItemReport
Dim rs As New ADOR.Recordset
Dim ADOConnection As ADODB.Connection
Dim ADOCommand As ADODB.Command

Private Sub Form_Load()
Screen.MousePointer = vbHourglass

' Open the data connection
Set ADOConnection = New ADODB.Connection

'These Jet are to set the odbc, and provider for the report data source.
ADOConnection.Open "Provider=MSDASQL;Persist Security Info=False;Data Source=Millenium Database;Mode=Read"

' Create a new instance of an ADO command object
Set ADOCommand = New ADODB.Command
Set ADOCommand.ActiveConnection = ADOConnection
ADOCommand.CommandText = "item_file"
ADOCommand.CommandType = adCmdTable

' Add the datasource to the repot
Report.Database.AddADOCommand ADOConnection, ADOCommand

‘Get the SQL Statemtn and send it to the report
rs.Open MYSQL, ADOConnection
Report.Database.SetDataSource rs, 3, 1
Report.description.SetUnboundFieldSource "{ado.description1}"
'Report.OpeningDate.SetUnboundFieldSource "{ado.opening_Date}"


CRViewer1.ReportSource = Report
CRViewer1.ViewReport
Screen.MousePointer = vbDefault

End Sub

Private Sub Form_Resize()
CRViewer1.Top = 0
CRViewer1.Left = 0
CRViewer1.Height = ScaleHeight
CRViewer1.Width = ScaleWidth
End Sub

Private Sub Form_Unload(Cancel As Integer)
rs.Close
ADOConnection.Close
End Sub


1) I used the unbound fields because nothing worked with the ordinary fields.
2) I am not being able to use other then the “Select * …” in MYSQL, noting that I may use to select only one field from different tables.
MYSQL: Select description1 from item_File
N.B. I am not getting an error but an empty report.
3) I am not being able to select from more than one table.
MYSQL: SELECT Item_File.Description1, Group1.Description FROM Group1 INNER JOIN Item_File ON Group1.Item = Item_File.Group1;
N.B. I am getting the following error: This Field Name is not known.

4) Regarding the second and third problem, when I run a very simple Statement, let’s say; MYSQL: “Select * from Regions”, for the first time, the report is open greatly. Without closing the Project, if I change MYSQL to: “Select * from Currencies”, I am getting the same result as the execution with the first SQL Statement. I am not getting any error. If I closed the application, and rerun it, and enter the second MYSQL, which was “Select * from Currencies”, I get the correct result.

Please help me it’s very important.
I am really very confused because it is sop simple, but Why I am getting all these errors I really don’t know.
I think I gave all necessary details, if you think that you need any more details please feel free to send me an e-mail at:
[email protected]


N.B: In all cases above I checked in a msgbox the content of the SQL Statement, and it is executing correctly.