Help needed to create a MS Access Report with VB

Posted on 2003-11-14
Last Modified: 2013-12-25

I'm not sure wheter this is possible, but I need to create an Access Report from VB. (Not just call it.)

My VB app creates a table in Access with columns changes depending on user choices.
     (The amount of columns and also the column names).

The report is not very complex. If I run report wizard in Access by selecting the mentioned table as my datasource and following the wizard, I get the desired results, by using the default values. The problem comes in when the table structure change, the report structure does not refresh accordingly.

I suppose what I'm trying to ask is, is there a way to 'use' the MS Access Report Wizard from within VB, and if so how..?

Question by:SV
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 3
  • +3

Expert Comment

ID: 9746599
why don't try Crystal Report or DataReport.

Author Comment

ID: 9746625
Thanks for the suggestion, but unfortunately I'm not allowed to use Crystal Reports on this project,
and out of experience the datareport is to unstable, and the db is Access.

Bit of a tricky question, I suppose.
LVL 70

Expert Comment

by:Éric Moreau
ID: 9746996
VB let you manipulate (create/modifiy) tables, queries, users, even modules but not forms and reports of an Access database.

For very simple report, datareport can be enough.

Another way to go, could be Word or Excel that let you create whatever you want from VB.
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now


Author Comment

ID: 9747047
Thanx, but there SHOULD be a way,
since Access is COM based,
we should be able to create it from VB. Don't you think?

The big problem is, that is what my boss wants. You all know the drill.

Expert Comment

ID: 9747062
Do you have MSDN library install ?
just search "datareport" and a lot of definition and example will gift to you.
LVL 70

Expert Comment

by:Éric Moreau
ID: 9747095
LVL 29

Expert Comment

ID: 9747620

I do not know if this would be a good choice for you, but would your boss accept an Excel based report instead?


Author Comment

ID: 9747660

Nope! Don't think so.
Urgh! This is such an annoying problem.
Do you know if you can create or modify a report with a macro?

Expert Comment

ID: 9747795

I have been working with VB and Access for ages. I have yet to be able to create an access report from VB. I was using datareports until I started using Crystal Reports with VB Net. you may be able to import the ADO Library reference and do something with the DBEngine, but I still do not think that you can create an access report from VB. VB is a tool used to access the information stored in Access and nothing more. The fact that you can compress and repair the database are also VB tools used for manipulating the data in the Access Database, however, creating an Access form is not a possibility that I have found yet, and trust me, its not that I havent tried. You can export to Excel, Word, Email, RTF, textfile, and CSV, but you cannot 'create' something in another program without knowing the ole interface (if they have made provision for such a feature). I know that even modifying Access tables from VB can be somewhat of a tedious chore.

I had the same instance happen to me with my boss, once I explained to him that he has all these other options easily and readily available to him, and I even did dynamic reporting with exportation to excel for all available reports, and Datareports for reporting purposes. He was sufficiently happy with the results. May I ask why it _has_ to be an Access Report?


Author Comment

ID: 9747846

Thanks for the reply and the explanation. Problem is not really the boss, more a client issue. We said it will be in Access, and they might just decapitate us if we don't deliver exactly like we said.

So, I really need to get this done!

Thanks, so much for everyone's help on this. It just amazes me how nice people can be! :)

Expert Comment

ID: 9748409

I dont know if this will help or not.

Check it out.


Expert Comment

ID: 9748428
LOL, just noticed its the same as above.

Expert Comment

ID: 9752295
friend :)
i think you got to explain your problem to your boss and told him about you wish to change your reporting tools.
switch to more powerful reporting tools will bring you a new gate.

Accepted Solution

VB-Expert earned 200 total points
ID: 9761460


The files containing the routines to make this possible, are  acwzmain.mde, acwztool.mde & acwzlib.mde; Make mde file is used for this.

The weblink shows how to get around the problem, but as is mentioned, has drawbacks.

NOTE: You cannot programmatically create a reference using Visual Basic. It must be created manually. The basic steps are as follows:

1.  Open your database in which you want to start the wizards from Visual Basic.
2.  Open a module in Design view.
3.  On the Tools menu, click References.
4.  If the libraries appear in the list of available references, ensure that their check boxes are
     checked and click OK. If these libraries are not listed, then click the Browse button and
     proceed with step 5.
5.  In the Add Reference box, Select "Databases *.MDB, *.MDA" from the Files Of Type list, and
     then locate the Wzmain80.mde or Wztool80.mde.

     Note that these files are typically installed in the Microsoft Office folder in the subfolder,
     Office (for example, C:\Msoffice\Office).

     NOTE: In Microsoft Access 7.0 for Windows 95, you would look for Wzmain70.mda or
     Wztool70.mda, typically located in the Microsoft Access folder (for example,

6. Once you locate the library (for example Wzmain80.mde), select the file name and click OK.
    The library name should then appear in the References box with a check mark. Click OK to
    close the References box.

Syntax Using a Reference

If you want to avoid creating a reference to Wztool80.mde and Wzmain80.mde (Microsoft Access 97) or Wztool70.mda and Wzmain70.mda (Microsoft Access 7.0), you can use the Run method of the Application object in a Visual Basic procedure to call a wizard, for example:
   Application.Run "<library>.<function>", [arguments]


Author Comment

ID: 9761850
What can I say ???? VB-Expert, thanks soooooooooo much! Your idea worked!
The code in the end was quite simple:

I've added a reference to Access and the following code did the trick!

Private Sub PrintReport()

    Dim objAccess   As Object
    Dim dbname      As String
    dbname = "C:\Development\EMSCostDB.mdb"
    Set objAccess = CreateObject("Access.Application")
    With objAccess
        .Visible = True
        .OpenCurrentDatabase filepath:=dbname
    End With
    Application.Run "ACWZMAIN.auto_Entry", "tmpScenarios", 2, acReport

End Sub

Thanks again.


Expert Comment

ID: 9766369
Wow, good one VB-Expert, I tried for months to make Access Reports in VB, couldn't and ended up using DataReports. Too bad I didn't know about EE then. LOL.

Featured Post

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question ( in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Suggested Courses
Course of the Month9 days, 19 hours left to enroll

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question