Solved

Help needed to create a MS Access Report with VB

Posted on 2003-11-14
16
2,477 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
  • 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 69

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
 

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 69

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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now