Help needed to create a MS Access Report with VB

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
SVAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MYLimCommented:
why don't try Crystal Report or DataReport.
0
SVAuthor Commented:
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
Éric MoreauSenior .Net ConsultantCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

SVAuthor Commented:
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
MYLimCommented:
Do you have MSDN library install ?
just search "datareport" and a lot of definition and example will gift to you.
0
Éric MoreauSenior .Net ConsultantCommented:
0
leonstrykerCommented:
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
SVAuthor Commented:
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
wulfshaydeCommented:
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
SVAuthor Commented:
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
wulfshaydeCommented:
SV,

I dont know if this will help or not.

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

Check it out.

-wulfshayde
0
wulfshaydeCommented:
LOL, just noticed its the same as above.
0
MYLimCommented:
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
VB-ExpertCommented:

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SVAuthor Commented:
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
wulfshaydeCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.