Two tables in one report

[gate7cy]

I have a big problem presenting the information I want on the report. I am trying to put data from two tables so far with no success. I did a good search on the forum and google without finding an answer to correct it. I have tables 1 and 2. I use 3 fields from table1 and one from table2. The fields from table1 show up but not the ones from table2. Here is the code I use to load the report and fill it up on the winform load event.

        Dim rpt As New CrystalReport1() 'The report you created.

        Dim conn As OleDbConnection
        Dim MyCommand As New OleDbCommand
        Dim myDA As New OleDbDataAdapter
        Dim myDS As New Database2DataSet  'The DataSet we created

        Try

            conn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\Serverjde\jde\Database0.accdb;Persist Security Info=False;")
            MyCommand.Connection = conn
            MyCommand.CommandText = "SELECT * FROM AllServices ORDER BY StartDate ASC"
            MyCommand.CommandType = CommandType.Text
            myDA.SelectCommand = MyCommand

            myDA.Fill(myDS, "AllServices")
            rpt.SetDataSource(myDS)
            CrystalReportViewer1.ReportSource = rpt

        Catch Excep As Exception
            MessageBox.Show(Excep.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try

I have tried some other ways to call and fill the report but with no success. As I see from the forum this is big issue as most post with this problem are not resolved or with no replies from anyone with a solution to it. Thanks for the replies and all the help so far

[jggtz]

You could try a Select query that brings all the fields you need, as :

SELECT Table1.Field1, Table1.Field2, Table1.Field3, Table2.Field1 
FROM    Table1
INNER JOIN Table2 ON Table2.Field0=Table1.Field0
ORDER BY Table1.Fieldx

[gate7cy]

Thanks for the reply. tried it but not result. Any other clues. I am buffled. I have tried creating a new report to load to the form. I choosed a different way to find my database rather than using the dataset of vb. I used the ODBC/RDO to find my database and load it. All works fine. The preview boxes show the correct data and everythin is fine. When I try to load it it request a password and loginID. >???????? I never entered it one.The I made a new report with pass and id but still "logon failed". This is the code I have on form load to call and fill my reportviewer.

CrystalReportViewer1.ReportSource = New CrystalReport4()

[rasinc]

I've never used Access 2007 (I think that's what you are using but have you tried Admin as the userid and no password?

[gate7cy]

Yes I have done that. I cannot make it work. thank for the reply.. Any other ways I can do this? I have tried to use my dataset for the report but I encounter different problems there. The report does not work when I use fields from two tables. I have tried using other connection options to connect to the db but no success. When I try to add database in one of the options available it does find the 2007 db but finds only a 2003 version of it that I used in the past. Crystal reports has problems with access 2007?