hi everybody out there, i'm facing a problem trying to joining dbf tables. this is my scenario:
i'm using oledb provider for visual fox pro 9.0 to access dbf tables in a free table directory. i have two tables i like to join, the resumed tables are:

table m_solic fields: fech, cod_est, cod_est_rc.
table c_est_solic fields : cod_est, estado.

and i want to make a query with this result:

table (query) solic: fech, cod_est, desc_est, cod_est_rc, desc_est.

so when i'm configuring the oledbdataadapter i wrote directly the following sql sentence in the wizard:

SELECT m_solic.*, c_est_solic.ESTADO, c_est_solic_1.ESTADO
FROM (m_solic INNER JOIN c_est_solic ON m_solic.COD_EST = c_est_solic.COD_EST) LEFT JOIN c_est_solic AS c_est_solic_1 ON m_solic.COD_EST_RC = c_est_solic_1.COD_EST

which apparently is right!!! but the wizard give errors in select sintax, and unrecognized phrase or keyword.
i use the query builder in the oledbdataadapter configuration wizard and it builds a similar statement but it doesn't work too giving the same errors.
any idea whats happening in here???? thanks in advance!!!!

If these are datasets, try using the merge method to merge the 2 tables into 1 datset and then execute your select statement.

Use DataAdapter to fill both tables in DataSet. Create a relation between them at runtime –

   Dim CON As String = "<Connection String>"

   Dim sqlm_solic As String = "Select fech, cod_est, cod_est_rc from m_solic"
   Dim DAm_solic As SqlDataAdapter = Nothing
   Dim sqlc_est_solic As String = "Select cod_est, estado from c_est_solic"
   Dim DAc_est_solic As SqlDataAdapter = Nothing
   Dim DS As DataSet = Nothing
   Try
      Dim connection As New SqlConnection(CON)
      DAm_solic = New SqlDataAdapter(sqlm_solic, connection)
      DS = New DataSet
      DAm_solic.Fill(DS,"m_solic")
      DAc_est_solic = New SqlDataAdapter(sqlm_solic, connection)
      DAc_est_solic.Fill(DS,"c_est_solic")

      DS.Relations.Add("myRel",DS.Tables["m_solic"].Columns ["cod_est"], _
      DS.Tables["c_est_solic"].Columns["cod_est"])

      connection.Close()
   Catch ex As Exception
      MsgBox("Error")
      Me.Close()
      Return
   End Try

I would suggest using an ORM framework (I use DevForce Express) for quickly establishing relations.