Print report from VB

[gmit]

Can anyone tell me how to print a report in Access Database from VB.
something like:

dim dbStock as database
set dbstock = Opendatabase "C:\stock.mdb"
dbStock.DoCmd.OpenReport "rptStock", acViewNormal

?????
The DoCmd isn't available, is it possible to print a report this way?

 

[charityg]

dim dbStock as New Access.Application
dbStock.OpenCurrentDatabase "C:\stock.mdb"
dbStock.DoCmd.OpenReport "rptStock", acViewNormal
dbStock.Visible=true
Set dbStock = Nothing

 

[gmit]

Thanks for the reply I have tried adding .Application and the open database method but because I already have a connection to the database using data control (Connection is made when form loads) eg:

Set dbStock = OpenDatabase(gDataBaseName)
Set rsStock= dbStock.OpenRecordset("Stock", dbOpenDynaset)
Set datStock.Recordset = rsStock

I get an error message "Cannot open Database locked by user".

 

[charityg]

Dim dbAccess As New Access.Application
dbAccess.OpenCurrentDatabase ("C:\stock.mdb")
dbAccess.DoCmd.OpenReport "rptStock", acViewNormal
dbAccess.Visible = True
Set dbAccess = Nothing

This worked for me. Change the name of the access application object to dbAccess.

 

[gmit]

I get an error when trying to open database, because the database is already open. I have a form with a data control on it and text boxes bound to fields in the database. When the form loads the properties of the data control is set and the fields displayed in the bound text boxes. There are navigation buttons at the bottom of the form, one is for printing the report. If I place the code in the print button OpenCurrentDatabase generates an error because I am trying to open a database twice.

Form load
Set dbStock = OpenDatabase(gDataBaseName)
Set rsStock= dbStock.OpenRecordset("Stock", dbOpenDynaset)
Set datStock.Recordset = rsStock

Print code
dim dbStock as New Access.Application
dbStock.OpenCurrentDatabase "C:\stock.mdb"
dbStock.DoCmd.OpenReport "rptStock", acViewNormal
dbStock.Visible=true
Set dbStock = Nothing

As you can see the OpenDatabase and OpenCurrentDatabase is called twice.

 

[charityg]

Off the top of my head, a quick fix would be to create a "front-end" database that contains the report object with links to the tables in the db that you are using as your datasource. Then use the front-end database path in the opencurrentdatabase function to print the report.

The code I posted last that opened the database as a datasource and as an object worked for me, but I didn't have any field binding to the datasource, so that might be the difference.

Hope this helps!

 

[gmit]

I have found a way around the problem, cheated a bit but the results are what I want. I closed the connection first then opened it again after the code for the printing had completed. There's a slight flicker in the program but I get my printout. Not sure if this is the correct way to do it but it works. Thanks again for your help.