Opening Access Report via VB

[Guest_imported]

Hi folks,

I need to display or print (at least this option) an MS-Access report within Visual Basic (5 but could be newer version).
I know how to create an Access object, the point is the computers that will run the VB application don't have Access pre-installed.

Does anyone know if it's possible, and in this case how to do it (maybe including a few files will help, but which ones ?)

Please reply at ytse@chez.com
Thanks

 

[jasek78]

You need to at least include the Access Run-Time files, I believe you get those with the Office Professional version.

You can then declare your Access DB object, and use DoCmd to print the report. (You can't preview it, unfortunately... I've been unable to find a way to for the past 3 months.)

Hope this helps.

jason

 

[BigDaddy]

Part I of II (Easy)

You can preview a report with the same DoCmd command that you use to print your report.

With appAccess
' Make sure the application is visible.
.Visible = TRUE
'Open the report using the constant you wish acViewDesign
.DoCmd.OpenReport strReportName, acViewDesign, , strFilter
End With

The above constant acViewDesign has other options
acViewDesign = Design
acViewNormal = Print
acViewPreview = Preview

 

[BigDaddy]

Part II of II (More difficult, but somewhat documented)

'***************************
'* Calling sub, throughout the following bit of code you
'* will see how to call the functions listed below in
'* modAccessFunction
'*
'* The following is partial, just and example.
'***************************

If bolIsDBOpen Then
If bolIsPreviewOn Then
bolIsRptCopied = CopyBaseReportToTemp(strOldRptName, strNewRptName)
lblStatus.Caption = "Base Report Copied"
End If
pbrPreview = (intCurrentStep / intSteps) * pbrPreview.Max
intCurrentStep = intCurrentStep + 1
DoEvents

If bolIsRptCopied And bolIsPreviewOn Then
bolReturn = ChangeReportFilter(strNewRptName, CreateRptFilter())
lblStatus.Caption = "Filter Changed in Report Copy"
End If

pbrPreview = (intCurrentStep / intSteps) * pbrPreview.Max
intCurrentStep = intCurrentStep + 1
DoEvents

If bolIsRptCopied And bolIsPreviewOn Then
bolReturn = ExportReportToFile(strNewRptName, SNAPSHOT)
lblStatus.Caption = "Report Exported to Snap File"
End If

pbrPreview = (intCurrentStep / intSteps) * pbrPreview.Max
intCurrentStep = intCurrentStep + 1
DoEvents

If bolIsRptCopied Then
DeleteReport strNewRptName
lblStatus.Caption = "Report Copy Deleted"
End If



'***************************
'* Everything below here is for modAccess Functions
'***************************

Option Explicit

Enum opgRptType
XLS = 1
RTF = 2
SNAPSHOT = 3
HTML = 4
End Enum

Public appAccess As Access.Application
Public dbCurrent As Database

Sub CloseDB()
If Not appAccess Is Nothing Then
If Not dbCurrent Is Nothing Then
appAccess.CloseCurrentDatabase
End If
appAccess.Quit
Set dbCurrent = Nothing
Set appAccess = Nothing
End If
End Sub

Function OpenReportDB(strDBPath As String) As Boolean
On Error GoTo ErrHandler
' Return reference to Microsoft Access Application object.
Set appAccess = New Access.Application
' Open database in Microsoft Access.
appAccess.OpenCurrentDatabase strDBPath

Set dbCurrent = appAccess.CurrentDb

' Return True if not error
OpenReportDB = True
Exit Function

ErrHandler:
Set dbCurrent = Nothing
If Not appAccess Is Nothing Then
appAccess.Quit
End If
Set appAccess = Nothing
' Return False if error
OpenReportDB = False
End Function

Public Function OpenDatabase(Optional strFileToOpen As String, _
Optional bolIsVisible As Boolean = True) As Boolean
On Error GoTo ErrHandler
If appAccess Is Nothing Then
Set appAccess = New Access.Application

If strFileToOpen = "" Then
strFileToOpen = OpenMDBBrowser()
Debug.Print strFileToOpen
Else
End If
appAccess.OpenCurrentDatabase strFileToOpen, False

appAccess.Visible = bolIsVisible
End If
OpenDatabase = True
Exit Function
ErrHandler:
OpenDatabase = False
End Function

Public Sub CloseDatabase()
If Not (appAccess Is Nothing) Then
If Not (dbCurrent Is Nothing) Then
appAccess.CloseCurrentDatabase
End If
End If
appAccess.Quit
Set appAccess = Nothing
End Sub

Public Function ChangeReportFilter(strReportName As String, strFilter As String) As Boolean
Dim rptToChange As Report

If Not (appAccess Is Nothing) Then
If AccessObjectExists("Report", strReportName) Then
appAccess.DoCmd.OpenReport strReportName, acViewDesign, , strFilter
appAccess.Reports(strReportName).Filter = strFilter
appAccess.Reports(strReportName).FilterOn = True
appAccess.DoCmd.Close acReport, strReportName, acSaveYes
ChangeReportFilter = True
Else
MsgBox strReportName & " could not be found. Please check your database for the report.", vbExclamation & vbOKOnly, "ChangeReportFilter Failure"
ChangeReportFilter = False
End If
Else
ChangeReportFilter = False
End If

Set rptToChange = Nothing
End Function

Public Function ExportReportToFile(strReportName As String, Optional lngRptType As opgRptType, Optional bolOpen As Boolean = False) As Boolean
Dim strReportPath As String
strReportPath = App.Path & "\"
If Not (appAccess Is Nothing) Then
If AccessObjectExists("Report", strReportName) Then
With appAccess
' Output or display in specified format.
Select Case lngRptType
Case XLS
.DoCmd.OutputTo acOutputReport, strReportName, acFormatXLS, _
strReportPath & strReportName & ".xls", bolOpen
Case RTF
.DoCmd.OutputTo acOutputReport, strReportName, acFormatRTF, _
strReportPath & strReportName & ".rtf", bolOpen
Case SNAPSHOT
' Snapshot Viewer must be installed to view snapshot
' output.
.DoCmd.OutputTo acOutputReport, strReportName, acFormatSNP, _
strReportPath & strReportName & ".snp", bolOpen
Case HTML
.DoCmd.OutputTo acOutputReport, strReportName, acFormatHTML, _
strReportPath & strReportName & ".htm", bolOpen
Case Else
.Visible = True
.DoCmd.OpenReport strReportName, acViewPreview
End Select
End With

ExportReportToFile = True
Else
MsgBox strReportName & " could not be found. Please check your database for the report.", vbExclamation & vbOKOnly, "ExportReportToSnap Failure"
ExportReportToFile = False
End If
Else
ExportReportToFile = False

ƜXƜ

 

[BigDaddy]

Oops Part II of II cont

Public Function ExportReportToFile(strReportName As String, Optional lngRptType As opgRptType, Optional bolOpen As Boolean = False) As Boolean
Dim strReportPath As String
strReportPath = App.Path & "\"
If Not (appAccess Is Nothing) Then
If AccessObjectExists("Report", strReportName) Then
With appAccess
' Output or display in specified format.
Select Case lngRptType
Case XLS
.DoCmd.OutputTo acOutputReport, strReportName, acFormatXLS, _
strReportPath & strReportName & ".xls", bolOpen
Case RTF
.DoCmd.OutputTo acOutputReport, strReportName, acFormatRTF, _
strReportPath & strReportName & ".rtf", bolOpen
Case SNAPSHOT
' Snapshot Viewer must be installed to view snapshot
' output.
.DoCmd.OutputTo acOutputReport, strReportName, acFormatSNP, _
strReportPath & strReportName & ".snp", bolOpen
Case HTML
.DoCmd.OutputTo acOutputReport, strReportName, acFormatHTML, _
strReportPath & strReportName & ".htm", bolOpen
Case Else
.Visible = True
.DoCmd.OpenReport strReportName, acViewPreview
End Select
End With

ExportReportToFile = True
Else
MsgBox strReportName & " could not be found. Please check your database for the report.", vbExclamation & vbOKOnly, "ExportReportToSnap Failure"
ExportReportToFile = False
End If
Else
ExportReportToFile = False
End If
End Function

Public Function CopyBaseReportToTemp(strOldReportName As String, strNewReportName As String) As Boolean
If Not (appAccess Is Nothing) Then
If AccessObjectExists("Report", strOldReportName) Then
appAccess.DoCmd.CopyObject , strNewReportName, acReport, strOldReportName
CopyBaseReportToTemp = True
Else
MsgBox strOldReportName & " could not be found. Please check your database for the report.", vbExclamation & vbOKOnly, "CopyBaseReportToTemp Failure"
CopyBaseReportToTemp = False
End If
Else
CopyBaseReportToTemp = False
End If
End Function

Public Function DeleteReport(strReportName As String) As Boolean
If Not (appAccess Is Nothing) Then
If AccessObjectExists("Report", strReportName) Then
appAccess.DoCmd.DeleteObject acReport, strReportName
DeleteReport = True
Else
MsgBox strReportName & " could not be found. Please check your database for the report.", vbExclamation & vbOKOnly, "DeleteReport Failure"
DeleteReport = False
End If
Else
DeleteReport = False
End If
End Function

Public Function OpenMDBBrowser() As String
Dim FileBrowser As Object

Set FileBrowser = CreateObject("MSComDlg.CommonDialog&quot

FileBrowser.ShowOpen
OpenMDBBrowser = FileBrowser.FileName
Set FileBrowser = Nothing
End Function

Public Function AccessObjectExists(strObjectType As String, strObjectName) As Boolean
Dim i As Integer
Select Case strObjectType
Case "Table"
AccessObjectExists = False
Case "Form"
AccessObjectExists = False
Case "Report"
appAccess.RefreshDatabaseWindow
For i = 0 To appAccess.CurrentDb.Containers!Reports.Documents.Count - 1
If UCase$(strObjectName) = UCase$(appAccess.CurrentDb.Containers!Reports.Documents(i).Name) Then
AccessObjectExists = True
Exit For
Else
Debug.Print appAccess.CurrentDb.Containers!Reports.Documents(i).Name
End If
Next
Case Else
MsgBox "Object Types: Table, Query, Form, Report.", vbInformation & vbOKOnly, "Valid Object Types"
AccessObjectExists = False
End Select
End Function

Public Function PrintReport(strReportName As String, Optional strFilter As String) As Boolean
If Not (appAccess Is Nothing) Then
If AccessObjectExists("Report", strReportName) Then
With appAccess
' Print the Report acViewNormal-Send to Printer
.DoCmd.OpenReport strReportName, acViewNormal, , strFilter
End With

PrintReport = True
Else
MsgBox strReportName & " could not be found. Please check your database for the report.", vbExclamation & vbOKOnly, "ExportReportToSnap Failure"
PrintReport = False
End If
Else
PrintReport = False
End If
End Function

Public Function GetRandomRptName() As String
Dim strUserName As String

' Initialize the Random Number Generator
Randomize
GetRandomRptName = UserName() & Trim$(CStr(Abs(CLng(Rnd * 32000))))
End Function

 

[Guest_imported]

Jason (jasek78),

two things :
- about viewing / printing report, try this site :

http://www.azan.net/sarosh

and read about Access automation (there's a small piece of code and at first sight I think it's able to print OR display a report, depending on parameters you give to the function)

- for my topic, do you have an idea of how I can include those Access RunTime Files ?

Thanks

 

[TheInsider]

What you are essentially doing is coding VB to open an Access database and then open a report within that database. You cannot do this without some form of Microsoft Access installed on the application computer.

Microsoft created a "run-time" version of Access for distribution to end users who do not have a copy of Access available to them. This "run-time" version of Access is included with the Developer Edition of Microsoft Office and only those developers who have purchased the Developer Edition may distribute this "run-time". The Developer Edition is rare and expensive (around $1500).

The other choice that you have is to use VideoSoft's VSReports (around $250), which converts a Microsoft Access report to a stand-alone object, which you can basically use like a Crystal Report. I once purchased this product, however, it was returned due to its large amount of limitations. If you have very simple reports then you can use this tool. If you have reports that have queries with sub-queries or sub-selects which require parameters -- forget it.

Crystal Reports is your best bet, but it isn't cheap either. Basically you will need to lay out some cash or go with VB's built-in data report.

 

[petermeachem]

I didn't think the dev option was that expensive. It doesn't seem to work as well as the real one. I got my money back on VSreports too. Awful thing. Good idea but was dreadfully slow and full of bugs. I had to get version 471 (yes 471) as the one I bought couldn't add a column and get a total. Not impressed with Crystal Reports either actually. Peter Meachem
peter@accuflight.com

 

[jasek78]

ytse -

I had a variable going out of scope on me. Sorry about misleading you. It looks possible via automation, however I've been trying to use a dataenvironment to retrieve the reports' structure into a datareport.

jason

 

[Guest_imported]

Ok guys, thanks for your help, even though I still don't know how I am going to do.
The users (clients) of the application did want Access so that they can include some graphical things (arrows...) and create their own reports.
I guess I just have to see what can be done with latest versions of Crystal Report (as the limited one furnished with VB is really limited and give them courses.

 

[jasek78]

Currently, the users of my program use the VB program to input the data into the database, and the managers use Access to run the reports. I created the reports, and made a 'Switchboard' screen that loads on startup.

Maybe you can use Shell() to open Access and the MDB file for them, then let them navigate through access themselves? (Assuming that they have a decent knowledge of Access)

You may even want to split the database, although that will make the reports and queries run a little bit slower (more so if the users are creating new reports).

Jason

 

[petermeachem]

You can only run an Access report on a particular machine IF you have Access installed on said machine. Either full version or run time. Peter Meachem
peter@accuflight.com