Welcome to the Sage 300 Support Group on Community Hub! Available 24/7, the Forums are a great place to ask and answer product questions, as well as share tips and tricks with Sage peers, partners, and pros.
I have a colleague that is getting an error message when running certain custom Crystal reports from within ACCPAC. Apparently these all used to work before going from version 6.0 to 6.1. here is the error:
What is baffling me is how these reports ever worked in the first place. These reports are pulling data from an ACCPAC database and another non-ACCPAC database. In my experience I have never been able to make these kind of reports work and I thought it was impossible. But I know this person isn't lying to me and they used to work.
The exact symptoms are that when they run the report they get the parameter window asking for input and after that they get the error message.
So obviously it can't find the non-ACCPAC table (ie. GLAFS_EndBals).
Anyone have any idea how this used to work but now it doesn't?
0 Andy Bruner over 10 years agoBy the lack of replies I am guessing that everyone is stumped.
Well, despite the lack of enthusiasm for my question I have a solution! So for anyone out there wondering how you can run a report from within ACCPAC that can access non-ACCPAC databases here is what to do:
1) Make two ODBC links: 1 to the ACCPAC database and 1 to the non-ACCPAC database.
2) In the crystal report, make connections to those two links and then use the appropriate link for the different tables. In screenshot you can see that I use the PNB on PRODUCTION link for the tables that are in the ACCPAC database and I use the PAR link for the tables in the non-ACCPAC database.
0 Django over 10 years ago in reply to Andy BrunerAndy - thanks for sharing that. I hadn't considered creating two connections within the same report. Typically I've been using sub-reports or creating the reports in Excel where I can leverage the ability to go through the data in a non-linear fashion.
Having worked with the Crystal API and watching them make a mess of it with the .Net versions (just check out the SAP support forums for how many people simply can't get connected to the database) I can see how Sage might not want to rock the boat and change up too much. But if someone did just change up the logic to not require that the tables be within Datadict.dat to be considered an 'accpac' table then we could do so much more without all of the song an dance that we have to go through.
0 Ettienne Schwagele over 10 years ago in reply to DjangoYou don't enjoy song and dance ;)
Yes it would be great if there were a bit more flexibility available in terms of reporting from non-Accpac tables or SQL queries or SQL views etc. In the past I've made use of CSV files or Access dbs to get around some reporting issues, but it is becoming more problematic to use external dbs in Sage 300 reports.
One cannot always create ODBC DSNs on client computers in order to make reports run (as done by the OP) so a solution is needed that does not require a handson approach.
Steve Smith has a blog post on reporting with some interesting tips:
