Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Help needed to create a MS Access Report with VB

Posted on 2003-11-14
16
Medium Priority
?
2,533 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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 800 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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

722 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