ADO Recordset don't returns INNER JOINs

[Emidio]

Hi guys.

I have two tables (Bags and Category) and I need to JOIN the tables.

If I use the DAO object whith the SQL statemant, its works OK, but if I try to use the ADO object, the Recordset comes empty.

I try to use this code:

cSQL = ""
cSQL = cSQL & "SELECT Bag.Name, Category.Price "
cSQL = cSQL & "FROM Bag "
cSQL = cSQL & "INNER JOIN Category "
cSQL = cSQL & "ON Bag.CodCategory = Category.CodCategory"
cSQL = cSQL & "WHERE Bag.Code = '" & Format(CodeBag, "00000") & "'"

Set RecSet = New ADODB.Recordset
RecSet.Open cSQL, cnConexao, adOpenKeyset, adLockBatchOptimistic

The same SQL statement works fine in Microsoft Access and VisData, but not in my application. If I'm cut the INNER JOIN statement, the Recordset comes with 1 row.

Do anyone know how can I resolv this?

Tkx from Brazil!

[This message has been edited by Emidio (edited 01-31-2000).]

[pardede]

Maybe the problem is not in the SQL but in your connection object (cnConexao), can you show this.

On the other hand, try to open a recordset via a database object like:

set db = OpenDatabase("c:\dbname.mdb")
  set RecSet = db.OpenRecordset(cSQL)

It just might help...

[Emidio]

Tkx Pardede.

I'm saw the connection object and change the kind of access to database.

Was:

cnConexao.ConnectionString = "Provider=Microsoft.Jet.OLEDB.3.51;Data Source=D:\Sistemas\locmem\dados\century21.mdb;Persist Security Info=False"

And I change to:

cnConexao.ConnectionString = "DSN=Century;uid=sa;pwd="

Now, I'm using the ODBC driver to access the database and works fine.

Tkx again.

[Clunietp]

Is BAG.CODE a numeric or text field? If it is numeric, you do not need the single quotes around your WHERE delimiter