recordset.open returns no records

[Pan]

Recordset.Open returns no records when I use a query with a join in it as the source. If I change the source to a simpler query (with no join), I get records. What's going on???!!!

 

[Liv Manto]

Your join does not generate common data then.

there must be common data between the two joins.

do a left outer join if you dont care if the second table does not a common data.

Ask the clip, what is left outer join query.

 

[Pan]

Even though the query works just fine on it's own?

 

[RoyVidar]

Hard to say without seing the query... would you care to share it, it would make it easier to comment.

 

[modest]

Yes, first post your code.

Second.. try:

dim db as DAO.Database
dim rs as DAO.Recordset

set db = currentdb
set rs = db.openrecordset("query name")

and evaluate your recordset that way

 

[Pan]

Here's the query:

SELECT tblPlant.Sort, qryChargebackData.[Process Level], tblPlant.PLDesc, tblPlant.ProdLineDesc, qryChargebackData.Year, qryChargebackData.Month, qryChargebackData.[Reason Code 2], qryChargebackData.Desc, Sum(qryChargebackData.Amount) AS ChargebackAmount, Sum(qryChargebackData.NumberOfChargebacks) AS Amount, qryChargebackData.Salesman, tblSalesPeople.Name
FROM (qryChargebackData INNER JOIN tblPlant ON qryChargebackData.[Process Level] = tblPlant.PL) INNER JOIN tblSalesPeople ON qryChargebackData.Salesman = tblSalesPeople.Salesman
GROUP BY tblPlant.Sort, qryChargebackData.[Process Level], tblPlant.PLDesc, tblPlant.ProdLineDesc, qryChargebackData.Year, qryChargebackData.Month, qryChargebackData.[Reason Code 2], qryChargebackData.Desc, qryChargebackData.Salesman, tblSalesPeople.Name, qryChargebackData.[Process Level]
ORDER BY qryChargebackData.[Process Level], qryChargebackData.Year, qryChargebackData.Month;

 

[Pan]

DAO.Database
Gives me the "User-defined type not defined." error.

 

[RoyVidar]

One of the things it may bomb at, is the use of reserwed words, Name, Desc, Year and Month, to mention a few, though that would usually create errormessages, they would probably need to be [bracketed], and yes, which method are you using to open this?

 

[Pan]

Here's the code:


Dim cnn as New ADO.Connection
Dim rst as New ADO.Recordset

strSQL = "SELECT qryChargeback.Sort, qryChargeback.[Process Level], qryChargeback.PLDesc, qryChargeback.ProdLineDesc, qryChargeback.Year, qryChargeback.[Reason Code 2], qryChargeback.Desc, Sum(qryChargeback.ChargebackAmount) AS ChargebackAmount, Sum(qryChargeback.Amount) AS Amount FROM qryChargeback WHERE (((qryChargeback.ProdLineDesc) = '" & strProdLineDesc & "') AND ((qryChargeback.Year) = 2004) AND ((qryChargeback.Month) <= " & Forms!frmCriteria!cboMonth & ")) GROUP BY qryChargeback.Sort, qryChargeback.[Process Level], qryChargeback.PLDesc, qryChargeback.ProdLineDesc, qryChargeback.Year, qryChargeback.[Reason Code 2], qryChargeback.Desc; "

Set cnn = CurrentProject.Connection
rst.Open strSQL, cnn, adOpenStatic, , adCmdText

MsgBox rst.RecordCount & " records."



The record count is 0. When I run "qryChargeback", there are over one hundred records. Even if I set strSQL to "SELECT * From qryChargeback;", it still comes up with no records. I'm baffled...

 

[RoyVidar]

ADO isn't always too happy with nested queries, but you may have a bit more luck using the following declarations:

Dim cnn as New ADODB.Connection
Dim rst as New ADODB.Recordset

Though I prefer

Dim cnn as ADODB.Connection
Dim rst as ADODB.Recordset
set rst = new ADODB.Recordset

But your declaration should have given errors too. Also, don't rely on the recordcount property of ADO recordset (then at least try clientside cursor), test for records with

if not rst.bof and not rst.eof
' contains records
end if

There isn't any parameters in the qryChargeback query, is it?

Try setting a reference to Microsoft DAO 3.# Object Library (in VBE - Tools | References) and try modest's suggestion. Would be fun to know if it worked with DAO as is (just replace "queryname" with strSql)

 

[Pan]

Thanks for your time.

I meant "ADODB", not "ADO". Sorry.

Could it be as you said, ADO is just freaking out on the nested query(s)?

Thanks again.

 

[modest]

to use DAO... you need a reference to DAO... go make sure you have one.

Plus

Forms!frmCriteria!cboMonth -> '" & [FORMS]![frmCriteria]![cboMonth].[Value] & "' (don't for get the single quotes inside the double quotes)

2004 -> '2004' (dont forget the single quotes at all)

 

[RoyVidar]

If it's not giving any errormessages, just not returning any records, that would be my guess. Did you try DAO?

 

[modest]

Dim db as DAO.Database
Dim rs as DAO.Recordset

set db = currentdb

strSQL = _
    "SELECT " & _
    "qryChargeback.Sort, " & _
    "qryChargeback.[Process Level], " & _
    "qryChargeback.PLDesc, " & _
    "qryChargeback.ProdLineDesc, " & _
    "qryChargeback.Year, " & _
    "qryChargeback.[Reason Code 2], " & _
    "qryChargeback.Desc, " & _
    "Sum(qryChargeback.ChargebackAmount) AS [ChargebackAmount], " & _
    "Sum(qryChargeback.Amount) AS [Amount] " & _
    "FROM " & _
    "qryChargeback " & _
    "WHERE (" & _
    "((qryChargeback.ProdLineDesc) = '" & strProdLineDesc & "') AND " & _
    "((qryChargeback.Year) = '2004') AND " & _
    "((qryChargeback.Month) <= '" & [Forms]![frmCriteria]![cboMonth].[Value] & "')) " & _
    "GROUP BY " & _
    "qryChargeback.Sort, " & _
    "qryChargeback.[Process Level], " & _
    "qryChargeback.PLDesc, " & _
    "qryChargeback.ProdLineDesc, " & _
    "qryChargeback.Year, " & _
    "qryChargeback.[Reason Code 2], " & _
    "qryChargeback.Desc;"

set rs = db.OpenRecordset(strSQL)

You must go to Tools => References and check the "Microsoft DAO <version> Object Library"

** please don't mind the excessive quotation

 

[Pan]

I set the DAO reference, plugged in the code, and it's working.
Thank you very much.

 

[modest]

not a problem - I help where I can -modest

I'm sure this could work with ADO... I'm just not as familiar with it. Besides the new DAO can do about everything ADO can. So in my opinion it's easier to use with and has the same functionality.

 

[Mile-O]

Is it just because the recordset object (in ADO) has not been "populated"?

 

[Pan]

I'm not exactly sure what you mean.
I suspect, however, that it's just a limitation of ADO because when I swapped the SQL with a simpler query, it worked fine.
The DAO is working great. I feel I need a better understanding of the difference between the two. Anybody have any info?
Thanks again.

 

[RoyVidar]

SJ McAbney, you're thinking of the DAO method of issuing a .movelast .movefirst to populate?

Sorry, doesn't work with ADO. And the fact that the .recordcount in this thead returned 0, shows the recordset supported .recordcount, and the count would be correct, else it would have returned -1 - which is a bit different from DAO, isn't it

But one will often find advice not to rely on ADO recordcount.

Yes, there are some difference, some things you can do with ADO that you can't do with DAO and the other way around. Since ADO isn't a native to Access method, as DAO is, but a bit more general, differences are to be expected. I've just accepted that opening recordsets based on Access stored queries within stored queries might be one of those.

There are lots of information on the net, on this site... on differences, and different advice on what to chose and why. To me it's clear that DAO is most often faster on native Access tables, whilst ADO has some advantages if you need to work with other databases.

 

[modest]

Yes... ADO is great for working with other programs... especially when working with LDAP.

But recordsets (queries)..tables..Access objects.... DAO If you need further knowledge about them... go on msdn... or use your computer's search function and look up "*.chm" look for ADO and DAO files.