Preview access report without printing

B Blyden
B Blyden used Ask the Experts™
on
I am using the code below on a userform in Excel to run a report stored in access database. It currently prints the report immediately. I am trying to have the report just display without printing. I would prefer that the report opens in excel and the user can then decide to print or discard.

Right now if the user runs the report it locks the access dba until the print job is complete. I need to prevent that from happening since we have multiple writing to the database from the excel userform. Any suggestions or perhaps a better method?


Private Sub CommandButton2_Click()
       Dim objAcc As Object
       Dim strWhere As String
       strWhere = "IDnNo = " & Me.txtBrNo & ""
       Set objAcc = CreateObject("Access.Application")
       objAcc.OpenCurrentDatabase FilePath:=TARGET_DB
       objAcc.DoCmd.OpenReport "rptClasss", acViewPreview, strWhere
       On Error GoTo ErrHandler
       objAcc.RunCommand 340 
       On Error GoTo 0
       objAcc.Quit
       Exit Sub
ErrHandler:
       If Err = 2501 Then 
           ' Ignore
       Else
           MsgBox Err.Description, vbExclamation
       End If
       Resume Next

End Sub

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
NorieAnalyst Assistant

Commented:
Try replacing acViewPreview with the value 2, or add this at the top of the code.
Const acViewPreview = 2

Open in new window

John TsioumprisSoftware & Systems Engineer

Commented:
How about using Access all the way....its usually the other way around...Access controlling Excel ...not Excel Access...
Anyway

       objAcc.DoCmd.OpenReport "rptClasss", 2, strWhere
John TsioumprisSoftware & Systems Engineer

Commented:
Try this
Private Sub CommandButton2_Click()
On Error GoTo ErrHandler
       Dim objAcc As Object
       Dim strWhere As String
       strWhere = "IDnNo = " & Me.txtBrNo & ""
       Set objAcc = CreateObject("Access.Application")

       objAcc.OpenCurrentDatabase FilePath:=TARGET_DB
       objAcc.Visible = True
       objAcc.DoCmd.OpenReport "rptClasss", 2, strWhere
       Docmd.SelectObject acReport, "rptClasss"
       
       
       On Error GoTo 0
       objAcc.Quit
       Exit Sub
ErrHandler:
       If Err = 2501 Then 
           ' Ignore
       Else
           MsgBox Err.Description, vbExclamation
       End If
       Resume Next

End Sub

Open in new window

Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
John, when I tried your code  I get error "Object doesn't support this property or method"
NorieAnalyst Assistant

Commented:
If you are using late binding and don't have a reference to the appropriate Access Object Library then acViewPreview will not be recognised.

You have 3 options:

1 Change to early binding and add the appropriate reference.

2 Replace acViewPreview with 2

3 Add a constant declaration for acViewPreview and set it's value appropriately, i.e. to 2.

Author

Commented:
Norie… I tried your suggestion and it still displayed the print dialog. Can you explain the early binding?

Thanks
John TsioumprisSoftware & Systems Engineer

Commented:
Clear the error for now....you should be able to see Access popup and show the report.

Author

Commented:
We don't want access to display to the user at all. Some user don't even have access loaded on there PC. That is another reason why I am not doing it all in Access. Would prefer it just display the results in a separate spreadsheet if that would be easier.
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:
You can't "run" (i.e. Print) a report in Access without opening Access, and you can't preview an Access report without opening Access.

Some user don't even have access loaded on there PC
Then your code will fail, unless you're trapping for that error.
John TsioumprisSoftware & Systems Engineer

Commented:
So to get a rough idea ...you want to have a report previewed in Access but without Access and preferably to a separate spreadsheet.....lets take a step back....how about cooking spaghetti without pasta...and on the end wanting to have the plate filled on the table...its irrelevant ..but the same.
Without at least Access Runtime there isn't an easy way to control an Access Application to somehow prepare you a Report and have it on a spreadsheet.
The only thing you can do besides Runtime is to hide just about everything from Access but the Report preview window

Author

Commented:
Okay, I get it. Cant be done, so was asking for suggestions. Thanks for the cooking class.
Benjamin LuSAP/ERP Data Player

Commented:
I'd think you try this workaround:
1. Setup one command to export the report as PDF
2.Then, user can view the PDF report which had no connection to DBA

Author

Commented:
Lu..... do you have a sample code that I can use to accomplish the export to pdf?
John TsioumprisSoftware & Systems Engineer

Commented:
The only viable scenario is like this
You have an Access running somewhere .. I suggest a server
On this particular Access there is a timer even running constantly monitoring a folder
Each time a user requests a report they place a file on the mentioned before folder.
The timer event picks the file and from there you have 2 options...either it returns some export of the report in a "form" Excel can handle...e.g text...or it prepares the report as PDF and emails it to the user requesting the report
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:
You can export a report to PDF like this. This link shows how to do that:

https://www.datanumen.com/blogs/export-access-report-pdf-file-via-vba/

However, you still can't do this if your end user doesn't have Access installed. And while you can do as John suggests and automate an instance of Access on a server, remember that Access is not meant for unattended use.
To avoid locking the Access DB while the report is in preview mode, make a copy of the Access file, and use that copy in your VBA code:

' Delete the [u]copy [/u]of the file, if it exists:
Kill "C:\DatabasePath\AccessFileName_Copy.accdb"
' Make copy of the Access file:
FileCopy "c:\DatabasePath\AccessFileName.accdb", "C:\DatabasePath\AccessFileName_Copy.accdb"
' Run the code that previews the report, but open the copy of the Access file: 

Open in new window

SAP/ERP Data Player
Commented:
Following is the sample code,
It will output the report as PDF, and open it with PDF reader (if parameter =true):
Dim objAccess As New Access.Application
Dim strDb As String, strReport
Dim strFile As String
strDb = "C:\Users\lu.b\Documents\deliveryPlan2019.mdb"
strFile = "C:\my1.pdf"
strReport = "ISTOPKA4"
'Set objAccess = New Access.Application
objAccess.Visible = False
With objAccess
.OpenCurrentDatabase (strDb)
.Visible = False
.DoCmd.OutputTo acOutputReport, strReport, acFormatPDF, strFile, True
.DoCmd.Quit
End With
Set objAccess = Nothing

Author

Commented:
Thanks you guys. I used LU's solution along with ms3930 to prevent the database from being locked while the report is being ran.
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:
Just remember this isn't going to help you if the user doesn't have Access installed.
John TsioumprisSoftware & Systems Engineer

Commented:
Given the fact that the Author accepted a solution based on the presence of Access probably he will resolve this first...or...

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial