Dcount of Query in VBA gives Error 3219

[krausr79]

I have a database which runs quality queries on data before uploading it. Normally I run a series of if Dcount>0 statements on different queries where if there are any results in the query, something is wrong and the upload stops. I have one query in a newer database where trying to do dcount on it gives runtime error 3219. At first I thought it was the iif statements or query criteria based on form fields, but even after I removed them as a test the error remained. Here are the simplified queries:

qryQC2: runnable, but fails on dcount. A run-of the mill left join of table>query>table:
SELECT tblLoadCA.LCN, qryOmegaCurrent.LSACONXB, tblLoadCA.CA, O2B_LDMHP.CANUMBHP


FROM (tblLoadCA LEFT JOIN qryOmegaCurrent ON tblLoadCA.LCN = qryOmegaCurrent.LSACONXB) LEFT JOIN O2B_LDMHP ON (qryOmegaCurrent.IPN = O2B_LDMHP.IPN) AND (qryOmegaCurrent.ISN = O2B_LDMHP.ISN);

qryOmegaCurrent: Can be dcounted without error. The 'middle' query joined in the first query. Just 4 joined tables:
SELECT O2B_LDMXA.EIACODXA, O2B_LDMXB.LSACONXB, O2B_LDMHG.PLISNOHG, O2B_LDMHA.CAGECDXH, O2B_LDMHA.REFNUMHA, O2B_LDMHG.ISN, O2B_LDMHG.IPN
FROM ((O2B_LDMXA INNER JOIN O2B_LDMXB ON O2B_LDMXA.IEN = O2B_LDMXB.IEN) INNER JOIN O2B_LDMHG ON O2B_LDMXB.ISN = O2B_LDMHG.ISN) INNER JOIN O2B_LDMHA ON O2B_LDMHG.IPN = O2B_LDMHA.IPN;

Anything that's O2B_ is an external ODBC database. I've done several things like this and never encountered the error. Why doesn't this work?

[ranman256]

That is an object error. Are you sure it happens at the sql execute? It sounds like something else in code (an object) is not getting assigned.

If the error IS in the sql,...
does it have any paramerters invovled?
have all tables been refreshed ,in case there where table modifications?

[krausr79]

In the immediate window if I type
msgbox(Dcount("*","qryOmegaCurrent"))
then I get a messagebox with the correct number of records.

if I type
msgbox(Dcount("*","qryQC2"))
then I get the error.

Or I should say I got the error. I made a workaround that uses recordset EOF and BOF and that worked fine. Then we uploaded some data. Then I got your reply and started checking again and with the new test data there is no error.. I can use Dcounts just fine.

In the previous data set, both queries were simple select queries made in the query designer. Neither had parameters. I am not familiar with refreshing tables unless you are referring to the form.refresh of a form with data on it.

Since I am now unable to repeat the error and have a workaround, I'm marking this thread as solved, though I don't actually know what caused the problem in the first place.