Solved

Help needed to create a MS Access Report with VB

Posted on 2003-11-14
16
2,523 Views
Last Modified: 2013-12-25
Hi,

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

Background:
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..?

Thanx
S
0
Comment
Question by:SV
[X]
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
16 Comments
 
LVL 8

Expert Comment

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

Author Comment

by:SV
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.
0
 
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.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:SV
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.
0
 
LVL 8

Expert Comment

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

Expert Comment

by:Éric Moreau
ID: 9747095
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 9747620
SV,

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

Leon
0
 

Author Comment

by:SV
ID: 9747660
Hi,

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?
0
 
LVL 4

Expert Comment

by:wulfshayde
ID: 9747795
SV,

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?

wulfshayde
0
 

Author Comment

by:SV
ID: 9747846
Hi!

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! :)
0
 
LVL 4

Expert Comment

by:wulfshayde
ID: 9748409
SV,

I dont know if this will help or not.

http://www.accessreportwizard.com/arw/default.asp?CF=ARW

Check it out.

-wulfshayde
0
 
LVL 4

Expert Comment

by:wulfshayde
ID: 9748428
LOL, just noticed its the same as above.
0
 
LVL 8

Expert Comment

by:MYLim
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.
0
 
LVL 1

Accepted Solution

by:
VB-Expert earned 200 total points
ID: 9761460

Weblink:   http://support.microsoft.com/default.aspx?scid=kb;EN-US;148506

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:

Access.
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,
     C:\Msoffice\Msaccess).

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]

0
 

Author Comment

by:SV
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.

Shantell
0
 
LVL 4

Expert Comment

by:wulfshayde
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.
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
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…

751 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