How do I programmatically query-based reports in an Access 2005 database from a WCF application?

Posted on 2011-02-22
Medium Priority
Last Modified: 2013-11-28
Hello all,

  I have a database created with MS Access 2003 which contains a large number of reports.  These reports are generated based on queries and, while using Access, the parameters for these queries are gathered in a form.  Internally (i.e. when opening the mdb in Access) all of the operations work perfectly.
  However, I now need to be able to collect those parameters (like date ranges) from an ASP page and publish the Access report to the browser.

  In order to bridge the native process to web application gap, the ASP application implements a WCF client and the server sits on a machine with the database.  When the ASP client requests a report, I need the server to be able to programmatically  supply those parameters to MS Access, generate the report, save it to HTML, then return the HTML to the client.
  My current implementation/attempt using the Microsoft.Office.Interop.Access library to get a handle to the Access object and from there I can

Open in new window

but that doesn't allow me to supply parameters.  I can also
oAccess.Run("Procedure1", "Parameter1", "Parameter2")

Open in new window

but that still leaves me with having to do all of the query work in VBA.

Thanks for you time,
Question by:Dalexan
LVL 31

Accepted Solution

Helen Feddema earned 2000 total points
ID: 34953100
Here is some Access VBA code I use to programmatically create a filtered recordset for a report, which is then saved as a PDF and emailed.  You could save the reports in some other format instead of PDF, and get the filter criteria from the ASP client.
Public Sub SendInterventionEmails()
'Created by Helen Feddema 10-Jan-2010
'Last modified by Helen Feddema 10-Jan-2010

On Error GoTo ErrorHandler

   Dim appOutlook As New Outlook.Application
   Dim itm As Outlook.MailItem
   Dim rstIntervention As DAO.Recordset
   Dim lngCount As Long
   Dim lngID As Long
   Dim rpt As Access.Report
   Dim strFileName As String
   Dim strPrompt As String
   Dim strQuery As String
   Dim strRecordSource As String
   Dim strReport As String
   Dim strSQL As String
   Dim strTitle As String
   Dim strCurrentPath As String
   Dim strFileNameAndPath As String
   Dim strEmailSource As String
   strEmailSource = "qryInterventionEmail"
   strRecordSource = "qryMissingAssignments"
   strQuery = "qryMissingAssignmentsSingleStudent"
   Set dbs = CurrentDb
   Set rstIntervention = dbs.OpenRecordset(strEmailSource)
   strCurrentPath = Application.CurrentProject.Path & "\"
   'Use path selected with SelectFolder procedure
   'strCurrentPath = SelectFolder()

   With rstIntervention
      Do While Not .EOF
         lngID = ![StID]
         Debug.Print "Processing Student ID " & lngID
         strFileName = "Intervention Report for " & ![StFirst] _
            & " " & ![StLast] & ".pdf"
         strFileNameAndPath = strCurrentPath & strFileName
         'Create filtered query
         strSQL = "SELECT * FROM " & strRecordSource & " WHERE " _
            & "[StID] = " & Chr(39) & lngID & Chr(39) & ";"
         Debug.Print "SQL for " & strQuery & ": " & strSQL
         lngCount = CreateAndTestQuery(strQuery, strSQL)
         Debug.Print "No. of items found: " & lngCount
         If lngCount = 0 Then
            GoTo NextStudent
         End If
         'Open report with filtered query record source
         strReport = "rptMissingAssignmentsNew"
         DoCmd.OpenReport ReportName:=strReport, _
            View:=acViewPreview, _
         Set rpt = Reports(strReport)
         DoCmd.OutputTo objecttype:=acOutputReport, _
            objectname:=strReport, _
            outputformat:=acFormatPDF, _
         'Create email
         Set itm = appOutlook.CreateItem(olMailItem)
         itm.Subject = "MISSING WORK"
         itm.Body = "The attached file lists your missing assignments"
         itm.To = ![Email]
         itm.Attachments.Add Source:=strFileNameAndPath, _
         'For editing before sending
         'For sending automatically
         DoCmd.Close objecttype:=acReport, _
            objectname:=strReport, _

   End With
   Exit Sub

   MsgBox "Error No: " & Err.Number _
      & " in SendInterventionEmails procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit

End Sub

Public Function CreateAndTestQuery(strTestQuery As String, _
   strTestSQL As String) As Long
'Created by Helen Feddema 28-Jul-2002
'Last modified by Helen Feddema 10-Jan-2010

On Error Resume Next
   'Delete old query
   Set dbs = CurrentDb
   dbs.QueryDefs.Delete strTestQuery

On Error GoTo ErrorHandler
   'Create new query
   Set qdf = dbs.CreateQueryDef(strTestQuery, strTestSQL)
   'Test whether there are any records
   Set rst = dbs.OpenRecordset(strTestQuery)
   With rst
      CreateAndTestQuery = .RecordCount
   End With
   Exit Function

   If Err.Number = 3021 Then
      CreateAndTestQuery = 0
      Resume ErrorHandlerExit
   MsgBox "Error No: " & Err.Number _
      & " in CreateAndTestQuery procedure; " _
      & "Description: " & Err.Description
   End If
End Function

Open in new window


Author Closing Comment

ID: 34960690
I worked with Jason, the original poster, on this project. The key assistance in Helen Feddema's answer was the idea to re-write the query underlying the report. We will end up taking a "base" query, insert a WHERE clause constructed from the supplied parameters, and use the result as the report's RecordSource. I intend to post the Access portion of the solution later today.

Featured Post

Live webcast with Pinal Dave

Pinal Dave will teach you tricks to help identify the real root cause of database problems rather than red herrings. Attendees will learn scripts that they can use in their environment to immediately figure out their performance Blame Shifters and fix them quickly.

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.

Join & Write a Comment

Blockchain technology enhances society similar to the Internet. Its effects are broad, disruptive, and will boost global productivity.
A method of moving multiple mailboxes (in bulk) to another database in an Exchange 2010/2013/2016 environment...
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

607 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