473,320 Members | 2,000 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Opening an Access Report from vb6 pro

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
3 23832
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Andrew | last post by:
Hi All: I am using Access2000 and I find that the command to open an Access report in preview mode is very slow: DoCmd.OpenReport rptABC, acViewPreview, "", "" The scenario is this: - The...
15
by: Mark C | last post by:
All, I have exhaustingly been looking through the newsgroups in search of a way to systemically output an Access 97 report to a pdf file using the full version of Adobe Acrobat. I want the user...
0
by: project | last post by:
I want to print an sales invoice with out open crystal report. I want to know ,with out opening Crystal report, how can I give the command to be printing invoice. I'm using the following lines...
3
by: Newbie | last post by:
This is my first try at running Access Report by Visual Basic I have the following code in my button press event: ' 2 - Show print preview objAccess.DoCmd.OpenReport "Invoices", 2, , ".=" & _...
3
by: ducky | last post by:
I need help with opening a report when a button is clicked, but I only want to open the report with the Object I have chosen in the combo box. This is what i have so far: 'If 2 is selected open...
8
by: ljungers | last post by:
Wondering if somone knows how to open a Access report in Word or export it to Word. Currently I'm opening and printing a report using VBA with the following command (DoCmd.OpenReport "TheReportName",...
0
by: keithsimpson3973 | last post by:
Does anyone know if it is possible to display the value of a date picker control on a vb6 form that the user selected on an access report? I am using vb6 to display a report selection and it has a...
7
by: kpfunf | last post by:
Getting the following error opening a report, cannot figure out the cause: "You tried to execute a query that does not include the specified expression 'RQ_FuelQuoteReportHistory.Vendor' as part...
4
tuxalot
by: tuxalot | last post by:
What is the best way to use a pdf file as the basis for a report? I have a Texas Work Comp. file that I would like to use as a report form. The original pdf fills an 8-1/2x11 page nicely within...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.