Need Help in Using Crystal Report with MS Access Database

See more:

VB

Hello everyone,
I'm currently working on a financial application for an organization. My problem is accessing data stored in the database using the crystal report at runtime when my database has a password. It is not that I can't access data stored in a table at all, but when two or more tables are involved, it always generate error. Below are code segments that worked and the ones that give problems:

This one works perfectly:
Declaration below is in public module.bas

Public gbstrDBConnection As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Application.StartupPath & "\FinManagerDB.mdb;Persist Security Info=True;Jet OLEDB:Database Password=dontworry"

This following sub-routine is in a form:

Private Sub GenerateReport()
    Dim mstrUserID As String
    Dim DB_Path As String = My.Application.Info.DirectoryPath
    Dim con As New OleDbConnection(gbstrDBConnection)
    Dim rpt As New crptStationList

    Dim com As New OleDbCommand("SELECT * FROM Stations")
    mstrUserID = "  "
    Try
        com.CommandType = CommandType.Text  'Command type
        com.Connection = con    'give connection to command

        Dim adp As New OleDbDataAdapter 'declare adapter
        adp.SelectCommand = com         'select command for adapter to worker
        Dim ds As New DataSet           'declare dataset
        adp.Fill(ds, "Stations")         'fill the dataset through adapter
        Try
            'Change database path
            rpt.SetDataSource(ds)
        Catch ex As CrystalDecisions.ReportSource.EnterpriseLogonException
            MsgBox(ex.Message)
        End Try
        CrystalReportViewer1.ReportSource = rpt

        CrystalReportViewer1.RefreshReport()
    Catch ex As Exception
        MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Error Alert!")
    Finally

    End Try

End Sub

In the above, I successfully access the data in "Stations" table.

The code segment below didn't work

Private Sub GenerateReport()
        Dim DB_Path As String = My.Application.Info.DirectoryPath
        Dim con As New OleDbConnection(gbstrDBConnection)

        Dim rpt As New crptLoanSummary
        Dim com As New OleDbCommand("SELECT * FROM MembersAccount INNER JOIN MemberRegister ON MembersAccount.MemberNo=MemberRegister.MemberNo ORDER BY MembersAccount.MemberNo")
        Try
            com.CommandType = CommandType.Text                              'Command type
            com.Connection = con                                            'give connection to command

            Dim adp As New OleDbDataAdapter                                 'declare adapter
            adp.SelectCommand = com                                         'select command for adapter to worker
            Dim ds As New DataSet                                           'declare dataset
            adp.Fill(ds, "MembersAccount,MemberRegister")       'fill the dataset through adapter
            Try
                'Change database path
                rpt.SetDataSource(ds)
            Catch ex As CrystalDecisions.ReportSource.EnterpriseLogonException
                MsgBox(ex.Message)
            End Try
            CrystalReportViewer1.ReportSource = rpt
            '

            CrystalReportViewer1.RefreshReport()
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Error Alert!")
        Finally

        End Try

    End Sub

In the above, I tried to extract data from two tables i.e. "MembersAccount" and "MemberRegister" without success.

The above, usually pop up a "Database Login" dialog box requesting for database:, LoginID:, and Password:, which had already been included in the connection string.

I will be very glad if you guys can bail me out.

Thanks