How to group records in Data Report that gets data from .DBF File(foxpro)?

[AndyXis]

Hi Everyone,

I got a problem with creating groups in the Data Report which gets data from a .dbf file(foxpro).

I tried creating a simple report without group headers/footers. The connection to the database is working fine. It displays the data from the database.

But I need to use groups to display the info by Name. The error displays "failed getting rowset/s from current data source"

I've also tried creating groups that gets data from MsAccess, and it's working fine too.. displaying info the way I want it to be.

It seems that the problem is when the report is grouped and gets data from a .dbf file. Btw, these were existing databases so converting to MsAccess might consume a lot of time. I'm using Data Environment.

Can anyone help me here please? I've been searching the net for the same problem for two days now.. Any suggestions or idea will be of great help.. Thanks in advance!

[Hack]

Moved To Reporting

[wes4dbt]

The only other way to do grouping with a datareport is by manually creating a MSDataShape and setting the report datasource at runtime. See below,

        cFinalCond = "SHAPE {SELECT tags.*,us.*,iif(trim(tags.status)='I','Invoiced','Not Invoiced') as invstatus from tags inner join users us on us.userid=tags.userid where " & cWhere & " order by " & cOrderby & " } AS Tag " & _
                    "APPEND ((SHAPE {SELECT * From tagsdetail order by line} As Command1) " & _
                    "RELATE tagnumber To tagnumber)"
                     
        'Open a DATA Shape Connection For Reports
        With conReport
            .Mode = adModeShareDenyNone
            .Provider = "MSDataShape" 'Microsoft Provider for Data Shaping
            .ConnectionString = "Data Provider = Microsoft.Jet.OLEDB.4.0;" & _
                                "Data Source = " & conDb
            .Open
        End With
        
        If (conReport.State And adStateOpen) > 0 Then
            With rsData
                .ActiveConnection = conReport
                .LockType = adLockReadOnly
                .CursorType = adOpenStatic
                .Open cFinalCond
            End With
        End If
        'Set the new Data source of Report
        Set rptTBfull_shape.DataSource = rsData

But I don't think this will solve your problem because the dataEnvironment is also creating a MSDataShape. And you said that's not working for .dbf files.

you should be able to create a recordset from the .dbf files, then insert the data into a temp Access table, then run the report from the Access temp tables.

BTW - Importing .dbf files into Access is very easy and quick. You probably could have setup to new Access db with all your .dbfs in the time you spent researching this problem. You probably will have to do it someday.

Good luck

[AndyXis]

I guess I'll try to create a MSDataShape manually and see if it works.. Because I've seen the datashape in the Data Environment, it's not as specific as what we can do by manually coding it. If this doesn't work, then I think I'll go for MsAccess. I'll try to post here updates.. Thanks for the info.

[AndyXis]

Hi wes4dbt, I would like to ask something. Does Msdatashape requires 2 or more tables? For parent and child relationship? Because I've googled some examples using datashapes, it always had two or more tables relating the parent and child.

Is it possible to use only one table? Just to group the fields of that table to make it more organized.

It will be using one table, a .dbf file, that has redundant entries. It has several duplicate records like for example,

DATE NAME GROSS DED NET
8/15/2010 employee1 grossinc ded net
8/15/2010 employee2 grossinc ded net
8/31/2010 employee1 grossinc ded net
8/31/2010 employee2 grossinc ded net
and so on..

I wanted to make a report that will display it this way: (grouped by employee name)

NAME: employee1
DATE GROSS DED NET
8/15/2010 grossinc ded net
8/31/2010 grossinc ded net

NAME: employee2
DATE GROSS DED NET
8/15/2010 grossinc ded net
8/31/2010 grossinc ded net

Is it possible with datashapes? Thanks in advance.

[wes4dbt]

I don't know that much about MSDataShapes. I usually cut and paste from the DataEnvironment and then tweek it. You should search the "Database Development" forum. If you can't find answer then post a thread there.

I don't have FoxPro files but I did do some research on dBase dbf file, with good results, this code works and would be easy to insert the data into an Access table.

Private Sub Command1_Click()
Dim con As New ADODB.Connection
Dim rs As New ADODB.Recordset

 con.Open "Driver={Microsoft dBASE Driver (*.dbf)};DriverID=277;Dbq=c:\cars"

rs.Open "select ecnum from hist93.dbf group by ecnum", con, adOpenKeyset, adLockOptimistic, adCmdText
 
rs.MoveFirst
Do While Not rs.EOF
    MsgBox rs.RecordCount
    rs.MoveNext
Loop

End Sub

Heres a link about connecting to FoxPro, Check out the last post
http://www.vbforums.com/showthread.p...nnect+dbf+file

[AndyXis]

Thanks for the sample codes. You've really helped a lot. I'll try to work this out. Good day

[wes4dbt]

I created a DataReport with grouping from one dbf that uses DE for the datasource. See attached.
Heres the shape

 SHAPE ( SHAPE {select ecnum from hist93.dbf}  AS Command1 APPEND ({select ecnum from hist93.dbf}  AS Command2 RELATE 'ecnum' TO 'ecnum') AS Command2) COMPUTE Command1 BY 'ecnum'

[AndyXis]

Wes, thanks a lot for your help.. I've switched to ms sql server. It's a lot more smoother than connecting vb6 to foxpro.