Can anyone shed some light how to call an access report(2007) from MFC application? I have a form with a button and when clicked it should display the report.
I tried this Code Project suggestion it shows the report like a flash screen and then disappears.
any suggestions?
I help maintain an MFC application that uses Access 2003/2007 for its reporting. It's a system we're trying to change - Access is not very flexible and our "hosting" of Access inside MFC is a bit haphazard.
I wasn't a first party to the design of the hosting architecture and why things were implemented in certain ways, but here's a short outline of two difficulties we have with Access in our application:
Only a full Access installation registers the Access.Application OLE Automation interface. If you can guarantee that your users will have a full installation of Access then this won't apply to you (CLSIDFromProgID followed by a CoCreateInstance will get you an automation interface), but if you have to get an Access.Application object for the Access Runtime then there's some extra plumbing to be done. You need to start msaccess.exe and mess around with GetActiveObject and Sleep until you get find an instance of the interface. There's KB145707 which shows the two ways to connect, albeit in VBA.
Access is not designed to be embedded in another application. There's no Excel.Sheet equivalent for Access reports. You can see this in your CodeProject sample - it renders a report to an intermediate .html file and then uses an embedded web browser control to display the report.
Our application does The Wrong Thing (TM) here and fiddles with the Access application's window style, parent and position and makes it look like there's a report inside our application but it's just a visual sleight of hand. While there's nothing wrong with having parent/child windows from different processes, in this case it leaves us with us some undesired behaviour.
We use a custom Office Ribbon, and because Access 2007's ribbon is part of the window frame the user can drag Access "out" of our app.
When a process terminates it gives up any "foreground focus" it had. When msaccess.exe closes because the user has closed a report our app ends up at the bottom of the window z-order (due to the way we've parented the Access window).
I think you should look at actual reporting libraries, and compare them to the effort you'll need to go to integrate Access into your application.
Microsoft Report Viewer is a lightweight yet powerful reporting library. It uses .NET, but you can use a proxy application to host the control. From above, it's valid to have parent/child windows from different processes. If you can get your application to C++/CLI, it's even easier with CWinFormsControl.
SAP Crystal Reports is also free, but it's not as lightweight as Report Viewer.
If you absolutely have to use Access, I'd personally steer away from using the automation interfaces and use an AutoExec macro with the /cmd switch to describe which report I want rendered and where to render it to. AutoExec would kick in, render the report and close Access (there's an example of the technique here). Once the output file is created, you can take that and embed it in your application. A couple of suggestions:
If your reports are simple then stick with the .html and web browser example from the CodeProject article.
If your reports have complex formatting you could output the report as a .snp (Snapshot) file and then display it in your application (the Access Snapshot Viewer is embeddable - the PROGID is snpvw.Snapshot Viewer Control.1).
