Solved

Launching Crystal Reports from Access

Posted on 2014-02-05
13
1,306 Views
Last Modified: 2014-02-16
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.)
0
Comment
Question by:rspahitz
  • 6
  • 5
  • 2
13 Comments
 
LVL 84
ID: 39836608
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.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 39836885
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
http://www.kenhamady.com/bookmarks.html

He also reviews the tools annually.  The report viewers review is at
http://kenhamady.com/cru/comparisons/crystal-reports-viewers

The Data Link Viewer from Millet Software is one that can be called from the command line.
It is relatively inexpensive.
http://www.milletsoftware.com/DataLink_Viewer.htm

mlmcc
0
 
LVL 22

Author Comment

by:rspahitz
ID: 39837589
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?)
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 39837711
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?

mlmcc
0
 
LVL 22

Author Comment

by:rspahitz
ID: 39838049
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)
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 39838881
Crystal doesn't have the version number in the application name.

WHen you open a report
CLick HELP --> ABOUT CRYSTAL

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.

mlmcc
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 22

Author Comment

by:rspahitz
ID: 39845700
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.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 39846259
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.

mlmcc
0
 
LVL 22

Author Comment

by:rspahitz
ID: 39853958
"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.
0
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 100 total points
ID: 39854221
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.
0
 
LVL 100

Accepted Solution

by:
mlmcc earned 400 total points
ID: 39854387
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?

mlmcc
0
 
LVL 22

Author Comment

by:rspahitz
ID: 39854625
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.
Thanks!

I'll report back after my Saturday meeting with her.
0
 
LVL 22

Author Closing Comment

by:rspahitz
ID: 39863081
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.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now