Works in Microsoft Query, but not with QueryTables
 

I have a query that runs fine in Microsoft Query, but when I try to run
the query as part of a QueryTable I get "Run-time error '1004':
Application-defined or object-defined error."

Are there any differences between these 2 ways of executing a query
that I am unaware of? Can I get more specific information about the
error?

Any help would be greatly appreciated.

Works in Microsoft Query, but not with QueryTables
 

Matt,
Maybe you can get more info from:
Application.ODBCErrors
Application.OLEDBErrors

Show us your code for the QT.

NickHK

wrote in message
ups.com...
I have a query that runs fine in Microsoft Query, but when I try to run
the query as part of a QueryTable I get "Run-time error '1004':
Application-defined or object-defined error."

Are there any differences between these 2 ways of executing a query
that I am unaware of? Can I get more specific information about the
error?

Any help would be greatly appreciated.

Works in Microsoft Query, but not with QueryTables
 

I found out that the problem is with the CREATE OR REPLACE VIEW
statement - can you not use views with a QueryTable?

This is the query I am trying to execute - it will execute if I take
out the first 3 lines:

CREATE OR REPLACE VIEW pp_clincond
(clinical_cond, person_id, chg_allow_amt)
AS
SELECT Dx.CLINICAL_COND, Member.PERSON_ID, Sum(f.CHG_ALLOW_AMT)
FROM DRUG_CLM f,
PERIOD_PAID Per,
PERSON Member,
DX Dx WHERE f.PERIOD_PAID_KEY=Per.PERIOD_PAID_KEY
and Per.IA_LEVEL='DAY'
and f.PERSON_KEY=Member.PERSON_KEY
and f.DX_KEY=Dx.DX_KEY
and Per.ROLLING_YEAR in ('Apr 1999 - Mar 2000')
and Dx.IA_LEVEL='BASE'
and Member.PERSON_ID in ('1439608601990')
and Member.IA_LEVEL='BASE'
GROUP BY Dx.CLINICAL_COND,Dx.CLINICAL_COND,Member.PERSON_ID
UNION ALL
SELECT Dx.CLINICAL_COND, Member.PERSON_ID, Sum(f.CHG_ALLOW_AMT)
FROM PROF_CLM f,
PERIOD_PAID Per,
PERSON Member,
DX Dx
WHERE f.PERIOD_PAID_KEY=Per.PERIOD_PAID_KEY
and Per.IA_LEVEL='DAY'
and f.PERSON_KEY=Member.PERSON_KEY
and f.DX_KEY=Dx.DX_KEY
and Per.ROLLING_YEAR in ('Apr 1999 - Mar 2000')
and Dx.IA_LEVEL='BASE'
and Member.PERSON_ID in ('1439608601990')
and Member.IA_LEVEL='BASE'
GROUP BY Dx.CLINICAL_COND,Dx.CLINICAL_COND,Member.PERSON_ID
UNION ALL
SELECT Dx.CLINICAL_COND, Member.PERSON_ID, Sum(f.CHG_ALLOW_AMT)
FROM FAC_DTL f, PERIOD_PAID Per, PERSON Member, DX Dx
WHERE f.PERIOD_PAID_KEY=Per.PERIOD_PAID_KEY
and Per.IA_LEVEL='DAY'
and f.PERSON_KEY=Member.PERSON_KEY
and f.DX_KEY=Dx.DX_KEY
and Per.ROLLING_YEAR in ('Apr 1999 - Mar 2000')
and Dx.IA_LEVEL='BASE'
and Member.PERSON_ID in ('1439608601990')
and Member.IA_LEVEL='BASE'
GROUP BY Dx.CLINICAL_COND,Dx.CLINICAL_COND,Member.PERSON_ID