I have a VS2010 (VB.Net) application that needs to count the number of reports (report objects) in an Access 2016 database. In previous versions of Access I did this by first giving Read permission for the MSysObjects table to the "admin" User and then querying the MSysObjects table via this code:
Access 2016 does not support Users and Group Permissions anymore so I cannot grant Read permissions to the admin User and the above code does not work in my application.Code:strSQL = "SELECT * FROM MSysObjects WHERE Type=-32764 AND Flags=0" If con.State = ConnectionState.Closed Then con.Open() cmd = New OleDb.OleDbCommand(strSQL, con) Dim dr As OleDb.OleDbDataReader = cmd.ExecuteReader '...raises OleDb.OleDbException if no read permission on 'MSysObjects' table (hidden table) While dr.Read If flgDebug Then Debug.Print(CStr(dr("Name"))) Select Case CStr(dr("Name")) Case "Accomplishments List" reportCount += 1 Case "Actions Checklist" reportCount += 1 Case "Owners List" reportCount += 1 Case "To-Do List" reportCount += 1 Case "Projects" reportCount += 1 Case "Project Notes (Projects) sub-report" reportCount += 1 Case "Task Notes (Projects) sub-report" reportCount += 1 Case "Activity Notes (Projects) sub-report" reportCount += 1 Case "Tasks" reportCount += 1 Case "Task Notes (Tasks) sub-report" reportCount += 1 Case "Activity Notes (Tasks) sub-report" reportCount += 1 Case "Activities" reportCount += 1 Case "Activity Notes (Activities) sub-report" reportCount += 1 End Select End While dr = Nothing
Side note...I can get the count tables in an Access 2016 database using the GetSchema method of the Connection:
Does anyone know how to get a count of the number of reports in an Access 2016 database?Code:If con.State = ConnectionState.Closed Then con.Open() dt = con.GetSchema("Tables") For Each row As DataRow In dt.Rows If CStr(row.Item("TABLE_TYPE")) = "TABLE" Then Select Case CStr(row.Item("TABLE_NAME")) Case "Account Notes" tableCount += 1 Case "Account Types" tableCount += 1 Case "Accounts" tableCount += 1 Case "Browser Types" tableCount += 1 Case "Field Changes" tableCount += 1 Case "Passwords" tableCount += 1 Case "Security Questions" tableCount += 1 End Select End If '...CStr(row.Item("TABLE_TYPE")) = "TABLE" Next row




Reply With Quote
