School Project - VB.NET application with MS Access DB

[vbrecka]

I am working on a group project for school and I'm looking for some resources or direction. We are creating a sales and inventory system for a musical instrument store. I've taken on the

responsibility of creating the reports that will accompany our

system (for example: Daily Sales, Employee Productivity, Low-Stock Flagged Items, etc.). I have found that we can use

Crystal Reports, but I really don't want to. It's ugly and there's a serious learning curve for me. I need to know if I can create the reports within the Access Database and allow the user to access these reports (to respond to prompt parameters, and then view and print the report) from our VB.NET application.

 

FYI, I have used VB5 and VB6, and am an intermediate to advanced user of MS-Access. I'm new to using VB.NET, but fortunately we have a team member that is very familiar with it.

 

The end of our semester is sneaking up quickly. Any help is appreciated!

[Robby]

This works in VB6 and with a little tweak in will work in .NET.

'add a reference to your project "Microsoft Access 8.0/9.0 or 10.0 Object Library(97, 2000, 2002)
Dim appAccess As Access.Application
Set appAccess = New Access.Application
appAccess.OpenCurrentDatabase (YourStringPath)
appAccess.DoCmd.OpenReport YourStringReportName, acPreview, ,YourStringCriteria
appAccess.DoCmd.PrintOut acPrintAll, , , acMedium
appAccess.Quit
Set appAccess = Nothing

[vbrecka]

I've gotten this code to work, but I have one more question. This code will automatically print the report once the button is clicked. Is it possible to get a screen preview on the report and then add a button to print it? Edited December 5, 2003 by vbrecka

[Voca]

Try using the printpreview-dialog. It's been some time I've used it but I think it should work for you, as there is only little to program.

 

Cavo

[Robby]

vbrecka, omit the line "appAccess.DoCmd.PrintOut acPrintAll, , , acMedium"

[vbrecka]

I could not get the Access report to print to the screen for the user to preview it before printing it. Therefore I decided to try the following solution:

*************************

appAccess.OpenCurrentDatabase(strDatabaseName)

 

appAccess.DoCmd.OpenReport(strReportName, Access.AcView.acViewPreview, , , Access.AcWindowMode.acDialog, )

 

appAccess.DoCmd.OutputTo(Access.AcOutputObjectType.acOutputReport, strReportName, , strOutputHTMLFile, True, , )

 

appAccess.Quit()

appAccess = Nothing

**************************

 

It saves the report to an .HTML file where it opens up in IE to be previewed on the screen, and can be printed from there. It's sloppy, but it works.

 

Has anyone used the OutputTo before? In this line, where I have the ****'s, supposedly I can put in the constant "acFormatHTML" however, VB.NET gives me the error that it is not declared. I've tried finding any method that "acFormatHTML" is part of, but it keeps giving errors:

appAccess.DoCmd.OutputTo(Access.AcOutputObjectType.acOutputReport, strReportName, *********, strOutputHTMLFile, True, , )

 

Any hints on this one?

[Robby]

I don't understand why this line does not allow you to preview (it works for me)...

 

appAccess.DoCmd.OpenReport YourStringReportName, acPreview, ,YourStringCriteria

[vbrecka]

Could it have to do with the versions of the software, or maybe it's how I'm accessing the database? Or could I have missed adding a reference?

 

We're running Windows XP, MS-Access 2002 and Visual Basic.NET has the following under it: MS Development Enviroment 2003 ver. 7.1.3088 and MS .NET Framework 1.1 ver. 1.1.4322.

 

I'm connecting to my DB with this:

appAccess.OpenCurrentDatabase(strDatabaseName)

 

I couldn't just use "acOutputReport" either. I had to use: "Access.AcOutputObjectType.acOutputReport".

[Robby]

does this line work as is...

 

appAccess.DoCmd.OpenReport strReportName, acPreview

[vbrecka]

No, that line does NOT work as is. The "acViewPreview" is not recognized. VB.NET insists it should be "Access.AcView.acViewPreview"