Solved

get Access report properties using a VB app?

Posted on 2002-04-21
22
350 Views
Last Modified: 2013-11-25
HI experts

below i have included some code that worked in Access, but i am not sure how i can do the same thing in my visual basic application ?????

i am currently connecting to a access database via ADO and get data from a table....but i would also like to get the sortby,groupby values from the reports in the access database?

Dim frm As Form, prp As Property 'rpt As Report
   Dim obj As AccessObject, dbs As Object
   Dim GrpOn, Grpkeeptog, GrpInterval
   Dim i As Long
   Dim rptgrouplevel As String
   Dim isGroupLevel As Variant
   
   'set the dbs to the current application, which in this case is a access database
   Set dbs = Application.CurrentProject
   
   'For each report in the database
   For Each obj In dbs.AllReports
   
       DoCmd.OpenReport obj.Name, acViewDesign
       If obj.IsLoaded = True Then
           
       
            'Identifies the sort order for each field
         For i = 0 To 9
   
             'If not((reports(0).grouplevel(i)) isnothing) then 'not work!!!
                If (reports(0).grouplevel(i).SortOrder) = False Then
                    MsgBox ("The Field " + Str(i) + " is in Descending Order")
                Else
                    MsgBox ("The Field " + Str(i) + " is in Ascending Order")
                End If
             'End If
         Next i

       
        DoCmd.Close acReport, obj.Name, acSaveNo
      End If

0
Comment
Question by:onegui
  • 12
  • 9
22 Comments
 
LVL 4

Expert Comment

by:RichW
ID: 6957604
Here's a class with some helpful Access methods.

It's a bit long, but I've found it very helpful to get things done.

There's an example of how you can use a lot of the methods, including opening and printing a report, at the bottom.

Just tweak the paths and filenames and you're good to go.
RichW

' Class       : CAccess97
' Description : Class for working with Microsoft Access 97 through Automation

'
Public Enum EnumAccess97Types
  A97Table = 0
  A97Query = 1
  A97Form = 2
  A97Report = 3
  A97Macro = 4
  A97Module = 5
End Enum

' The following define the various modes that Access objects can be opened
' with through the user interface.
' Object    Valid Modes
' ---------------------------------
' Table     Normal, Design, Preview
' Query     Normal, Design, Preview
' Form      Normal, Design, Preview
' Report    Normal (means Print), Design, Preview
' Macro     Design
' Module    Design, PrintPreview
Public Enum EnumAccess97ViewModes
  A97VM_Normal = 0
  A97VM_Design = 1
  A97VM_PrintPreview = 2
End Enum

' The following enum defines the various output formats available
' for Access objects. Note that not all formats are available
' for all objects.
' Object     Valid Formats
' -------   ------------------------
' Table     HTML, IIS, RichText, Text, Excel
' Query     HTML, IIS, RichText, Text, Excel
' Form      HTML, IIS, RichText, Text, Excel
' Report    HTML, RichText, Text, Excel
' Macro     None
' Module    Text
'
Public Enum EnumAccess97OutputFormats
  A97OF_HTML = 1
  A97OF_IIS = 2
  A97OF_RichText = 3
  A97OF_Text = 4
  A97OF_Excel = 5
End Enum

' The following constants define the strings that Access expects
' for the OutputTo action
Private Const mcstrOutput_HTML As String = "HTML (*.html)"
Private Const mcstrOutput_IIS As String = "Microsoft IIS (*.htx, *.idc)"
Private Const mcstrOutput_RichText As String = "Rich Text Format (*.rtf)"
Private Const mcstrOutput_Text As String = "MS-DOS Text (*.txt)"
Private Const mcstrOutput_Excel As String = "Microsoft Excel (*.xls)"

' The following enum defines the various import/export formats
' available.
Public Enum EnumAccess97IMEXTypes
  A97IMEX_Access = 1
  A97IMEX_Access2 = 2
  A97IMEX_dBASE3 = 3
  A97IMEX_dBASE4 = 4
  A97IMEX_dBASE5 = 5
  A97IMEX_FoxPro20 = 6
  A97IMEX_FoxPro25 = 7
  A97IMEX_FoxPro26 = 8
  A97IMEX_FoxPro30 = 9
  A97IMEX_FoxProDBC = 10
  A97IMEX_Paradox3 = 11
  A97IMEX_Paradox4 = 12
  A97IMEX_Paradox5 = 13
End Enum

' The following constants define the strings that Access expects
' for the TransferDatabase action
Private Const mcstrIMEX_Access As String = "Microsoft Access"
Private Const mcstrIMEX_Access2 As String = "Jet 2.x"
Private Const mcstrIMEX_dBASE3 As String = "dBASE III"
Private Const mcstrIMEX_dBASE4 As String = "dBASE IV"
Private Const mcstrIMEX_dBASE5 As String = "dBASE V"
Private Const mcstrIMEX_FoxPro2 As String = "Foxpro 2.0"
Private Const mcstrIMEX_FoxPro25 As String = "FoxPro 2.5"
Private Const mcstrIMEX_FoxPro26 As String = "FoxPro 2.6"
Private Const mcstrIMEX_FoxPro30 As String = "FoxPro 3.0"
Private Const mcstrIMEX_FoxProDBC As String = "FoxPro DBC"
Private Const mcstrIMEX_Paradox3 As String = "Paradox 3.x"
Private Const mcstrIMEX_Paradox4 As String = "Paradox 4.x"
Private Const mcstrIMEX_Paradox5 As String = "Paradox 5.x"

Private m_dbsCurrent As Object
Private m_appAccess As Access.Application
Private m_intCountTables As Integer
Private m_intCountQueries As Integer
Private m_intCountForms As Integer
Private m_intCountReports As Integer
Private m_intCountMacros As Integer
Private m_intCountModules As Integer

Public Property Get AccessPath() As String
  ' Comments  : Returns the path where Access is installed
  '
  On Error GoTo PROC_ERR
 
  AccessPath = m_appAccess.SysCmd(acSysCmdAccessDir)
 
PROC_EXIT:
  Exit Property
 
PROC_ERR:
  MsgBox "Error: " & Err.Number & ". " & Err.Description, , _
    "AccessPath"
  Resume PROC_EXIT
 
End Property

Public Property Get AccessVersion() As String
  ' Comments  : Returns the version number of Access
  '
  On Error GoTo PROC_ERR
 
  AccessVersion = m_appAccess.SysCmd(acSysCmdAccessVer)
 
PROC_EXIT:
  Exit Property
 
PROC_ERR:
  MsgBox "Error: " & Err.Number & ". " & Err.Description, , _
    "AccessVersion"
  Resume PROC_EXIT
   
End Property

Public Property Get AppAccess() As Access.Application
  ' Comments  : Returns a handle to the Access application

  Set AppAccess = m_appAccess
 
End Property

Public Property Get CountOfForms() As Integer
  ' Comments  : Returns the count of forms
  '
  CountOfForms = m_intCountForms
 
End Property

Public Property Get CountOfMacros() As Integer
  ' Comments  : Returns the count of macros
  '
  CountOfMacros = m_intCountMacros
 
End Property

Public Property Get CountOfModules() As Integer
  ' Comments  : Returns the count of modules
  '
  CountOfModules = m_intCountModules
 
End Property

Public Property Get CountOfQueries() As Integer
  ' Comments  : Returns the count of queries
  '
  CountOfQueries = m_intCountQueries
 
End Property

Public Property Get CountOfReports() As Integer
  ' Comments  : Returns the count of reports
  '
  CountOfReports = m_intCountReports
 
End Property

Public Property Get CountOfTables() As Integer
  ' Comments  : Returns the count of table objects
  '
  CountOfTables = m_intCountTables
 
End Property

Public Property Get CurrentDBS() As DAO.Database
  ' Comments  : Returns a handle to the current open database
  '
  Set CurrentDBS = m_dbsCurrent
 
End Property

Public Property Get Forms(index As Integer) As String
  ' Comments  : returns Form x
  ' Parameters: index - position in the collection (0-based)
  '
  Forms = m_dbsCurrent.Containers!Forms.Documents(index).Name
 
End Property

Public Property Get Macros(index As Integer) As String
  ' Comments  : returns macro x
  ' Parameters: index - position in the collection (0-based)
  '
  Macros = m_dbsCurrent.Containers!Scripts.Documents(index).Name
 
End Property

Public Property Get Modules(index As Integer) As String
  ' Comments  : returns module x
  ' Parameters: index - position in the collection (0-based)
  '
  Modules = m_dbsCurrent.Containers!Modules.Documents(index).Name
 
End Property

Public Property Get Queries(index As Integer) As String
  ' Comments  : returns Query x
  ' Parameters: index - position in the collection (0-based)
  '
  Queries = m_dbsCurrent.QueryDefs(index).Name
 
End Property

Public Property Get Reports(index As Integer) As String
  ' Comments  : returns Report x
  ' Parameters: index - position in the collection (0-based)
  '
  Reports = m_dbsCurrent.Containers!Reports.Documents(index).Name
 
End Property

Public Property Get Tables(index As Integer) As String
  ' Comments  : returns table x
  ' Parameters: index - position in the collection (0-based)
  '
  Tables = m_dbsCurrent.TableDefs(index).Name
 
End Property

Public Property Get WorkgroupPath() As String
  ' Comments  : Returns the path to the current workgroup
  '             information file in use by Access.
  '
  On Error GoTo PROC_ERR
 
  WorkgroupPath = m_appAccess.SysCmd(acSysCmdGetWorkgroupFile)
 
PROC_EXIT:
  Exit Property
 
PROC_ERR:
  MsgBox "Error: " & Err.Number & ". " & Err.Description, , _
    "WorkgroupPath"
  Resume PROC_EXIT
 
End Property

Public Sub CloneAccessObject( _
  strSourceName As String, _
  strNewName As String, _
  eObjType As EnumAccess97Types)
  ' Comments  : Copies the specified object to a new name
  ' Parameters: strSourceName - name of object to copy
  '             strNewName - name of for the new object
  '             eObjType - object type as defined by the EnumAccess97Types
  '             enumerated type
  ' Returns   : Nothing
  '
  On Error GoTo PROC_ERR

  ' Specifying a blank value for the Database parameter forces
  ' CopyObject to copy in the same database
  m_appAccess.DoCmd.CopyObject _
    "", _
    strNewName, _
    eObjType, _
    strSourceName
   
PROC_EXIT:
  Exit Sub
 
PROC_ERR:
  MsgBox "Error: " & Err.Number & ". " & Err.Description, , _
    "CloneAccessObject"
  Resume PROC_EXIT
 
End Sub

Public Sub CloseAccess()
  ' Comments  : Closes the instance of Access
  ' Parameters: None
  ' Returns   : Nothing
  '
  On Error GoTo PROC_ERR
 
  ' Only close it its already open
  If Not (m_appAccess Is Nothing) Then
    ' Check to see if there is a database open
    If Not (m_appAccess.CurrentDb Is Nothing) Then
      m_appAccess.CloseCurrentDatabase
      m_appAccess.Quit
    End If
    Set m_appAccess = Nothing
   
  End If
 
PROC_EXIT:
  Exit Sub
 
PROC_ERR:
  MsgBox "Error: " & Err.Number & ". " & Err.Description, , _
    "CloseAccess"
  Resume PROC_EXIT
 
End Sub

Public Sub CloseDatabase()
  ' Comments  : Closes the current open database
  ' Parameters: None
  ' Returns   : Nothing
  '
  On Error GoTo PROC_ERR
 
  If Not (m_dbsCurrent Is Nothing) Then
    m_dbsCurrent.Close
    Set m_dbsCurrent = Nothing
  End If
 
PROC_EXIT:
  Exit Sub
 
PROC_ERR:
  MsgBox "Error: " & Err.Number & ". " & Err.Description, , _
    "CloseDatabase"
  Resume PROC_EXIT
 
End Sub

Public Sub CloseObjectOnScreen( _
  strName As String, _
  eType As EnumAccess97Types, _
  fSave As Boolean)
  ' Comments  : Closes the named object on the screen
  ' Parameters: strName - name of the object to close
  '             eType - object type as defined by the EnumAccess97Types
  '             enumerated type
  '             fSave - True to save changes, False to prompt before
  '             saving changes
  ' Returns   : Nothing
  '
  Dim lngSave As Long
 
  On Error GoTo PROC_ERR
 
  ' Map the input boolean to the three state Access value
  If fSave Then
    lngSave = AcCloseSave.acSaveYes
  Else
    lngSave = AcCloseSave.acSavePrompt
  End If
 
  m_appAccess.DoCmd.Close eType, strName, lngSave
 
PROC_EXIT:
  Exit Sub
 
PROC_ERR:
  MsgBox "Error: " & Err.Number & ". " & Err.Description, , _
    "CloseObjectOnScreen"
  Resume PROC_EXIT
 
End Sub

Public Sub CopyAccessObject( _
  strDestinationDatabase As String, _
  strSourceName As String, _
  strNewName As String, _
  eObjType As EnumAccess97Types)
  ' Comments  : Copies the specified object from the current database
  '             to another database.
  ' Parameters: strDestinationDatabase - path and name of the database to
  '             copy the object to
  '             strSourceName - name of object to copy
  '             strNewName - name of for the new object
  '             eObjType - object type as defined by the EnumAccess97Types
  '             enumerated type
  ' Returns   : Nothing
  '
  On Error GoTo PROC_ERR

  m_appAccess.DoCmd.CopyObject _
    strDestinationDatabase, _
    strNewName, _
    eObjType, _
    strSourceName
   
PROC_EXIT:
  Exit Sub
 
PROC_ERR:
  MsgBox "Error: " & Err.Number & ". " & Err.Description, , _
    "CopyAccessObject"
  Resume PROC_EXIT
 
End Sub

Public Sub DeleteObject(strName As String, eObjType As EnumAccess97Types)
  ' Comments  : Deletes the named object from the database
  ' Parameters: strName - name of the object to delete
  '             eObjType - type of the object as defined by the
  '             EnumAccess97Types enumerated type
  ' Returns   : Nothing
  '
  On Error GoTo PROC_ERR
 
  m_appAccess.DoCmd.DeleteObject eObjType, strName
 
PROC_EXIT:
  Exit Sub
 
PROC_ERR:
  MsgBox "Error: " & Err.Number & ". " & Err.Description, , _
    "DeleteObject"
  Resume PROC_EXIT
 
End Sub

Public Sub ExecuteSQL(strSQL As String, _
  fTransaction As Boolean, _
  fWarning As Boolean)
  ' Comments  : Executes the passed SQL string.
  ' Parameters: strSQL - SQL string to execute, must be a valid
  '             Access/Jet action query that doesn't return rows.
  '             fTransaction - True to wrap the query in a transaction
  '             False otherwise.
  '             fWarning - True to show warnings, False otherwise
  ' Returns   : Nothing
  '
  On Error GoTo PROC_ERR
 
  ' Turn warning dialogs off is specified
  If Not fWarning Then
    m_appAccess.DoCmd.SetWarnings False
  End If
 
  ' Execute the query
  m_appAccess.DoCmd.RunSQL strSQL, fTransaction
 
  ' Restore warning dialogs setting
  If Not fWarning Then
    m_appAccess.DoCmd.SetWarnings True
  End If
 
PROC_EXIT:
  Exit Sub
 
PROC_ERR:
  MsgBox "Error: " & Err.Number & ". " & Err.Description, , _
    "ExecuteSQL"
  Resume PROC_EXIT
 
End Sub

Public Sub ExportObject( _
  strSourceObject As String, _
  eSourceType As EnumAccess97Types, _
  eExportType As EnumAccess97IMEXTypes, _
  strDestDatabase As String, _
  Optional fStructureOnly As Boolean = False)
  ' Comments  : Exports the named object from the current database to another
  '             database in the specified format.
  ' Parameters: strSourceObject - name of the object to export
  '             eSourceType - source object type as defined by the
  '             EnumAccess97Types
  '             enumerated type
  '             eExportType - format for the export as specified by the
  '             EnumAccess97IMEXTypes enumerated type
  '             strDestDatabase - full path and name of the database to export
  '             to
  '             fStructureOnly - Optional, set to True to export only the
  '             structure and no data. This option is only valid for tables.
  ' Returns   : Nothing
  '
  Dim strType As String
 
  On Error GoTo PROC_ERR
 
  ' Get the appropriate export string that Access expects
  Select Case eExportType
   
    Case A97IMEX_Access
      strType = mcstrIMEX_Access
    Case A97IMEX_Access2
      strType = mcstrIMEX_Access2
    Case A97IMEX_dBASE3
      strType = mcstrIMEX_dBASE3
    Case A97IMEX_dBASE4
      strType = mcstrIMEX_dBASE4
    Case A97IMEX_dBASE5
      strType = mcstrIMEX_dBASE5
    Case A97IMEX_FoxPro20
      strType = mcstrIMEX_FoxPro2
    Case A97IMEX_FoxPro25
      strType = mcstrIMEX_FoxPro25
    Case A97IMEX_FoxPro26
      strType = mcstrIMEX_FoxPro26
    Case A97IMEX_FoxPro30
      strType = mcstrIMEX_FoxPro30
    Case A97IMEX_FoxProDBC
      strType = mcstrIMEX_FoxProDBC
    Case A97IMEX_Paradox3
      strType = mcstrIMEX_Paradox3
    Case A97IMEX_Paradox4
      strType = mcstrIMEX_Paradox4
    Case A97IMEX_Paradox5
      strType = mcstrIMEX_Paradox5
     
  End Select
 
  ' Do the export
  m_appAccess.DoCmd.TransferDatabase _
    acExport, _
    strType, _
    strDestDatabase, _
    eSourceType, _
    strSourceObject, _
    strSourceObject, _
    fStructureOnly
   
PROC_EXIT:
  Exit Sub
 
PROC_ERR:
  MsgBox "Error: " & Err.Number & ". " & Err.Description, , _
    "ExportObject"
  Resume PROC_EXIT
 
End Sub

Public Sub ImportObject( _
  strSourceObject As String, _
  eSourceType As EnumAccess97Types, _
  eImportType As EnumAccess97IMEXTypes, _
  strSourceDatabase As String)
  ' Comments  : Imports the named object into the current database
  ' Parameters: strSourceObject - name of the object to import
  '             eSourceType - source object type as defined by the
  '             EnumAccess97Types enumerated type
  '             eExportType - format for the import as specified by the
  '             EnumAccess97IMEXTypes enumerated type
  '             strSourceDatabase - full path and name of the database
  '             to import from
  ' Returns   : Nothing
  '
  Dim strType As String
 
  On Error GoTo PROC_ERR
 
  ' Get the appropriate export string that Access expects
  Select Case eImportType
   
    Case A97IMEX_Access
      strType = mcstrIMEX_Access
    Case A97IMEX_Access2
      strType = mcstrIMEX_Access2
    Case A97IMEX_dBASE3
      strType = mcstrIMEX_dBASE3
    Case A97IMEX_dBASE4
      strType = mcstrIMEX_dBASE4
    Case A97IMEX_dBASE5
      strType = mcstrIMEX_dBASE5
    Case A97IMEX_FoxPro20
      strType = mcstrIMEX_FoxPro2
    Case A97IMEX_FoxPro25
      strType = mcstrIMEX_FoxPro25
    Case A97IMEX_FoxPro26
      strType = mcstrIMEX_FoxPro26
    Case A97IMEX_FoxPro30
      strType = mcstrIMEX_FoxPro30
    Case A97IMEX_FoxProDBC
      strType = mcstrIMEX_FoxProDBC
    Case A97IMEX_Paradox3
      strType = mcstrIMEX_Paradox3
    Case A97IMEX_Paradox4
      strType = mcstrIMEX_Paradox4
    Case A97IMEX_Paradox5
      strType = mcstrIMEX_Paradox5
     
  End Select
 
  ' Do the export
  m_appAccess.DoCmd.TransferDatabase _
    acImport, _
    strType, _
    strSourceDatabase, _
    eSourceType, _
    strSourceObject, _
    strSourceObject
   
PROC_EXIT:
  Exit Sub
 
PROC_ERR:
  MsgBox "Error: " & Err.Number & ". " & Err.Description, , _
    "ImportObject"
  Resume PROC_EXIT
 
End Sub

Public Sub OpenAndPrint( _
  strName As String, _
  eType As EnumAccess97Types, _
  intPageFrom As Integer, _
  intPageTo As Integer, _
  intCopies As Integer, _
  fCollate As Boolean)
  ' Comments  : Opens the named object, prints it with the options specified
  '             and closes it.
  ' Parameters: strName - name of the object to print
  '             eType - object type as defined by the EnumAccess97Types
  '             enumerated type
  '             intPageFrom - page to start from
  '             intPageTo - page to print to, or 9999 for all pages
  '             intCopies - number of copies to print
  '             fCollate - True to collate copies, False otherwise
  ' Returns   : Nothing
  '
  On Error GoTo PROC_ERR
 
  ' First, open the object
  Me.OpenObjectOnScreen strName, eType, A97VM_PrintPreview
 
  ' Now send the printout command
  m_appAccess.DoCmd.PrintOut acPages, intPageFrom, intPageTo, , intCopies, fCollate
 
  ' Now close the object
  Me.CloseObjectOnScreen strName, eType, True
 
PROC_EXIT:
  Exit Sub
 
PROC_ERR:
  MsgBox "Error: " & Err.Number & ". " & Err.Description, , _
    "OpenAndPrint"
  Resume PROC_EXIT
   
End Sub

Public Sub OpenDatabase( _
  strDatabase As String, _
  fExclusive As Boolean)
  ' Comments  : Opens the specified database in the current
  '             instance of Access.
  ' Parameters: strDatabase - path and name of the database file
  '             fExclusive - True to open the database in exclusive
  '             mode, False to open in shared mode.
  ' Returns   : Nothing
  '
  On Error GoTo PROC_ERR
 
  m_appAccess.OpenCurrentDatabase strDatabase, fExclusive
 
  Set m_dbsCurrent = m_appAccess.CurrentDb

  ' Update the object counts
  UpdateCounts False
 
PROC_EXIT:
  Exit Sub
 
PROC_ERR:
  MsgBox "Error: " & Err.Number & ". " & Err.Description, , _
    "OpenDatabase"
  Resume PROC_EXIT
 
End Sub

Public Sub OpenObjectOnScreen( _
  strName As String, _
  eType As EnumAccess97Types, _
  eMode As EnumAccess97ViewModes, _
  Optional varExtra As Variant)
  ' Comments  : Opens the named object within Access.
  '             Note that this is a user interface command because
  '             it merely asks Access to open the object on the screen--
  '             said object cannot be refered to programmatically.
  ' Parameters: strName - name of the object to open
  '             eType - type of the object as defined by the
  '             EnumAccess97Types enumerated type
  '             eMode - mode to open in as defined by the
  '             EnumAccess97ViewModes enumerated type
  ' Returns   : Nothing
  '
  On Error GoTo PROC_ERR

  Select Case eType
    Case A97Table
      m_appAccess.DoCmd.OpenTable strName, eMode
     
    Case A97Query
      m_appAccess.DoCmd.OpenQuery strName, eMode
     
    Case A97Form
      m_appAccess.DoCmd.OpenForm strName, eMode
     
    Case A97Report
      m_appAccess.DoCmd.OpenReport strName, eMode
     
    Case A97Macro
      m_appAccess.DoCmd.RunMacro strName, eMode
     
    Case A97Module
      m_appAccess.DoCmd.OpenModule strName, eMode
     
  End Select
 
PROC_EXIT:
  Exit Sub
 
PROC_ERR:
  MsgBox "Error: " & Err.Number & ". " & Err.Description, , _
    "OpenObjectOnScreen"
  Resume PROC_EXIT

End Sub

Public Sub OutputObject( _
  strName As String, _
  eType As EnumAccess97Types, _
  eFormat As EnumAccess97OutputFormats, _
  strFile As String, _
  Optional fAutoStart = False)
  ' Comments  : Sends the specified object to a file in the specified format
  ' Parameters: strName - name of the object to output
  '             eType - object type as defined by the EnumAccess97Types
  '             enumerated type
  '             eFormat - output format as defined by the
  '             EnumAccess97OutputFormats enumerated type
  '             strFile - name of the output file. If the file exists, it is
  '             overwritten
  '             fAutoStart - Optional, set to True to launch the program
  '             associated with the output format.
  ' Returns   : Nothing
  '
  Dim strFormat As String
 
  On Error GoTo PROC_ERR
 
  ' Get the correct text string
  Select Case eFormat
   
    Case A97OF_Excel
      strFormat = mcstrOutput_Excel
     
    Case A97OF_HTML
      strFormat = mcstrOutput_HTML
     
    Case A97OF_IIS
      strFormat = mcstrOutput_IIS
     
    Case A97OF_RichText
      strFormat = mcstrOutput_RichText
     
    Case A97OF_Text
      strFormat = mcstrOutput_Text
   
    Case Else
      ' invalid type
      strFormat = ""
     
  End Select
 
  If strFormat <> "" Then
    m_appAccess.DoCmd.OutputTo eType, strName, strFormat, strFile, fAutoStart
  End If
 
PROC_EXIT:
  Exit Sub
 
PROC_ERR:
  MsgBox "Error: " & Err.Number & ". " & Err.Description, , _
    "OutputObject"
  Resume PROC_EXIT
 
End Sub

Public Sub RefreshCounts()
  ' Comments  : Refreshes the object counts
  ' Parameters: None
  ' Returns   : Nothing
  '
  UpdateCounts True
 
End Sub

Public Sub RenameObject( _
  strName As String, _
  eType As EnumAccess97Types, _
  strNewName As String)
  ' Comments  : Renames the specified object
  ' Parameters: strName - name of the object to rename
  '             eType - object type as defined by the EnumAccess97Types
  '             enumerated type
  '             strNewName - name to rename to
  ' Returns   : Nothing
  '
  On Error GoTo PROC_ERR
 
  m_appAccess.DoCmd.Rename strNewName, eType, strName
 
PROC_EXIT:
  Exit Sub
 
PROC_ERR:
  MsgBox "Error: " & Err.Number & ". " & Err.Description, , _
    "RenameObject"
  Resume PROC_EXIT
 
End Sub

Public Sub ShowCompactDialog()
  ' Comments  : Shows the Compact Database dialog. This procedure shows
  '             how to use the RunCommand action to programatically
  '             select any of the Access menu commands.
  ' Parameters: None
  ' Returns   : Nothing
  '
  On Error GoTo PROC_ERR
 
  m_appAccess.DoCmd.RunCommand acCmdCompactDatabase
 
PROC_EXIT:
  Exit Sub
 
PROC_ERR:
  MsgBox "Error: " & Err.Number & ". " & Err.Description, , _
    "ShowCompactDialog"
  Resume PROC_EXIT
   
End Sub

Public Sub StartAccess()
  ' Comments  : Starts an instance of Access
  ' Parameters: None
  ' Returns   : Nothing
  '
  On Error GoTo PROC_ERR
 
  Set m_appAccess = New Access.Application
 
PROC_EXIT:
  Exit Sub
 
PROC_ERR:
  MsgBox "Error: " & Err.Number & ". " & Err.Description, , _
    "StartAccess"
  Resume PROC_EXIT
 
End Sub

Private Sub UpdateCounts(fRefresh As Boolean)
  ' Comments  : Updates the class object counts
  ' Parameters: fRefresh - True to refresh the collection first
  ' Returns   : Nothing
  '
  On Error GoTo PROC_ERR
 
  If fRefresh Then
    m_dbsCurrent.TableDefs.Refresh
    m_dbsCurrent.QueryDefs.Refresh
    m_dbsCurrent.Containers!Forms.Documents.Refresh
    m_dbsCurrent.Containers!Reports.Documents.Refresh
    m_dbsCurrent.Containers!Scripts.Documents.Refresh
    m_dbsCurrent.Containers!Modules.Documents.Refresh
  End If
 
  m_intCountTables = m_dbsCurrent.TableDefs.Count
  m_intCountQueries = m_dbsCurrent.QueryDefs.Count
  m_intCountForms = m_dbsCurrent.Containers!Forms.Documents.Count
  m_intCountReports = m_dbsCurrent.Containers!Reports.Documents.Count
  m_intCountMacros = m_dbsCurrent.Containers!Scripts.Documents.Count
  m_intCountModules = m_dbsCurrent.Containers!Modules.Documents.Count
 
PROC_EXIT:
  Exit Sub
 
PROC_ERR:
  MsgBox "Error: " & Err.Number & ". " & Err.Description, , _
    "UpdateCounts"
  Resume PROC_EXIT
   
End Sub





' Example code for the CAccess97 class
'
' To use this example:
' 1. Create a new form.
' 2. Create a command button called cmdTest
' 3. Paste the entire contents of this module into the
'    new form's module.
'
' This example assumes that the sample files are located in the
' directory named by the following constant.
Private Const mcstrExamplePath As String = "c:\MyFolder\"

Private Sub cmdTest_Click()
  Dim intCounter As Integer
  Dim intCount As Integer
  Dim AccessTest As CAccess97
  Dim strFile As String
  Const cstrDBCurrent As String = mcstrExamplePath & "INTEROP.MDB"
  Const cstrDBOther As String = mcstrExamplePath & "SAMPLE.MDB"
  Const cstrFile As String = mcstrExamplePath & "TESTOUT.RTF"
 
  ' Before continuing, we want to be sure that the computer doesn't have
  ' an existing instance of Excel running. This is so our example code
  ' doesn't erroneously interact with the wrong instance of Excel and
  ' cause problems.
 
  If MsgBox("This example instantiates Access 97. Before continuing, " & _
    "please close any copies of Access that are running. " & vbCrLf & _
    vbCrLf & "Do you want to continue?", _
    vbYesNo + vbQuestion) = vbYes Then
   
    ' Continue on with the example.
   
    Set AccessTest = New CAccess97
    Debug.Print "CAccess97 class instantiated."
       
    ' Start Access and open the database
    AccessTest.StartAccess
    Debug.Print "StartAccess(): Access opened."
   
    AccessTest.OpenDatabase cstrDBCurrent, False
    Debug.Print "OpenDatabase(): database opened."
   
    ' Show the tables
    intCount = AccessTest.CountOfTables
    Debug.Print "CountOfTables(): " & intCount & " tables in Database:"
    For intCounter = 0 To intCount - 1
      Debug.Print AccessTest.Tables(intCounter)
    Next intCounter
   
    ' Show the queries
    intCount = AccessTest.CountOfQueries
    Debug.Print "CountOfQueries(): " & intCount & " queries in Database:"
    For intCounter = 0 To intCount - 1
      Debug.Print AccessTest.Queries(intCounter)
    Next intCounter
   
     ' Show the forms
    intCount = AccessTest.CountOfForms
    Debug.Print "CountOfForms(): " & intCount & " forms in Database:"
    For intCounter = 0 To intCount - 1
      Debug.Print AccessTest.Forms(intCounter)
    Next intCounter
   
     ' Show the reports
    intCount = AccessTest.CountOfReports
    Debug.Print "CountOfReports(): " & intCount & " reports in Database:"
    For intCounter = 0 To intCount - 1
      Debug.Print AccessTest.Reports(intCounter)
    Next intCounter
   
     ' Show the macros
    intCount = AccessTest.CountOfMacros
    Debug.Print "CountOfMacros(): " & intCount & " macros in Database:"
    For intCounter = 0 To intCount - 1
      Debug.Print AccessTest.Macros(intCounter)
    Next intCounter
   
     ' Show the modules
    intCount = AccessTest.CountOfModules
    Debug.Print "CountOfModules(): " & intCount & " modules in Database:"
    For intCounter = 0 To intCount - 1
      Debug.Print AccessTest.Modules(intCounter)
    Next intCounter
     
    ' Open the Categories table and then close it
    AccessTest.OpenObjectOnScreen _
      "Categories", _
      A97Table, _
      A97VM_Normal
    Debug.Print "OpenObjectOnScreen(): opened a table."
   
    AccessTest.CloseObjectOnScreen _
      "Categories", _
      A97Table, _
      True
    Debug.Print "CloseObjectOnScreen(): closed the table."
   
    ' Make a copy of the Customers table and call it Customers1.
    AccessTest.CloneAccessObject _
      "Customers", _
      "Customers1", _
      A97Table
    Debug.Print "CloneAccessObject(): clone table in database."
   
    ' Copy the frmSample1 Form to another database
    AccessTest.CopyAccessObject _
      cstrDBOther, _
      "frmSample1", _
      "frmSample1", _
      A97Form
    Debug.Print "CopyAccessObject(): copied form to another database."
   
    ' Execute a query to empty the orders table
    AccessTest.ExecuteSQL _
      "DELETE * FROM [Customers];", _
      True, _
      False
    Debug.Print "ExecuteSQL(): executed a SQL statement."
   
    ' Delete the Customers1 table
    AccessTest.DeleteObject _
      "Customers1", _
      A97Table
    Debug.Print "DeleteObject(): deleted a table."
   
    ' Import a table into the current database
    AccessTest.ImportObject _
      "Categories", _
      A97Table, _
      A97IMEX_Access, _
      cstrDBOther
    Debug.Print "ImportObject(): imported a table."
   
    ' Open the Invoice report and print it
    AccessTest.OpenAndPrint _
      "Invoice", _
      A97Report, _
      1, _
      99, _
      1, _
      False
    Debug.Print "OpenAndPrint(): opened a report and printed it."
   
    ' Send the Customers table data to a rich text file
    On Error Resume Next
    Kill strFile
    On Error GoTo 0
    AccessTest.OutputObject _
      "Customers", _
      A97Table, _
      A97OF_RichText, _
      cstrFile, _
      False
    Debug.Print "OutputObject(): exported table data to an RTF file."
   
    ' Rename the Suppliers macro and then rename it back
    AccessTest.RenameObject "Suppliers", A97Macro, "test"
    AccessTest.RenameObject "test", A97Macro, "Suppliers"
    Debug.Print "RenameObject(): renamed a macro."
   
    ' Show the compact database dialog. Note that if you press [Cancel]
    ' from the Access compact dialog (or any other dialog), Access will
    ' raise an error. The ShowCompactDialog  procedure displays the error.
    AccessTest.ShowCompactDialog
    Debug.Print "ShowCompactDialog(): displayed the Access Compact dialog."
   
      ' Close everything down
    AccessTest.CloseDatabase
    Debug.Print "CloseDatabase(): database closed."
   
    AccessTest.CloseAccess
    Debug.Print "CloseAccess(): instance of Access closed."
   
    Set AccessTest = Nothing
    Debug.Print "Class de-instantiated."
   
  Else
    Debug.Print "Example not run."
  End If
 
End Sub
0
 

Author Comment

by:onegui
ID: 6958240
HI expert
thanks for your input..

Youre right this is very long...

Plus...Are you telling me there is nothing similiar to my
access code that i can use to loop through all the reports easily...

i couldn't find the code that enables me to get my values, for example....

I am intersted in recordsource, tablenames, groupbys, sortbys,orderby, keeptogether e.t.c. All the properties associated with the grouplevel object ususally found in access??

cheers
0
 
LVL 6

Expert Comment

by:pierrecampe
ID: 6958399
ping
0
 

Author Comment

by:onegui
ID: 6958466
what does ping mean?
0
 

Author Comment

by:onegui
ID: 6958833
Can anyone tell me of it is at all possible for
me to get the groupby, sortby e.t.c. values as highlighted above without putting the code in a form in access as i have stated above??

Thanks
0
 
LVL 4

Expert Comment

by:RichW
ID: 6959527
When you say groupby, sortby, are you speaking of the properties of the report?  If so, why not just run the report from VB?

Set your Access query as the recordsource of the report that already has the groupby properties and run i from VB.

0
 

Author Comment

by:onegui
ID: 6959552
HI RichW

thanks for enlightening me Rich..

Unfortunately, maybe ude to lack of sleep trying to solve this problem....

I am not sure what you are trying to explain??

Yes..i am trying to get the properties of all the reports in a access database via a VB application..

If you look at the first message, it shows how i was able to do this using VBA inside a access form..

You say .....
"Set your Access query as the recordsource of the report that already has the groupby properties and
run i from VB."

I assume you mean..
create a SQL query in VB with my current ADO connection which somehow...you have lost me mate...

could you please clarify...if this solves this, then i wont hesitate giving you the 200 points..

thanks onegui
0
 
LVL 4

Expert Comment

by:RichW
ID: 6959631
Ok.  In the Access database, go into the report you're trying to open.  I am assuming that the report already exists in the Access db.

In the properties of that report set the Recordsource property, at the top of the properties box, to an Access query that will pull the data from the tables just by opening the report.  In other words, create a query in Access that will give you the data you need to run the report.  The report's groupings and sortyby's should already be created, so all you need is the query set in the Recordsource property of the report. (By the way, you could also write a function in a Access Module that uses ADO and place the function's name in the Recordsource property for the report)

Now, you will have a report in the Access database that is ready to run.  All you need to do now is open that report from VB.

Now, all you need to do is open that report from VB using the code I gave you.

YES, you can use ADO to get a connection and recordset object, but why do that when in this case you can have the report already created, and all you need to do is open it?

You can set a reference to the Access Object Library in VB and open the report that way.  Technically, that's what my class does.

Why reinvent the wheel when you all you need to do is open the report.

If you need to do specific queries for the report we can do that too.

Are you trying to create a report outside of Access?  This can be done too, but it wold be much easier for you to run the report already created in Access from VB.

What version of Access are you using?
0
 

Author Comment

by:onegui
ID: 6961086
HI RichW.

Thanks for your comments....

Let me clarify my position..

I am not trying to re-invent the wheel but write a application that identifies the properties of the report, the same way that my original comment at the top outlined.

I do not want to open the report to view, because.....

The company has over 150 reports!!

Therefore the details of the report tells the customer, who has forgotten what each report does to quickly view each report from a listbox and see a summary of what each one does....

Can you see what i am trying to achieve here ??

looking forward to your reply,

onegui
0
 
LVL 4

Expert Comment

by:RichW
ID: 6961614
Forgive my ignorance, but when you say " the details of the report tells the customer", are you referring to the details section of the report?

I know you can't get the grouping and sort order of an Access report using ADO, but I'm pretty sure you can using the Access Object Library.

I will research more and get back to you.  Sorry.

RichW
0
 

Author Comment

by:onegui
ID: 6961898
HI Richw
thanks mate for your patience and staying with meo n this one, its much appreciated..

Like i said, i have developed a VB executable which so far opens a table found in a acccess appliaction using ADO.

The names of all the reports held in this table are then added to a list box. from here the user can click the report in the lsitbox that they want to view details about..

So far the only details i can display in a another listbox are that displays inforamtion about the report are:

The other fields in the table that stores the names of all the reports..

BUT..lie i have shown in my very first message, i was able to get the groupby, sortby, table name, recordsource using the accessobject..

These are the details that i would like to display as additional information, when the user clicks on the report in the listbox..

The format would be somthing like the following:

report summary:

Report Name: rptCustomer
Recordsource: sql = "select * from customer"
Groupon : name
sortby : Ascending
keeptogether: true

I was abel to do this in access using accessobjects, so i just want to do the same thing, but run it from within my VB applciation.

Because there is 150 + reports, i do not want to open them, i jsut want to loop through them allowing the user to view what are the properties as outlined that make up the report, i dont care about what it produces, its of no use as it will obviously be used by many different users using different databases..

This may seem like a crazy idea to you, but this company thinks its of real use to them..

cheers, i will wait for any help anyone can give?
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 4

Expert Comment

by:RichW
ID: 6962713
Here's how you can use the Access Object Library to get the properties of all reports.

Private Sub GetReports()
Dim accessProject As Access.Application
Dim strRecordSource As String
Dim intGroupKeepTogether As Integer
Dim strOrderBy As String
Dim rpt As Report
Dim strRptName
Set accessProject = New Access.Application
accessProject.OpenCurrentDatabase "c:\Path\DBName.mdb", False
For Each rpt In Reports
    strRptName = rpt.Name
    strRecordSource = rpt.RecordSource
    intGroupKeepTogether = rpt.GrpKeepTogether
    strOrderBy = rpt.Properties("OrderBy")
   
    ' Add to Listbox HERE with info above
Next

End Sub

Sorry it took so long to figure out what you were looking for.

You can get most of the properties you were looking for.  I didn't see one for GroupOn, but if you play around a little with the properties I'm sure you can find it.

If this STILL doesn't help let me know.

Good Luck

RichW
0
 

Author Comment

by:onegui
ID: 6967444
HI Richw

the objects below do not exist in VB do they?
These are only availbale inside MS access arent they?
i only get complier errors when i try to use this code as VB doesnot recognise the objects

accessProject As Access.Application
Dim rpt As Report

waiting for feedback

onegui
0
 
LVL 4

Expert Comment

by:RichW
ID: 6968243
You have to set a reference to the Access Object Library.

In VB click Project then References.  Go down the list until you fnd the Microsoft Access 8.0 Library.  (It might be a difference version number depending on which version of Access you're using)

Once you set the reference to the object library, the code will work.

The object library exposes your VB application to the Access object library, so that you can do anything in VB that you can do in Access.

RichW
0
 

Author Comment

by:onegui
ID: 6970896
HI Richw

thanks for your patience mate...

Look i set the reference straight away when you told me about itthe first time.

The access object is definetely there as it brings up the properties when you go.. accessproject.

however...when i add a watch on the reports, it does not even enter the for loop once...

i.e.
for each rpt in reports

so....it just goes to the end of the function...and there is definetely reports in the database..100 plus..

any ideas...
0
 

Author Comment

by:onegui
ID: 6972609
hiu richw..

i just wanted to add....

that i found that this method of getting properties didn't work for reports inside a access form as well, which is why i had to use the code displayed in my first comment.....

hmm..still no luck at this stage getting this too work..

will keep working on it...
0
 
LVL 4

Expert Comment

by:RichW
ID: 6973359
Ok, will the users be able to add reports to this db, or will you have control over that?

You can create a table that will hold the information and populate your form control from that table.

OR

Apparently you can only get report information on reports that are opened.  You can try opening the reports in design view hidden and see if you can then capture the properties by using the Count property as an index.

This should've been an easy one, but MS has its way of making things tougher.  lol

Sorry it took so long to get back.

If you can compact and zip the db up, send it to me at richpwelsh@yahoo.com

What version of Access are you using?


0
 

Author Comment

by:onegui
ID: 6973755
Hi Richw

no problems about the time delay mate, i'm just fortunate and appreciate that you are spending your time with me on this one...

I should clarify the over all functionality of this VB application since you ave raised a few issues..

Firsty..
Some of the reports in this database are stored in a report table called ttsreport.
I have already written code which queries the report table and displays the report description in a listbox.
The user can then click on the report description
and all the fields which are criteria for each report is d displayed in another listbox..

Because not all reports are stored in this report table(for some business reason), but alot are..

i need to also check for reports that are also in the file and display the properties that have been given to each report.. obviously i need to display the properties for the reports that are also in the table...

So once i have managed this, i will then try and link up the report properties to the right report clicked in the listbox.

so...Reports in the table will display...
field criteria and properties

reports that are not in table
display properties only.

However, becuase i would like to use this as a generic application, it should still work if a table does not exist, as it should only be an option.

I am using a Access 2000 database

thanks, onegui
0
 

Author Comment

by:onegui
ID: 6975464
Hi RichW
I have finally got some report properties to come out of the database.

I stumbled across this combination of code... i think its a bit ugly and wondered if there was a more correct way of getting the report properties..however it will do the trick.

This for loop goes through every report using the next obj method...

You wouldn't know by chance how to get only one report by entering the name of the report...

something like report("rptcustomer") ??

Anyway..thanks for your help


    Dim accessProject As Access.Application

    path = textbox1.text

    Set accessProject = New Access.Application
    accessProject.OpenCurrentDatabase path, False
   
     
   Dim obj As AccessObject, dbs As Object
   Dim i As Integer

  'set the dbs to the current application, which in this case is a access database opened using object accessProject
   Set dbs = Application.CurrentProject
   
   'For each report in the database
   For Each obj In dbs.AllReports
   
   
      'DoCmd.OpenReport obj.name, acViewDesign
      DoCmd.OpenReport obj.name, acViewDesign, , , acHidden
     
      If obj.IsLoaded = True Then
       
        'Identifies the sort order for each field
       For i = 0 To 9
   
        If Not (Reports(0).GroupLevel(i) Is Nothing) Then  'not work!!!
            If (Reports(0).GroupLevel(i).SortOrder) = False Then
                  MsgBox ("The Field " + Str(i) + " is in Descending Order")
            Else
                 MsgBox ("The Field " + Str(i) + " is in Ascending Order")
            End If
        End If
        Next i

       
     DoCmd.Close acReport, obj.name, acSaveNo
     End If
       
 Next obj
 accessProject.CloseCurrentDatabase
0
 
LVL 4

Accepted Solution

by:
RichW earned 200 total points
ID: 6975575
Very good!  

Try If obj.Name = "YourReportName" Then inside the For Each statement.

I didn't know there was an AllReports in the Access object.








0
 

Author Comment

by:onegui
ID: 6975778
cheers Richw
youve been a great help!!!!!!!!!

onegui
0
 
LVL 4

Expert Comment

by:RichW
ID: 6975836
Thanks onegui.  I'm only sorry I couldn't be of more help.

My email is richpwelsh@yahoo.com if you need any other help.

Thanks again!
RichW
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

757 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now