Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 237
  • Last Modified:

Lanch and Export a Crystal 2008 Report from MSAccess 2007

I want to launch a Crystal 2008 report from a VBA button on a form of an Access 2007 database. This report is to use tables held within this MS Access database. No prompts are contained in the Crystal report.
I am also hoping to be able to automatically export the report’s results to an Adobe pdf file in a specific location as part of the same button press.

Is this possible?
0
JayceW
Asked:
JayceW
  • 5
  • 3
  • 2
  • +2
4 Solutions
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
AFAIK, Crystal 2008 does not include the capability to directly view a report in a COM environment. Will all users have a full copy of CR2008 on their machines? If so, you might be able to launch it using Shell.
0
 
JayceWAuthor Commented:
Thanks for the response.  That exlpains why I was having so much trouble with this

Yes the user(s) will have Crystal reports installed so if you could advise on the shell option that would be appreciated.

Thanks
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Assuming that the file extension for the Crystal Report is properly associated with crystal reports, you can just do this:

Shell "Full path to your .rpt file"

this will launch whatever application is assocation with the .rpt file, and will show the report in that interface.
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
mlmccCommented:
Users will probably have to it the preview icon or F5 to refresh the data.

mlmcc
0
 
JayceWAuthor Commented:
Sadly when I attempt:

Sub CrystalreportCall()
Dim retval
retval = Shell("C:\temp\AgedProblemTickets.rpt", vbMaximizedFocus)
End Sub

Am I missing something obvious as I keep getting a file not found error. If I copy the path into a run command into a run prompt it works perfectly.


Am I missing something obvious?
0
 
mlmccCommented:
Is the database local or on a server?

If it is on the server you may actually be running from the server and the C: is the server drive.

mlmcc
0
 
JayceWAuthor Commented:
Its a local file, hence the path works when I  copy it into the run command.

Is there maybe a reference option that I am missing? Do I need to make the sub a global one?

Thanks
0
 
Ido MilletProfessor of MIS at Penn State Erie and Owner, Millet SoftwareCommented:
Since you want to also export, at least one of the 3rd-party Crystal Reports Desktop Scheduling tools listed at: http://www.kenhamady.com/bookmarks.html provides that functionality.  It allows you to specify (using vba in your case) the report to process, parameter values, export file path & name, export format, etc.

One other advantage is that using that approach, you would no longer need Crystal installed on that machine.  All you need is the .rpt file.
0
 
James0628Commented:
One obvious thing to try with the Shell function would be to add the CR EXE in front of the file name.  Something like:

retval = Shell("C:\Program Files\whatever\cr.exe C:\temp\AgedProblemTickets.rpt", vbMaximizedFocus)

 I have no idea if it would actually work.  It just seems like an obvious thing to try.  Of course you'd replace the path and EXE name with the ones from your system.  And it seems like you'd need some quotes around the path to the EXE, since the path contains spaces.  Maybe add them using something like Chr, or just some escaped quotes.  I saw an example online that was similar to that (an EXE name and then a data file name) and it didn't seem to have any quotes around the path, but I don't see how it could work without them.

 James
0
 
JayceWAuthor Commented:
Thanks for all the support
0
 
mlmccCommented:
What was the final resolution?

Since Crystal doesn't support a command line interface, James comment wouldn't work to open the report.  It might open Crystal but the report in question wouldn't open.

mlmcc
0
 
JayceWAuthor Commented:
mlmcc

As each report is a seperate file the syntax that James has suggested for the shell command works fine and an independent Crystal report can be assigned to each button. You are correct in that the user needs to be reminded to refresh the report but aside from that it works ok.

Thanks

0
 
James0628Commented:
 mlmcc,

 Maybe I'm wrong, but I wouldn't really call it a full blown command line interface.  You're just starting the application and passing it a file name from the command line.  It's a pretty commonly supported convention, although, to be honest, I hadn't actually tried it with CR.  But I just did and it worked fine with CR 10.  I copied a report file into the CR directory and opened a command window there (so that I didn't have to type in the path for the EXE or RPT file) and just entered "crw32 file.rpt" (without the quotes) and CR started and opened the specified report.


 JayceW,

 Glad I could help.

 James
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

  • 5
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now