Run an Access report from VB 2

[Ham]

I have a VB program working with an Access data base. I also have some reports written in the databae. Now when I want to generate a report, I have to close the program, open the db, run the report, close the db and reopen the program. Is there anyway I can kick off the Access report from within my VB program? This would make life so much more pleasant.

Thanks. -- Ham

 

[MikeCox]

Try playing with this code:

NOTE: be sure to add reference to the Microsoft Access X.0 Object Library (Project...References...)

Dim MSAccess As Access.Application
Set MSAccess = CreateObject("Access.Application&quot
MSAccess.OpenCurrentDatabase "MyDBFileName"
MSAccess.DoCmd.OpenReport (enter your arguments here)

Hope this helps!

-Mike

 

[Guest_imported]

There is also a good article on this on the Microsoft site, QF210132. I have used this as a base to print out queries and reports on a daily basis.

 

[Ham]

Mike - thanks for the help. It looks straight forward - I will try it. I'm already opening that db as follows:

Dim db as Database
Set db=DBEngine(0).OpenDatabase(dbpath)

and defining and opening various recordsets in that db. Is this going to be a problem? If I open it via the CreateObject method, can I do all the record-playing I'm doing now? Can I open the db as I'm doing now and also use the CreateObject approach when I want to do the report? I see lots of fun playing ahead. Thanks again for the help. -- Ham

 

[Ham]

Walt - this is obviously not starting out to be one of my better days. I can't find article QF210132. Is that the correct number? Am I looking in the right place? I went to the Microsoft Support Knowledge Base site and drew a blank. I would like to see that article. Thanks. -- Ham

 

[Guest_imported]

Sorry about that, there's an F in there for some reason. It should be Q210132, here is the link

http://support.microsoft.com/support/kb/articles/Q210/1/32.asp

 

[MikeCox]

You're using the database engine to open the database (Set db = DBEngine(0).OpenDatabase(dbpath), instead of using Access to open it. You shouldn't have to change any of your existing code, that's perfectly legal to do that.

Using the CreateObject command I showed you is creating an instance of Access, which you can use to run your report, but you'll still have to tell Access which database has the report(s) you want to run (OpenCurrentDatabase). You may play with what intelli-sense shows you about the DBEngine methods and events (or use the object browser), and see if it has something in there that looks like it may run an Access report (I didn't see any). Meanwhile, I don't know of any reason why you can't use either approach simultaneously.

 

[Ham]

Walt - I got the article. Thank you very much. -- Ham

 

[Ham]

Mike I set up a simple VB program and can print out MSAccess reports. The next big step will be to incorporate this into my real program and watch what happens. One problem (maybe) is that I can't Preview the report - I can only print it. When I use acViewPreview nothing happens. When I use acViewNormal, the report prints. Is there something else I have to do to be able to preview the report?

Many thanks for your help. -- Ham

 

[MikeCox]

I tried it, and I could tell something was happening (since my report displays an input box for criteria), but there was no other indication that anything was happening until I checked my task bar, which had 3 or 4 instances of Access (oops ;-)) Those instances had everything open (including the report preview in a very small form). I imagine there must be a way to only open the report preview, but I'm not sure what it is. Anyone else have any ideas?

 

[VBVines]

I have a vb app that opens up a report that is generated by two text box entries. I also could not preview the report so I made a seperate form in the vb app that had a grid and a ado data control. For the source of the data control I used the same sql query that the report is based on so at least you can see the values in the fields. aspvbwannab

 

[dotnetprogrammer]

On July 24th, Mike Cox Responded:
Dim MSAccess As Access.Application
Set MSAccess = CreateObject("Access.Application&quot
MSAccess.OpenCurrentDatabase "MyDBFileName"
MSAccess.DoCmd.OpenReport (enter your arguments here)

I wonder, how to pass a password parameter to open a password protected Access Database using above code in my VB?
fred