• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4271
  • Last Modified:

How to overcome no read permission on 'msysobjects' error in Access 2007 connecting via ADO.NET?

I get an error - OleDbException was unhandled -

Record(s) cannot be read; no read permission on 'msysobjects'.with the highlight on objDatareader  =objCommand.ExecuteReader()

Is this something in Microsoft Access where I change a setting or do I convert below to a dataset or something else besides a datareader?

Can I have someone's conversion of a datareader to a dataset?
I'm new to VB.net and pulled this example off the web.
Private Sub Assign_Objects(ByVal sDB As String)

    Dim sConnection As String
    Dim objCommand As New OleDbCommand
    Dim objDataReader As OleDbDataReader

    m_Tables = New Collection
    m_Reports = New Collection
    m_Queries = New Collection
    m_Forms = New Collection
    m_Modules = New Collection
    m_Macros = New Collection

    Dim sName As String
    'oConn.Mode = adModeShareExclusive
    'oConn.Open("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + sDB + ";")

    sConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + sDB + ";"
    objCommand.CommandText = "select name from msysobjects where type=1"
    objCommand.Connection = New OleDbConnection(sConnection)
    objCommand.Connection.Open()
    objDataReader = objCommand.ExecuteReader()
    If objDataReader.HasRows Then
      Do While objDataReader.Read()
        sName = Convert.ToString(objDataReader(0))
        If Not Microsoft.VisualBasic.Left(sName, 4) = "MSys" Then
          m_Tables.Add(sName)
        End If
      Loop
    Else
      Console.WriteLine("No rows returned.")
    End If
    objDataReader.Close()
    objCommand.Dispose()

    objCommand.CommandText = "select name from msysobjects where type=5"
    objCommand.Connection = New OleDbConnection(sConnection)
    objCommand.Connection.Open()
    objDataReader = objCommand.ExecuteReader()
    If objDataReader.HasRows Then
      Do While objDataReader.Read()
        sName = Convert.ToString(objDataReader(0))
        If Not Microsoft.VisualBasic.Left(sName, 1) = "~" Then
          m_Queries.Add(sName)
        End If
      Loop
    Else
      Console.WriteLine("No rows returned.")
    End If
    objDataReader.Close()
    objCommand.Dispose()

    objCommand.CommandText = "select name from msysobjects where type=-32768"
    objCommand.Connection = New OleDbConnection(sConnection)
    objCommand.Connection.Open()
    objDataReader = objCommand.ExecuteReader()
    If objDataReader.HasRows Then
      Do While objDataReader.Read()
        sName = Convert.ToString(objDataReader(0))
        m_Forms.Add(sName)
      Loop
    Else
      Console.WriteLine("No rows returned.")
    End If
    objDataReader.Close()
    objCommand.Dispose()

    objCommand.CommandText = "select name from msysobjects where type=-32764"
    objCommand.Connection = New OleDbConnection(sConnection)
    objCommand.Connection.Open()
    objDataReader = objCommand.ExecuteReader()
    If objDataReader.HasRows Then
      Do While objDataReader.Read()
        sName = Convert.ToString(objDataReader(0))
        m_Reports.Add(sName)
      Loop
    Else
      Console.WriteLine("No rows returned.")
    End If
    objDataReader.Close()
    objCommand.Dispose()

    objCommand.CommandText = "select name from msysobjects where type=-32761"
    objCommand.Connection = New OleDbConnection(sConnection)
    objCommand.Connection.Open()
    objDataReader = objCommand.ExecuteReader()
    If objDataReader.HasRows Then
      Do While objDataReader.Read()
        sName = Convert.ToString(objDataReader(0))
        m_Modules.Add(sName)
      Loop
    Else
      Console.WriteLine("No rows returned.")
    End If
    objDataReader.Close()
    objCommand.Dispose()

  End Sub

Open in new window

0
stephenlecomptejr
Asked:
stephenlecomptejr
  • 5
  • 2
1 Solution
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Does this happen ONLY on that database, or does it happen on any database?

Obviously you can successfully open the connection, but it may help to specify where your System .mdw file resides:

Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & sDB & ";Jet OLEDB:System Database=full_path_to_your_system.mdw_file"

Is this an Access 2007 database you're working with?
0
 
stephenlecomptejrAuthor Commented:
It's not a mdw file at all - only a .accdb (Access 2007) file residing on the C:\drive.

In fact sDB = "C:\Users\Stephen LeCompte\Documents\Databases\EC\OSUMC\E9120-00\Team Docs\OSU.accdb"
0
 
stephenlecomptejrAuthor Commented:
I've tested this on another .accdb file and it does the same thing.  

I'm pretty sure that if someone could give me a different syntax to try on reading the data line by line other than a data reader - I can get this to go through.
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You misread what I wrote.

I said you sometimes have to SPECIFY the .mdw file. Access/Jet/ACE all MUST have some form of .mdw file, and sometimes you must specify this path.

Have you tried this approach?

<I'm pretty sure that if someone could give me a different synta>

A data reader can certainly hook up to an Access database. You might try enclosing field/table names in square brackets i.e. [name]

0
 
stephenlecomptejrAuthor Commented:
LSMConsulting -

I did not know about the .mdw file is there an article that you know of that would explain this clearly?  I don't know how to go about finding my full_path_to_your_system.mdw_file?

I will try to enclose the field/table names tonight with a bracket to see if that would work.  It would make sense that since the name is used as the name of field in a system table.
0
 
stephenlecomptejrAuthor Commented:
When I change it to -

objCommand.CommandText = "select [name] from [msysobjects] where [type]=1"

I still get an error -

Record(s) cannot be read; no read permission on 'msysobjects'.

objDataReader = objCommand.ExecuteReader()
0
 
stephenlecomptejrAuthor Commented:
sConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + sDB + ";Jet OLEDB:System Database=C:\TVSBSamp\WORKGRP.MDW"

Once I changed it to the above after doing a search for .mdw - then it works perfectly!
Thanks LSMConsulting.  I appreciate it.
0
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now