Access: How to execute a query and save its result in a report

Question

HI,

I am trying to write a query in vba and to save its result in a report. I am a beginner. this is what i have tried can somebody correct me

Dim cn As New ADODB.Connection, rs As New ADODB.Recordset
Dim sql As String

Set cn = CurrentProject.Connection
sql = "Select * from table1 where empno is 0"

rs.Open sql, cn


While Not rs.EOF

' here i think i should save the result in a report but i am not sure how

  rs.MoveNext
Wend
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing

Also how do i change this query to run this on all tables in a database

Answer 1

Normally you would design the report based on a data source. Then after your report is done and working properly you use VBA to display or save the report.

Answer 2

To run this for each table in the database, I'd suggest writing a function that looped through CurrentData.AllTables(i) and then called your function above in each iteration

Hope this helps

Answer 3

If you want to simply view the results, you can create a query. For example, here is some rough, mostly untested VBA:

Sub ViewMySQL
Dim strSQL as String
Dim strName As String

'Note that this is not sensible in that you
'will end up with as many queries open as there are tables

    For Each tdf In CurrentDB.TableDefs
       If Left(tdf.Name,4)<>"Msys" Then
          strName = "tmp" & tdf.Name

          strSQL = "Select * from [" & tdf.Name & "] where empno = 0"
          UpdateQuery strName, strSQL
          DoCmd.OpenQuery strName, acViewNormal
       End If
    Next
End Sub

Function UpdateQuery(QueryName, SQL)

    If IsNull(DLookup("Name", "MsysObjects", "Name='" & QueryName & "'")) Then
        CurrentDb.CreateQueryDef QueryName, SQL
    Else
        CurrentDb.QueryDefs(QueryName).SQL = SQL
    End If

    UpdateQuery = True

End Function

You may also be interested in MDB Doc, an add-in for Microsoft Access 97-2003 that allows you to document objects/properties within an Access database to an HTML file.

-- http://mdbdoc.sourceforge.net/

Answer 4

It's not entirely clear to me what you want to do. If you want to view the results of SQL statement, you'd create a form and set its recordsource to "Select * from table1 where empno is 0". Then you could view the results one record at a time.

If that's not what you want, then I'm afraid I just don't have enough information to answer your question.

From what you have said so far, I don't see any reason why you need VBA or a report, since you just want to view the data. A report is for printing, a form is for viewing and editing. A report is page-oriented and not that easy to navigate, while a form is record-oriented, and allows you to edit the data (if you want to).

More information about what you want to accomplish will help us give you better answers.