VB 6 Reports problem

[ekafrawy]

Hi,
This is my first post on VBForums. I am trying to run some reports using the reports tool in VB6 this is the only i have to use.(no Crystal Reports). I am able to make reports pulling data from my Oracle database, but I can only get data from one table. I want to be able to pull information from multiple tables from the database, and I have no clue how to do this.
Anyone have any ideas or links to information on this?

Thanks
Eric

[MystikShadows]

Hi Eric,

The Reporting tool that comes with VB 6 doesn't allow polling for multiple tables. However there is a workaround to this depending on the specific needs of your report.

For example you could use a TempTable (worktable) where in VB you perform multiple "INSERT INTO FROM SELECT FROM <TABLENAME>" statement for each data you want (provided the data structure is the same).... ALl into that work table (with a field called table name so you can group by that field in your report. You could also perform a SELECT * FROM <Table1>, <Table2>, <TableN> where COnditions would also get you data from more than one table. Really depends on your needs. So, I could give you much more details and examples if I knew the following.

- The Tables you need data from (field definition would be a big plus ;-).
- What you need done with the information
- What kind of output you expect (a description of the fields you used to design the report).

Then we'll see what we can do :-)

Hope this helps

[vb_dba]

You should be able to generate a Recordset using an SQL statement that joins the tables together, then in the DataReport_Initialize() event, set the datasource of the report to the Recordset:

Private Sub DataReport_Initialize()
    Dim oRS  As Recordset
    Dim sSql  As String

    sSql = "Select a.Field1, a.Field2, b.Field3, b.Field 4 " & _
           "From Table1 a Inner Join Table2 b On a.Field1 = b.Field1 " & _
           "Where a.Field2 = 'blahblah'"
    Set oRS = New ADODB.Recordset
    oRS.Open sSql, oCon, adOpenStatic, adLockReadOnly

    Set Me.DataSource = oRS
End Sub

If you create procedures in Oracle, then you might do that instead of writing dynamic SQL in your VB app.