Use Accpac to print Crystal Report referencing non-accpac database

[WalterHeisenberg]

Hi guys,

Here is my problem. I have a report I've made that queries a custom my-sql database using a command to get an integer ID field and a datetime field.

The report is only going to be used by Accpac users so I was hoping to be able to add it to their Accpac desktop and be done with it.

When I try to run the report I get the error: Failed to retrieve data from the database. Vendor Code 208 which is often a missing table. We're using Accpac ERP 5.5 PU2, SQL-Server, and have no errors on DI.

Does anyone know if this can be accomplished? I was hoping by using a command to a non-accpac ODBC connection Accpac wouldn't try to change the connection when the report was launched.

Thanks for your help in advance.

 

[Jay Converse]

Put the non-Accpac data in a subreport that uses a DSN with network authentication.

 

[WalterHeisenberg]

Hi Tuba,

Thanks for the response. I don't believe there is an ODBC driver for mySQL that contains network authentication. Is this is a must in order to accomplish what I'm after? Thanks for your help.

 

[ettienne]

You can put a user id and password in the MySQL DSN.

 

[WalterHeisenberg]

Right but that fails if I try network credentials. It only connects using the ID/password for MySQL server.

I'm not very familiar with MySQL. Could the issue be we need to enable Mixed Authentication similar to MS-SQL?

 

[vbdbcoder]

I would use file DSN to connect CR in CR designer. You will need to manually add the "PWD=" line in file DSN uing notepad. By default, file DSN does not create the "PWD=" line if you use the wizzard to create.

 

[WalterHeisenberg]

Thanks for everyone's suggestions thus far. I've tried using a file DSN (which is working in VBA applications) but that didn't do the trick. Neither did the sub-report route. Same Accpac error. I'm using MySQL ODBC 5.1 (and tried 3.5).

I'm curious is there is a work-around out there or anyone has done this successfully with MySQL? Although I'm not a big fan of doing things in Excel, it might end up being easier to deploy to Accounting if I can't add crystal reports via Accpac. I could make an .exe file to do thisbut I'm not sure if I'd run into similar issues.

Thanks for help in advance.

 

[Jay Converse]

MySQL? LOL, that's for Linux systems, I'm sure you're the first who has tried with Accpac.

 

[DjangMan]

You could try wrapping the Crystal Report run-time engine inside of an application. Don't let Accpac manage the database connections. There are Crystal Report viewer applications that you can get as well that might do the trick.

 

[WalterHeisenberg]

Thanks for suggestions. I'll keep trying some different things and report back if I am successful. Thanks.