Creating a Stored Procedures Access Violation

[SundancerKid]

I was trying to use a Union Query in MS access to put two files together and add to a Crystal Report. The main database is ACCPAC 5.1 OEINVH, OEINVD Union to OECRDH & OECRDD. I am trying to match up the invoiced items with Credit Memo's done the same period.

The Union Query looks like this:

SELECT OEINVH.SALESPER1 as SALES, OEINVH.CUSTOMER as CUST, OEINVH.BILNAME as Name, OEINVH.TERRITORY as Terr, IIf(OEINVD!LINETYPE=1,"IN"," &quot AS Type, OEINVH!INVNUMBER AS Reference, OEINVH.INVDATE as OEDATE, OEINVD!UNITPRICE*OEINVD!QTYSHIPPED AS SalesAmt, OEINVD!QTYSHIPPED*OEINVD!UNITCOST AS CostAmt
FROM OEINVH INNER JOIN OEINVD ON OEINVH.INVNUMBER = OEINVD.INVNUMBER
WHERE (((IIf([OEINVD]![LINETYPE]=1,"IN"," &quot)="IN&quot)

UNION

SELECT OECRDH.SALESPER1 AS Sales, OECRDH.CUSTOMER AS Cust, OECRDH.BILNAME AS Name, OECRDH.TERRITORY AS Terr, IIf([OECRDD]![LINETYPE]>0,"CN"," &quot AS TYPE, IIf([OECRDD]![LINETYPE]=1,[OECRDH]![CRDNUMBER],Trim([OECRDH]![CRDNUMBER]) & "-" & Trim([OECRDD]![MISCCHARGE])) AS Reference, OECRDH!CRDDATE AS INVDATE, IIf([OECRDD]![LINETYPE]=1,[OECRDD]![QTYRETURN]*[OECRDD]![UNITPRICE],[OECRDD]![EXTCRDMISC]) AS SalesAmt, IIf(OECRDH!CRDSTATUS=2 And OECRDD!RETURNTYPE=1,OECRDD!QTYRETURN*OECRDD!UNITCOST,0) AS CostAmt
FROM OECRDH INNER JOIN OECRDD ON OECRDH.CRDUNIQ = OECRDD.CRDUNIQ
WHERE (((IIf([OECRDD]![LINETYPE]>0,"CN"," &quot)="CN&quot);

The union query works fine inside MS Access. But I am unable to access it outside of Access. I proceeded to build a table in access from the query and I am able to access the information.

I would like to use a pervasive Stored Procedure but I keep getting the following error:

Pervasive] [ODBC Client Interface] [Client LNA] [The ODBC Client Interface]Access Violation

Please Help, Thanks in advance

SDK

 

[Reflex71]

SDK,

Did you have any luck with your stored procedure? I am trying to do something similar, calling a stored procedure that populates a pervasive table from Crystal Reports for ACCPAC Advantage Series and am getting the same error.

 

[TomKane]

Does it work if you execute the query in the control centre or SQL Scope?