Launch access via VB or C++ app to generate report

[shaughton]

Question: Can Microsoft Access be launched from a VB or VC++ app just to generate a report?

Background: Users who are not Access uses want an easier way to generate predefined reports to view and print.

The database is a large patient database where doctors, nurses and techs view, create and generate their own reports using Access.

The non-computer savy users want a small app that lists a set of predefined reports created with access, asks the appropriate questions based on that report, then launches Access to allow the user to view and print that report. Note: Microsoft Access sold with product.

If my understanding of access databases are correct, the reports are embedded in the .mdb file, so my question put another way is; Can a VB or C++ app read the reports section of a .mdb file generate a listbox with those report options and then based on the users selection launch access to view and print the report?

Thanks for listening

 

[danvlas]

Hmmm...looks like scratching the left ear with the right index...

If users have Access installed, this would be much easier to do this stuff directly from Access.

But I guess it can be done.
In VB you would have to set a reference to DAO or ADO (ADO is prefferable).

Open a connection to the database and inspect the MSysObjects table for objects of type -32764. This will give you all report names in that database.

List them in a listbox in the VB form.
User would select one or more reports then click an OK button, which would:

Shell "msaccess.exe " & "C:\Path\File.mdb " & "/cmd StringHavingAllReportNames"

Now, the Access database would have a startup form with a Timer event. In this event, the argument of the command line (StringHavingAllReportNames) can be retrieved through the Command() function.

Parse the string to get the list of reports to open and
DoCmd.OpenReport
for all reports listed in the string.


But I repeat: it's easier and probably better to list the reports directly from Access.

HTH




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[RickSpr]

I'm with Dan--you're better off using Access directly. You can create some forms and code that run at startup to lead the user by the hand. This is better than using VB or VB++, because with the latter you have to launch Access as a separate process, and that means that if any problems occur in Access, your front end won't be able to recover gracefully. With the forms and code written as an Access front end, you can trap errors and give the user some help and instructions.

Since the same database is used by nontechnical users and technical users both, you need to have some way for the technical users to escape from the front end. You could do that either by having a command button on the forms, by using an Access command line parameter to alter the startup, or (if users have to log on) by including a table that identifies the technical users. Or, you could simply start the front end with a MsgBox that asks whether they want the raw Access design interface.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[jiqjaq]

If you define the form to select the reports in Access, then you can call the form directly from VB. Put an Exit button on the form, and close Access when the button is clicked. This will put the user back in the VB program.

Inside the VB program, you can do a CreateObject or define a variable referencing Access using automation. Once the variable is defined and Access is open, you can use all the Access methods using this variable. Access will open in another window which will receive the focus. If the user clicks on the VB window while Access is still open, you may want to force Access to shut down via automation.