Launching Crystal Reports from Access

I'm helping out on a project that is being phased out and moved to Access.  It uses SQL Server so all of the Access tables connect to a remove (internal) server.
Part of the project requires launching various Crystal Reports.

I tried direct launch (shell "path.rpt") but it only launches Crystal in design mode.
I also tried "connecting" to Crystal in various ways but the best I could get was an error that said "invalid login".

Anyone have ideas about how to launch CR (maybe version 8.5...not sure since I don't have immediate access to the project) using a command-line switch (e.g. CR -r my.rpt)
Or using VBA to launch it (which references do I need)?

I don't mind building a separate viewer window, but I'd prefer to just launch CR.  Users will have access to the network drive where the reports are located and should be able to view it using a shared user/pswd combination (which could be hard-coded as needed.)
LVL 22
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Will your users all have Crystal Reports? That is generally not the way an environment like that works. Instead, normally they'd have some sort of "viewer", and would see the reports there. That "viewer" can be many different things, and there are free ones out there.

CR used to have an activeX control that could be used in Access to some degree, but I believe that went away several versions back.
Crystal cannot  be launched from the command line.

Do you have the full version of Crystal the reports were developed with?
If so, you may be able to build the capability into an Access application.  I know for sure it must be CR XI or before to be able to do this.

There are several 3rd-party tools you could use for this.  Some of them are callable from the command line

Ken Hamady has an excellent list of the tools

He also reviews the tools annually.  The report viewers review is at

The Data Link Viewer from Millet Software is one that can be called from the command line.
It is relatively inexpensive.

rspahitzAuthor Commented:
I think that all have access to CR through the network (launching a shared instance?)

A viewer would be fine.

I saw a bunch of references to the ActiveX control and almost got it to work, but the login problem occurred (with no apparently way to supply that information--maybe it was trying to authenticate but had the wrong credentials.)

I can ask if they want a 3rd party control but I suspect they'd rather just migrate everything to Access unless the control is a cheap 1-time purchase that require virtually no maintenance or learning curve.

Since we are able to edit the reports in design view, I suspect we have access to migrate the capabilities (is there a tool for that so I don't have to write it?)
The Five Tenets of the Most Secure Backup

Data loss can hit a business in any number of ways. In reality, companies should expect to lose data at some point. The challenge is having a plan to recover from such an event.

There is no tool to migrate from Crystal to Access.  You will also find that the 2 reporting tools though similar in many ways have significant differences in how they do somethings.

CR is controlled by licenses and is not except through specific licensing supposed to be shared.  Licensing is version specific but most of the newer versions use named licenses or machine licenses.  Named license - a specific user can use r Crystal on any machine but nobody else can use his copy.  Concurrent - allows several people to use the copy but only one at a time.  Typically Crystal when purchased comes with 5 named-user licenses so they can be assigned to 5 users.

Have you determined the version of Crystal you are using?

rspahitzAuthor Commented:
The person I am working with is out until Monday so I won't be able to get the version until then, but I think we were working with CR9.exe, if that indicates the version.

As for licensing, I don't know how they handle it but they are a very large org so may have special licensing agreement, such as 500 licenses with the ability to have any one access as needed...if they offer something like that, or maybe the all have it installed on their machines but access the reports on the network.

Regardless, at least part of the problem is pulling the report files off the network to generate the reports on the local machine,
If we assume that all is local, maybe that would work across the network (which they call the V drive for the reports)
Crystal doesn't have the version number in the application name.

WHen you open a report

You will see the version and edition.
Edition will be  Standard, Professional, Developer, or Advanced.

You need Developer or Advanced to be able to use it from MS Access.

rspahitzAuthor Commented:
Just checking in....

"Do you have the full version of Crystal the reports were developed with?
If so, you may be able to build the capability into an Access application.  I know for sure it must be CR XI or before to be able to do this."

At this point, I am assuming that the developer version is installed.
If I "build the capability into an Access application" will that be impacted by the non-dev licensed users? (I assume it just requires a login.)

With this assumption, how would I proceed? Just add the ActiveX control? Because I tried that and it failed to log in (and never asked for any login info.
Crystal itself in general doesn't require a login.  The login is for the database.

I don't know of any examples for the code for calling Crystal from MS Access.

I will look again or I may even be able to develop it myself.

rspahitzAuthor Commented:
"We are using CR 9 now."

So it seems that these are the options:

* launch from a command window and it will only be in design mode
-- unacceptable

* add an ActiveX window in Access to allow the report to show there (and feed it appropriate parameters)
-- may not work on version 9

* connect to the CR server (or whatever it is) using a reference in VBA
-- should work if I can figure out which reference file to use.

* convert all reports to Access reports
-- there are too many, some are too complicated, and some get launched through a DB stored procedure, which seems to make it impractical to use this option

Any other choices? or any other suggestions?

When I tried the ActiveX, I think it failed because it couldn't get proper credentials to get in (but maybe that was a server problem rather than a CR problem.)

I'll be back working on it Saturday and will see if anything else works.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
The ActiveX doesn't use the server, I don't believe. The ActiveX control generally just loads a local report (a .rpt file) that is filled from your database. Note there are several different ways to fill a Crystal report, from .ttx files to database connections to datasets.
Do you have the Developer or Advanced edition of CR9?
If not you don't have the dlls required to call reports from Access.
Have you considered using one of the 3rd-party tools I linked to?


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rspahitzAuthor Commented:
I didn't get word back about whether it's developer, but I suspect it is.
Do you know which dll (or Reference name) to add?

As for the 3rd party tools, I'll submit that to the person in charge to see if she would consider going that way.

I'll report back after my Saturday meeting with her.
rspahitzAuthor Commented:
Thanks for all the help.  It was a struggle and I'm not sure how much the above solved it, but I'm sure it got me in the right direction.

The answer ended up being that I had to add a bunch of DECLARATIONS at the top of the VBA file to load dll functions to connect, load, open and show the reports.  This allowed it to connect to the network with the password and launch the Viewer in a separate window for viewing.

Thanks for the guidance.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.