Re: MS Access Reports and VB.NET Program

microsoft.public.dotnet.languages.vb

21 Aug 2006 2:02 PM

Izzy

While opinions will vary as to which is a better reporting tool,
something else to keep in mind is, the future of your database. If this
database will never grow out of Access then I say write the reports in
Access.

If you think this database might someday be upgraded to SQL Server or
Oracle then Crystal will be a much better solution as you wont be
rewriting reports in the future.

Izzy

aaron.ke***@gmail.com wrote:
Show quoteHide quote

> or of course you could just shell to the macro
>
> c:\InsertFullPath\MSACCESS.exe "c:\mySilly.mdb" /X macroname
>
> right?
>
> isn't that easier?
>
> Access reports kick Crystal's ass
>
> -aaron
>
>
> George Shubin wrote:
> > You need to have the Access application installed (not just the mdb file) on
> > the computer you want to run the report. Try using this code: (watch out
> > for word wrap)
> >
> > Public Sub PrintAccessReport(ByVal sReportName As String, Optional ByVal
> > sQryName As String = "", Optional ByVal sSQL As String = "")
> >   Dim oAccess As New Access.ApplicationClass
> >   oAccess.Visible = True
> >   oAccess.OpenCurrentDatabase("C:\PathTo\Your\Database.mdb")
> >   oAccess.DoCmd.Minimize()
> >   'Create new query if needed
> >   If sQryName.Length > 0 And sSQL.Length > 0 Then
> >    Try
> >     'oAccess.CurrentDb.QueryDefs.Delete(sQryName)
> >     oAccess.DoCmd.DeleteObject(Access.AcObjectType.acQuery, sQryName)
> >    Catch
> >     'Close and reopen the Access object if above statement failed
> >     oAccess.Visible = False
> >     oAccess.Quit(Access.AcQuitOption.acQuitSaveNone)
> >     System.Runtime.InteropServices.Marshal.ReleaseComObject(oAccess)
> >     oAccess.Visible = True
> >     oAccess.OpenCurrentDatabase("C:\PathTo\Your\Database.mdb")
> >     oAccess.DoCmd.Minimize()
> >    End Try
> >    oAccess.CurrentDb.CreateQueryDef(sQryName, sSQL)
> >   End If
> >   'Preview the report
> >   oAccess.DoCmd.OpenReport(sReportName, Access.AcView.acViewPreview, , ,
> > Access.AcWindowMode.acDialog)
> >   'Close the Access Instance
> >   If Not oAccess Is Nothing Then
> >    ' Call Access Quit method without saving any changes.
> >    oAccess.Quit(Access.AcQuitOption.acQuitSaveNone)
> >    ' Use Marshal class' ReleaseComObject to release the Access instance.
> >    System.Runtime.InteropServices.Marshal.ReleaseComObject(oAccess)
> >    ' Dereference the oAccess variable.
> >    oAccess = Nothing
> >   End If
> > End Sub
> >
> > --
> > ------------------------------------------------------------------------
> > George Shubin       Custom Software Development
> > dX Software Systems          Database Applications
> > Ph: 503-981-6806                     Fax: 503-982-0120
> > www.dxonline.com              geo***@dxonline.com
> > ------------------------------------------------------------------------
> >
> > "JoeW" <wajo0***@stcloudstate.edu> wrote in message
> > news:1155745516.792909.268870@m73g2000cwd.googlegroups.com...
> > > I'm utilizing a database that I created within MS Access within a
> > > program I've created in VB.NET. I am using the VB front end to
> > > navigate the information, but want to be able to print a report, that
> > > I've also created within MS Access. I've attempted using the
> > > Access.Application instance, but I get errors when it tries to load the
> > >
> > > database. Just wondering if anyone has any experience with this, and
> > > what I should do.
> > >
> > > Thanks for any help you can offer.
> > >

21 Aug 2006 5:11 PM

aaron.kempf@gmail.com

if you upgrade your database to SQL Server you don't have to rewrite
anything; except maybe a couple of sprocs.

-Aaron

Izzy wrote:
Show quoteHide quote

> While opinions will vary as to which is a better reporting tool,
> something else to keep in mind is, the future of your database. If this
> database will never grow out of Access then I say write the reports in
> Access.
>
> If you think this database might someday be upgraded to SQL Server or
> Oracle then Crystal will be a much better solution as you wont be
> rewriting reports in the future.
>
> Izzy
>
> aaron.ke***@gmail.com wrote:
> > or of course you could just shell to the macro
> >
> > c:\InsertFullPath\MSACCESS.exe "c:\mySilly.mdb" /X macroname
> >
> > right?
> >
> > isn't that easier?
> >
> > Access reports kick Crystal's ass
> >
> > -aaron
> >
> >
> > George Shubin wrote:
> > > You need to have the Access application installed (not just the mdb file) on
> > > the computer you want to run the report. Try using this code: (watch out
> > > for word wrap)
> > >
> > > Public Sub PrintAccessReport(ByVal sReportName As String, Optional ByVal
> > > sQryName As String = "", Optional ByVal sSQL As String = "")
> > >   Dim oAccess As New Access.ApplicationClass
> > >   oAccess.Visible = True
> > >   oAccess.OpenCurrentDatabase("C:\PathTo\Your\Database.mdb")
> > >   oAccess.DoCmd.Minimize()
> > >   'Create new query if needed
> > >   If sQryName.Length > 0 And sSQL.Length > 0 Then
> > >    Try
> > >     'oAccess.CurrentDb.QueryDefs.Delete(sQryName)
> > >     oAccess.DoCmd.DeleteObject(Access.AcObjectType.acQuery, sQryName)
> > >    Catch
> > >     'Close and reopen the Access object if above statement failed
> > >     oAccess.Visible = False
> > >     oAccess.Quit(Access.AcQuitOption.acQuitSaveNone)
> > >     System.Runtime.InteropServices.Marshal.ReleaseComObject(oAccess)
> > >     oAccess.Visible = True
> > >     oAccess.OpenCurrentDatabase("C:\PathTo\Your\Database.mdb")
> > >     oAccess.DoCmd.Minimize()
> > >    End Try
> > >    oAccess.CurrentDb.CreateQueryDef(sQryName, sSQL)
> > >   End If
> > >   'Preview the report
> > >   oAccess.DoCmd.OpenReport(sReportName, Access.AcView.acViewPreview, , ,
> > > Access.AcWindowMode.acDialog)
> > >   'Close the Access Instance
> > >   If Not oAccess Is Nothing Then
> > >    ' Call Access Quit method without saving any changes.
> > >    oAccess.Quit(Access.AcQuitOption.acQuitSaveNone)
> > >    ' Use Marshal class' ReleaseComObject to release the Access instance.
> > >    System.Runtime.InteropServices.Marshal.ReleaseComObject(oAccess)
> > >    ' Dereference the oAccess variable.
> > >    oAccess = Nothing
> > >   End If
> > > End Sub
> > >
> > > --
> > > ------------------------------------------------------------------------
> > > George Shubin       Custom Software Development
> > > dX Software Systems          Database Applications
> > > Ph: 503-981-6806                     Fax: 503-982-0120
> > > www.dxonline.com              geo***@dxonline.com
> > > ------------------------------------------------------------------------
> > >
> > > "JoeW" <wajo0***@stcloudstate.edu> wrote in message
> > > news:1155745516.792909.268870@m73g2000cwd.googlegroups.com...
> > > > I'm utilizing a database that I created within MS Access within a
> > > > program I've created in VB.NET. I am using the VB front end to
> > > > navigate the information, but want to be able to print a report, that
> > > > I've also created within MS Access. I've attempted using the
> > > > Access.Application instance, but I get errors when it tries to load the
> > > >
> > > > database. Just wondering if anyone has any experience with this, and
> > > > what I should do.
> > > >
> > > > Thanks for any help you can offer.
> > > >

21 Aug 2006 8:43 PM

Izzy

aaron.ke***@gmail.com wrote:

> if you upgrade your database to SQL Server you don't have to rewrite
> anything; except maybe a couple of sprocs.

Aaron,

Lets say he did upgrade his Access database to Sql Server. Keeping in
mind he's using .NET too write his client app. Are you suggesting using
..NET (which can query Sql Server more efficiently than the Jet engine
using ADO.NET) to send a request to Access which will in turn query Sql
Server sending the results back to Access and be displayed in .NET?

Talk about a performance nightmare.

I'm not saying no one should use Access as thier front end tool. Access
works just fine for some and not for others.

What I'm saying is if you are using Sql Server as your back end and
VB.NET as your front end, then why would you use Access? Unless you
were emotionally attached too it.

Unlock your mind sir.

Show quoteHide quote

>
> -Aaron
>
>
> Izzy wrote:
> > While opinions will vary as to which is a better reporting tool,
> > something else to keep in mind is, the future of your database. If this
> > database will never grow out of Access then I say write the reports in
> > Access.
> >
> > If you think this database might someday be upgraded to SQL Server or
> > Oracle then Crystal will be a much better solution as you wont be
> > rewriting reports in the future.
> >
> > Izzy
> >
> > aaron.ke***@gmail.com wrote:
> > > or of course you could just shell to the macro
> > >
> > > c:\InsertFullPath\MSACCESS.exe "c:\mySilly.mdb" /X macroname
> > >
> > > right?
> > >
> > > isn't that easier?
> > >
> > > Access reports kick Crystal's ass
> > >
> > > -aaron
> > >
> > >
> > > George Shubin wrote:
> > > > You need to have the Access application installed (not just the mdb file) on
> > > > the computer you want to run the report. Try using this code: (watch out
> > > > for word wrap)
> > > >
> > > > Public Sub PrintAccessReport(ByVal sReportName As String, Optional ByVal
> > > > sQryName As String = "", Optional ByVal sSQL As String = "")
> > > >   Dim oAccess As New Access.ApplicationClass
> > > >   oAccess.Visible = True
> > > >   oAccess.OpenCurrentDatabase("C:\PathTo\Your\Database.mdb")
> > > >   oAccess.DoCmd.Minimize()
> > > >   'Create new query if needed
> > > >   If sQryName.Length > 0 And sSQL.Length > 0 Then
> > > >    Try
> > > >     'oAccess.CurrentDb.QueryDefs.Delete(sQryName)
> > > >     oAccess.DoCmd.DeleteObject(Access.AcObjectType.acQuery, sQryName)
> > > >    Catch
> > > >     'Close and reopen the Access object if above statement failed
> > > >     oAccess.Visible = False
> > > >     oAccess.Quit(Access.AcQuitOption.acQuitSaveNone)
> > > >     System.Runtime.InteropServices.Marshal.ReleaseComObject(oAccess)
> > > >     oAccess.Visible = True
> > > >     oAccess.OpenCurrentDatabase("C:\PathTo\Your\Database.mdb")
> > > >     oAccess.DoCmd.Minimize()
> > > >    End Try
> > > >    oAccess.CurrentDb.CreateQueryDef(sQryName, sSQL)
> > > >   End If
> > > >   'Preview the report
> > > >   oAccess.DoCmd.OpenReport(sReportName, Access.AcView.acViewPreview, , ,
> > > > Access.AcWindowMode.acDialog)
> > > >   'Close the Access Instance
> > > >   If Not oAccess Is Nothing Then
> > > >    ' Call Access Quit method without saving any changes.
> > > >    oAccess.Quit(Access.AcQuitOption.acQuitSaveNone)
> > > >    ' Use Marshal class' ReleaseComObject to release the Access instance.
> > > >    System.Runtime.InteropServices.Marshal.ReleaseComObject(oAccess)
> > > >    ' Dereference the oAccess variable.
> > > >    oAccess = Nothing
> > > >   End If
> > > > End Sub
> > > >
> > > > --
> > > > ------------------------------------------------------------------------
> > > > George Shubin       Custom Software Development
> > > > dX Software Systems          Database Applications
> > > > Ph: 503-981-6806                     Fax: 503-982-0120
> > > > www.dxonline.com              geo***@dxonline.com
> > > > ------------------------------------------------------------------------
> > > >
> > > > "JoeW" <wajo0***@stcloudstate.edu> wrote in message
> > > > news:1155745516.792909.268870@m73g2000cwd.googlegroups.com...
> > > > > I'm utilizing a database that I created within MS Access within a
> > > > > program I've created in VB.NET. I am using the VB front end to
> > > > > navigate the information, but want to be able to print a report, that
> > > > > I've also created within MS Access. I've attempted using the
> > > > > Access.Application instance, but I get errors when it tries to load the
> > > > >
> > > > > database. Just wondering if anyone has any experience with this, and
> > > > > what I should do.
> > > > >
> > > > > Thanks for any help you can offer.
> > > > >