Access Report

[mik706]

I am generating a report in Access from a query. How can I modify the Query from VB?
I am not curently sending and SQL query, i am using one generated in Access. The problem is i want to be able to change the query (or pass a query) at runtime without giving the user direct access to the database. I am currently using:

Private Sub button_Click()
Dim strDummy As String

On Error Resume Next

strDummy = Printer.DeviceName
   
If Err.Number Then
    MsgBox "There are no printers installed on this computer. Please install a printer and try again.", vbInformation
Else
    RunAccessReport App.Path & "\RecuitmentPlanner.mdb", "Candidates"
End If
End Sub

Public Sub RunAccessReport(strDB As String, strReport As String)
Dim AccessDB As Access.Application

    Set AccessDB = New Access.Application
    AccessDB.OpenCurrentDatabase strDB
    AccessDB.DoCmd.OpenReport strReport, acViewPreview
    AccessDB.Visible = False
    AccessDB.DoCmd.PrintOut (acPrintAll)
    AccessDB.Quit acQuitSaveAll
    Set AccessDB = Nothing
    
End Sub

How can i pass a query using this method or is there another way to generate reports?

[LucGuldentops]

I have worked out a solution for you.
It may seem a bit complicated, but i think this is the only way to do it.

In your access DB, create 2 querys, qryBase and qryReport.
The 2 querys must be identique, lets say the sql for the query's is like this: SELECT Table1.Field1
FROM Table1
WHERE (((Table1.Field1)="ToBeReplaced"));
Build a report that uses qryReport as source.
The function i wrote will open access, copy the sql from qryBase to qryReport, and replace the criteria 'TobeReplaced' by 'aa'.
Then it opens the report, still based on query qryReport.

The use of the query qryBase is neccesary baecause otherwise you lose the searchstring 'ToBeReplaced' that you need to add your custom selection criteria.

You can modify the function to replace any part of the sql string of qryReport.

Let me know if this helped you out or not.

Greetz,

Luc

Public Sub RunAccessReport(strDB As String, strReport As String)
  
  Dim AccessDB As Access.Application
  Dim sSql As String
  Dim q
  Set AccessDB = New Access.Application
  AccessDB.OpenCurrentDatabase strDB
  Set q = AccessDB.CurrentDb.QueryDefs("qryReport")
  sSql = AccessDB.CurrentDb.QueryDefs("qryBase").SQL
  sSql = Replace(sSql, "ToBeReplaced", "aa")
  q.SQL = sSql
    
  AccessDB.DoCmd.OpenReport strReport, acViewPreview
  AccessDB.Visible = False
  AccessDB.DoCmd.PrintOut (acPrintAll)
  AccessDB.Quit acQuitSaveAll
  Set AccessDB = Nothing
    
End Sub

[Zealot]

You should read about Snapshots. That's what I use in my app so that I can use a report made in Access without letting the user even knowing you are using an Access Report. It has all the functions an Access Report has for the users, but it won't access the Access database. Pretty useful if the user doesn't have an Access license as well!

[mik706]

Cheers, just what i needed. Added a few more ToBeReplace fields and it works perfectly.

I will have a look at the SnapShot link.

[Zealot]

Actually, I think that link sucks.
Check out this link instead. It gives you an example in VB about using the Snapshot viewer.

Also check out this thread where I got some help about using the snapshot viewer.

[mik706]

I have also found that you can do the following:

Public Sub RunAccessReport(strDB As String, strReport As String, strQuery As String)
Dim AccessDB As Access.Application
Dim sSql As String
Dim q
 
Set AccessDB = New Access.Application
AccessDB.OpenCurrentDatabase strDB
Set q = AccessDB.CurrentDb.QueryDefs("QryDB")
sSql = "SELECT * FROM DB"
q.SQL = sSql
   
AccessDB.DoCmd.OpenReport strReport, acViewPreview
AccessDB.Visible = False
AccessDB.DoCmd.PrintOut (acPrintAll)
AccessDB.Quit acQuitSaveAll
Set AccessDB = Nothing
    
End Sub

This gives me total control of the Query i pass to the report.