By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,665 Members | 1,454 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,665 IT Pros & Developers. It's quick & easy.

Opening an Access Report from vb6 pro

P: n/a
Hi Everyone,

I am new to programming and would like to know how to
open an access Report from within vb 6. I am trying to write a program
to organise cross stitch threads. I have found out how to use a database
table
but all I want to do now is to click a command button to display this access
report.

Any suggestions please ?????

Thank you in advance
Jul 17 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Nicola schreef:
Hi Everyone,

I am new to programming and would like to know how to
open an access Report from within vb 6. I am trying to write a program
to organise cross stitch threads. I have found out how to use a database
table
but all I want to do now is to click a command button to display this access
report.

Any suggestions please ?????

Thank you in advance

You'll have to have a variable pointing to the Access application (No
DAO or ADO here, reference access in your project and ask for a new
Access.Application object, or use the createObject function. With that
reference you can open a database and give the command to open the report:

set objDatabaseApplication=new Access.Application
(open a database. without trying, I think it is done with the
SetCurrentDatabase method)
objDatabaseApplication.DoCmd.OpenReport <report name>

Hope this helps
Jul 17 '05 #2

P: n/a
Helpful Links:

ACC: How to Use Automation to Print Microsoft Access Reports:
http://support.microsoft.com/?id=145707
ACC2000: How to Use Automation to Print Microsoft Access Reports
http://support.microsoft.com/?id=210132
ACC2002: How to Use Automation to Print Microsoft Access Reports
http://support.microsoft.com/?id=296586
ACC: Using Microsoft Access as an Automation Server
http://support.microsoft.com/?id=147816
ACC2000: Using Microsoft Access as an Automation Server
http://support.microsoft.com/?id=210111

--
Chris Hanscom - Microsoft MVP (VB)
Veign's Resource Center
http://www.veign.com/vrc_main.asp
--

"Nicola" <bo***@bonzai.co.uk> wrote in message
news:42********@mk-nntp-2.news.uk.tiscali.com...
Hi Everyone,

I am new to programming and would like to know how to
open an access Report from within vb 6. I am trying to write a program
to organise cross stitch threads. I have found out how to use a database
table
but all I want to do now is to click a command button to display this access report.

Any suggestions please ?????

Thank you in advance

Jul 17 '05 #3

P: n/a
The following is a module written by David Ward and it isvery helpfull.

It should solve your predicament. Just read and follow the instructions.

Best of luck.

Robert

Option Explicit

' **************modAccessReports.bas***************
' Yu must reference the access object library
' Copy paste this entire module into a code module
' and save it a a code module template. It can be added
' to any project that requires previewing, printing,
' or emailing of Access reports.
'
' This module is based on
' "ACC: How to Use Automation to Print Microsoft Access Reports"
' Microsoft KB Article ID Q 145707
'
' What I have done is simplify the processes (??),
' and made the code easier to read and use.
' Dave Ward dc****@xtra.co.nz
' ************************************************** *

Declare Function SetForegroundWindow Lib "User32" _
(ByVal hwnd As Long) As Long

Declare Function ShowWindow Lib "User32" _
(ByVal hwnd As Long, ByVal nCmdShow As Long) As Long

Public Const SW_MAXIMIZE = 3 'Show window maximized

Global Const acNormal = 0 ' -- sends to the printer constant
Global Const acDesign = 1 ' -- not available from VB frontend
Global Const acPreview = 2 ' -- preview window constant
Public Sub PrintAccessReport(dbName As String, _
rptName As String, _
Optional rptFilter As Variant, _
Optional rptWhere As Variant)

On Error GoTo PrintAccessReport_Err

Dim objAccess As Object
Set objAccess = GetObject(DBPath)

With objAccess
' open the database
' .OpenCurrentDatabase filepath:=dbName
' send the report to printer.
.DoCmd.OpenReport rptName, 0, rptFilter, rptWhere
DoEvents ' off it goes to the printer
End With

' tidy up
objAccess.Quit
Set objAccess = Nothing
Exit Sub

PrintAccessReport_Err:
MsgBox Error$(), vbInformation, " Access Database Automation Error"
End Sub

Public Sub PreviewAccessReport(dbName As String, _
rptName As String, _
Optional rptFilter As Variant, _
Optional rptWhere As Variant)

On Error GoTo PreviewAccessReport_Err

Dim SIZE As Variant
Dim hwnd As Long
Dim temp As Long
SIZE = SW_MAXIMIZE

Dim objAccess As Object
Set objAccess = CreateObject("Access.Application")

With objAccess
' open the database
.OpenCurrentDatabase dbName, False
' make it visible
.Visible = True
' maximize the db window size
hwnd = objAccess.hWndAccessApp
temp = SetForegroundWindow(hwnd)
temp = ShowWindow(hwnd, SIZE)
' open the report
.DoCmd.OpenReport rptName, 2, rptFilter, rptWhere
' maximize the report window
' inside the db window
.DoCmd.Maximize
End With

PreviewAccessReport_End:
objAccess.Quit
Set objAccess = Nothing
Exit Sub

PreviewAccessReport_Err:
MsgBox Error$(), vbInformation, " Access Database Automation Error"
Resume PreviewAccessReport_End
End Sub

Public Sub EmailAccessReport(dbName As String, _
rptName As String, _
rptFormat As String, _
rptTo As String, _
rptSubject As String, _
Optional rptCc As String, _
Optional rptBcc As String, _
Optional rptMessage As String, _
Optional rptEdit As Boolean, _
Optional rptTemplate As String)

On Error GoTo EmailAccessReport_Err

Dim objAccess As Object
Set objAccess = CreateObject("Access.Application")

With objAccess
' open the database
.OpenCurrentDatabase filepath:=dbName
' email a report
.DoCmd.SendObject acReport, _
rptName, _
rptFormat, _
rptTo, _
rptCc, _
rptBcc, _
rptSubject, _
rptMessage, _
rptEdit, _
rptTemplate

DoEvents

End With
' tidy up
Set objAccess = Nothing

Exit Sub
EmailAccessReport_Err:
MsgBox Error$(), vbInformation, " Access Database Automation Error"
End Sub

Public Sub SaveReportAsWordDoc(dbName As String, _
rptName As String, _
rptPath As String)

On Error GoTo SaveReportAsWordDoc_Err

Dim objAccess As Object
Set objAccess = CreateObject("Access.Application")

With objAccess
' open the database
.OpenCurrentDatabase dbName, False
.DoCmd.OutputTo acReport, rptName, acFormatRTF, rptPath, -1
End With

SaveReportAsWordDoc_End:
Exit Sub

SaveReportAsWordDoc_Err:
MsgBox Error$(), vbInformation, " Access Database Automation Error"
Resume SaveReportAsWordDoc_End
End Sub

Public Sub SaveReportAsHTML(dbName As String, _
rptName As String, _
rptPath As String)

On Error GoTo SaveReportAsHTML_Err

Dim objAccess As Object
Set objAccess = CreateObject("Access.Application")

With objAccess
' open the database
.OpenCurrentDatabase dbName, False
.DoCmd.OutputTo acReport, rptName, "HTML", rptPath, -1
End With

SaveReportAsHTML_End:
Exit Sub

SaveReportAsHTML_Err:
MsgBox Error$(), vbInformation, " Access Database Automation Error"
Resume SaveReportAsHTML_End
End Sub

Public Sub SaveReportAsExcel(dbName As String, _
rptName As String, _
rptPath As String)

On Error GoTo SaveReportAsExcel_Err

Dim objAccess As Object
Set objAccess = CreateObject("Access.Application")

With objAccess
' open the database
.OpenCurrentDatabase dbName, False
.DoCmd.OutputTo acReport, rptName, acFormatXLS, rptPath, -1
End With

SaveReportAsExcel_End:
Exit Sub

SaveReportAsExcel_Err:
MsgBox Error$(), vbInformation, " Access Database Automation Error"
Resume SaveReportAsExcel_End
End Sub
' ********* Form module procedures
' **Preview Report
'Private Sub cmdPreviewReport_Click()
'Dim db As String
'Dim rptName As String
'db = "C:\...\...\Database\MyDB.mdb"
'rptName = "My Report Name"
'
'PreviewAccessReport db, rptName
'End Sub

' **Print Report
'Private Sub PrintReport_Click()
'Dim db As String
'Dim rptName As String
'db = "C:\...\...\Database\MyDB.mdb"
'rptName = "My Report Name"
'
'PrintAccessReport db, rptName
'End Sub

' **Email Report
'Private Sub cmdEmailReport()
'Dim db As String
'Dim rptName As String
'Dim rptFormat As String
'Dim rptTo As String
'Dim rptSubject As String
'Dim rptMessage As String
'
'db = "C:\...\...\Database\MyDB.mdb"
'rptName = "My Report Name"
'rptFormat = "HTML"
'rptTo = "dc****@xtra.co.nz"
'rptSubject = "Subject Title: " & Date
'rptMessage = "Attached is our latest report for your information."
'
'EmailAccessReport db, rptName, rptFormat, rptTo, rptSubject, , , _
' rptMessage, False
'
'End Sub
Public Sub AccessReportList()
Dim db As String
Dim intChan1 As Long
Dim strResponse As String
db = App.Path & "\RangeLocal.mdb"
On Error GoTo PrintAccessReport_Err

Dim objAccess As Object
Set objAccess = CreateObject("Access.Application")

With objAccess
' open the database
.OpenCurrentDatabase filepath:=db
.Visible = False
intChan1 = DDEInitiate("MSAccess", "RangeLocal")
'request a list of reports in the database
strResponse = DDERequest(intChan1, "ReportList")
MsgBox strResponse
DDETerminate intChan1
End With

' tidy up
Set objAccess = Nothing
Exit Sub

PrintAccessReport_Err:
MsgBox Error$(), vbInformation, " Access Database Automation Error"
End Sub


"Nicola" <bo***@bonzai.co.uk> wrote in message
news:42********@mk-nntp-2.news.uk.tiscali.com...
Hi Everyone,

I am new to programming and would like to know how to
open an access Report from within vb 6. I am trying to write a program
to organise cross stitch threads. I have found out how to use a database
table
but all I want to do now is to click a command button to display this
access report.

Any suggestions please ?????

Thank you in advance

Jul 17 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.