ACCPAC Union Query

[SundancerKid]

I have tried to creat a Union Query in MS Access using the data in ACCPAC 5.1 and running a crystal report to access this Union Query in Crystal. I currently get an error accessing the Union Query, I have to creat an access table from the union Query to make it work. The data I am accessing is the Order Invoice data and the Credit Notes data. I am joining the 2 queries together to come up with one file hense the union query.

If there was another way then I would use visual linking in crystal to make this work but there is sometimes a key field from Order Invoice that matches the Credit Notes but most of the time not.

Here is an example of a Union Query

SELECT City, CompanyName, ContactName, "Customers" AS [Relationship]
FROM Customers
UNION SELECT City, CompanyName, ContactName, "Suppliers"
FROM Suppliers
ORDER BY City, CompanyName;

Is there any way to creat a view or query in ACCPAC??

 

[DjangMan]

You haven't specified the error so it could be an Access error or an error from the database engine.

What is your ACCPAC database? Pervasive, MSSQL or ...? What I would suggest is to check out the SQL documentation for your database. What you're trying to do sounds reasonable. Another option is to load the invoices and credit notes into a table in Access and report off of it. Only update that table as required by the user.

Djangman

 

[SundancerKid]

Hi Djangman,

You asked "What is your ACCPAC database?" it is ACCPAC 5.1 using Pervasive SQL

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

 

[DjangMan]

You could build the table in Pervasive as well - just keep in mind that a dbDump/Load process won't maintain your table. Then your stored procedure might work as well.

Just a note - even though ACCPAC 5.2 starts the move to ODBC for all databases the transactional engine is still used by ACCPAC adn therefore you can't use triggers (to keep your table populated as the database is updated).

You might try using the Pervasive OLE DB provider vs. using ODBC. It may handle the stored procedure better...

What is the code of your stored procedure?

Regards,

Django

 

[Relloc]

If you are running Pervasive 2000 load service pack 4 if you want any success.

Or

Try in MSDE linked server using MS SQL server instead.


Regards
Relloc