Opening an Access Report from vb6 pro

[Nicola]

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

[Dikkie Dik]

Re: Opening an Access Report from vb6 pro

Nicola schreef:[color=blue]
> 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
>
>[/color]
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.Applicat ion object, or use the createObject function. With that
reference you can open a database and give the command to open the report:

set objDatabaseAppl ication=new Access.Applicat ion
(open a database. without trying, I think it is done with the
SetCurrentDatab ase method)
objDatabaseAppl ication.DoCmd.O penReport <report name>

Hope this helps

[Veign]

Re: Opening an Access Report from vb6 pro

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" <bonni@bonzai.c o.uk> wrote in message
news:42139cc9_1 @mk-nntp-2.news.uk.tisca li.com...[color=blue]
> 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[/color]
access[color=blue]
> report.
>
> Any suggestions please ?????
>
> Thank you in advance
>
>[/color]

[Robert Berman]

Re: Opening an Access Report from vb6 pro

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

' **************m odAccessReports .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 dcward@xtra.co. nz
' *************** *************** *************** ******

Declare Function SetForegroundWi ndow 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 PrintAccessRepo rt(dbName As String, _
rptName As String, _
Optional rptFilter As Variant, _
Optional rptWhere As Variant)

On Error GoTo PrintAccessRepo rt_Err

Dim objAccess As Object
Set objAccess = GetObject(DBPat h)

With objAccess
' open the database
' .OpenCurrentDat abase filepath:=dbNam e
' send the report to printer.
.DoCmd.OpenRepo rt rptName, 0, rptFilter, rptWhere
DoEvents ' off it goes to the printer
End With

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

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

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

On Error GoTo PreviewAccessRe port_Err

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

Dim objAccess As Object
Set objAccess = CreateObject("A ccess.Applicati on")

With objAccess
' open the database
.OpenCurrentDat abase dbName, False
' make it visible
.Visible = True
' maximize the db window size
hwnd = objAccess.hWndA ccessApp
temp = SetForegroundWi ndow(hwnd)
temp = ShowWindow(hwnd , SIZE)
' open the report
.DoCmd.OpenRepo rt rptName, 2, rptFilter, rptWhere
' maximize the report window
' inside the db window
.DoCmd.Maximize
End With

PreviewAccessRe port_End:
objAccess.Quit
Set objAccess = Nothing
Exit Sub

PreviewAccessRe port_Err:
MsgBox Error$(), vbInformation, " Access Database Automation Error"
Resume PreviewAccessRe port_End
End Sub

Public Sub EmailAccessRepo rt(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 EmailAccessRepo rt_Err

Dim objAccess As Object
Set objAccess = CreateObject("A ccess.Applicati on")

With objAccess
' open the database
.OpenCurrentDat abase filepath:=dbNam e
' email a report
.DoCmd.SendObje ct acReport, _
rptName, _
rptFormat, _
rptTo, _
rptCc, _
rptBcc, _
rptSubject, _
rptMessage, _
rptEdit, _
rptTemplate

DoEvents

End With
' tidy up
Set objAccess = Nothing

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

Public Sub SaveReportAsWor dDoc(dbName As String, _
rptName As String, _
rptPath As String)

On Error GoTo SaveReportAsWor dDoc_Err



Dim objAccess As Object
Set objAccess = CreateObject("A ccess.Applicati on")

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

SaveReportAsWor dDoc_End:
Exit Sub

SaveReportAsWor dDoc_Err:
MsgBox Error$(), vbInformation, " Access Database Automation Error"
Resume SaveReportAsWor dDoc_End
End Sub

Public Sub SaveReportAsHTM L(dbName As String, _
rptName As String, _
rptPath As String)

On Error GoTo SaveReportAsHTM L_Err



Dim objAccess As Object
Set objAccess = CreateObject("A ccess.Applicati on")

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

SaveReportAsHTM L_End:
Exit Sub

SaveReportAsHTM L_Err:
MsgBox Error$(), vbInformation, " Access Database Automation Error"
Resume SaveReportAsHTM L_End
End Sub

Public Sub SaveReportAsExc el(dbName As String, _
rptName As String, _
rptPath As String)

On Error GoTo SaveReportAsExc el_Err



Dim objAccess As Object
Set objAccess = CreateObject("A ccess.Applicati on")

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

SaveReportAsExc el_End:
Exit Sub

SaveReportAsExc el_Err:
MsgBox Error$(), vbInformation, " Access Database Automation Error"
Resume SaveReportAsExc el_End
End Sub
' ********* Form module procedures
' **Preview Report
'Private Sub cmdPreviewRepor t_Click()
'Dim db As String
'Dim rptName As String
'db = "C:\...\...\Dat abase\MyDB.mdb"
'rptName = "My Report Name"
'
'PreviewAccessR eport db, rptName
'End Sub

' **Print Report
'Private Sub PrintReport_Cli ck()
'Dim db As String
'Dim rptName As String
'db = "C:\...\...\Dat abase\MyDB.mdb"
'rptName = "My Report Name"
'
'PrintAccessRep ort 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:\...\...\Dat abase\MyDB.mdb"
'rptName = "My Report Name"
'rptFormat = "HTML"
'rptTo = "dcward@xtra.co .nz"
'rptSubject = "Subject Title: " & Date
'rptMessage = "Attached is our latest report for your information."
'
'EmailAccessRep ort db, rptName, rptFormat, rptTo, rptSubject, , , _
' rptMessage, False
'
'End Sub


Public Sub AccessReportLis t()
Dim db As String
Dim intChan1 As Long
Dim strResponse As String
db = App.Path & "\RangeLocal.md b"
On Error GoTo PrintAccessRepo rt_Err

Dim objAccess As Object
Set objAccess = CreateObject("A ccess.Applicati on")

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

' tidy up
Set objAccess = Nothing
Exit Sub

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








"Nicola" <bonni@bonzai.c o.uk> wrote in message
news:42139cc9_1 @mk-nntp-2.news.uk.tisca li.com...[color=blue]
> 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
>[/color]