none
Cannot resolve OledbDataReader permissions error RRS feed

  • Question

  • I am trying to retrieve a list of MS Access user database tables using this code:

     Public Function AccessDBTbls(ByVal strCnn As String) As List(Of String)
            'Purpose:       Lists the tables inside the selected database
            'Parameters:    strCnn As String
            'Returns:       Creates a list of tables for this Access DB
    
            Dim listTables As List(Of String) = New List(Of String)
            Dim strSQL As String
            Using cnn As New OleDbConnection(strCnn)
                Try
                    If cnn.State = ConnectionState.Open Then
                        cnn.Close()
                    Else
                        cnn.Open()
                        'Get database table names from selected MS-Access
                        'database.
                        strSQL = "SELECT MSysObjects.Name AS table_name " & _
                                 "FROM MSysObjects WHERE (((Left([Name],1))<>'~') " & _
                                 "AND ((Left([Name],4))<>'MSys') " & _
                                 "AND ((MSysObjects.Type) In (1,4,6)) AND " & _
                                 "((MSysObjects.Flags)=0)) order by MSysObjects.Name;"
                        Dim cmd As OleDbCommand = New OleDbCommand(strSQL, cnn)
                        Dim dr As OleDbDataReader = cmd.ExecuteReader()
                        While (dr.Read())
                            listTables.Add(dr(0).ToString())
                        End While
                        'Set table list as combobox’s datasource
                        dr.Close()
                        cmd.Dispose()
                    End If
                Catch ex As Exception
                    'Log error 
                    Dim el As New Log.ErrorLogger
                    el.WriteToErrorLog(ex.Message, ex.StackTrace, "Error")
                End Try
            End Using
            Return listTables
        End Function

    The SQL statement in the function works fine when run inside Access itself, but when I try to use it in the VB.Net function I get this error:

    Message: Record(s) cannot be read; no read permission on 'MSysObjects'.
    StackTrace:    at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
       at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
       at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
       at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
       at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
       at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)
       at System.Data.OleDb.OleDbCommand.ExecuteReader()
       at UniDataEntry_2013.MRMAccessDB.AccessDBTbls(String strCnn) in C:\Users\Mark\Documents\Visual Studio 2013\Projects\UniDataEntry_2013\UniDataEntry_2013\MRMAccessDB.vb:line 29
    Date/Time: 8/7/2017 11:14:04 AM
    ===========================================================================================

    I have tried every Commandbehavior option and I still get the same error.

    How do I fix this?

    Thanks,


    MRM256

    Monday, August 7, 2017 4:25 PM

Answers

  • I thought that was the case, so I modified the function.

    Public Function AccessDBTbls(ByVal strCnn As String) As List(Of String)
            'Purpose:       Lists the tables inside the selected database
            'Parameters:    strCnn As String
            'Returns:       reates a list of tables for this Access DB
    
            Dim listTables As List(Of String) = New List(Of String)
            Dim userTables As DataTable = Nothing
            Dim I As Long
            'Dim strSQL As String
            Using cnn As New OleDbConnection(strCnn)
                Try
                    If cnn.State = ConnectionState.Open Then
                        cnn.Close()
                    Else
                        cnn.Open()
                        userTables = cnn.GetSchema("Tables", _
                                                   New String() _
                                                   {Nothing, Nothing, _
                                                    Nothing, "TABLE"})
                        cnn.Close()
                        For I = 0 To userTables.Rows.Count - 1 Step I + 1
                            listTables.Add(userTables.Rows(I)(2).ToString)
                        Next
                    End If
                Catch ex As Exception
                    'Log error 
                    Dim el As New Log.ErrorLogger
                    el.WriteToErrorLog(ex.Message, ex.StackTrace, "Error")
                End Try
            End Using
            Return listTables
        End Function
    This works, and places the table names into a combobox.


    MRM256

    • Marked as answer by MRM256 Monday, August 7, 2017 8:18 PM
    Monday, August 7, 2017 8:17 PM

All replies

  • Microsoft Access system tables are not supposed to be accessed directly and have some level of security to protect them from corruption. If you want to get a list of Access database tables you should be using a different method, such as GetOleDbSchemaTable:

            Dim AccessConnection As System.Data.OleDb.OleDbConnection
            Dim AccessDataTable As DataTable
    
            Try
    
                AccessConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;" & _
                     "Data Source=C:\Test Files\db1 XP.mdb")
    
                AccessConnection.Open()
                AccessDataTable = AccessConnection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, Nothing})
    
                Dim RowCount As Int32
    
                For RowCount = 0 To AccessDataTable.Rows.Count - 1
                    Console.WriteLine(AccessDataTable.Rows(RowCount)!TABLE_NAME.ToString)
                Next RowCount
    
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            Finally
                AccessConnection.Close()
    
            End Try


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Monday, August 7, 2017 6:22 PM
  • I thought that was the case, so I modified the function.

    Public Function AccessDBTbls(ByVal strCnn As String) As List(Of String)
            'Purpose:       Lists the tables inside the selected database
            'Parameters:    strCnn As String
            'Returns:       reates a list of tables for this Access DB
    
            Dim listTables As List(Of String) = New List(Of String)
            Dim userTables As DataTable = Nothing
            Dim I As Long
            'Dim strSQL As String
            Using cnn As New OleDbConnection(strCnn)
                Try
                    If cnn.State = ConnectionState.Open Then
                        cnn.Close()
                    Else
                        cnn.Open()
                        userTables = cnn.GetSchema("Tables", _
                                                   New String() _
                                                   {Nothing, Nothing, _
                                                    Nothing, "TABLE"})
                        cnn.Close()
                        For I = 0 To userTables.Rows.Count - 1 Step I + 1
                            listTables.Add(userTables.Rows(I)(2).ToString)
                        Next
                    End If
                Catch ex As Exception
                    'Log error 
                    Dim el As New Log.ErrorLogger
                    el.WriteToErrorLog(ex.Message, ex.StackTrace, "Error")
                End Try
            End Using
            Return listTables
        End Function
    This works, and places the table names into a combobox.


    MRM256

    • Marked as answer by MRM256 Monday, August 7, 2017 8:18 PM
    Monday, August 7, 2017 8:17 PM
  • Hello,

    There is an easier method.

    Imports System.Data.OleDb
    Public Class Operations
        Private Builder As New OleDbConnectionStringBuilder With
        {
            .Provider = "Microsoft.ACE.OLEDB.12.0",
            .DataSource = IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Database1.accdb")
        }
        Public Function TableNames() As String()
            Using cn As New OleDbConnection(Builder.ConnectionString)
                cn.Open()
                Dim dt As DataTable = New DataTable With {.TableName = "test"}
                dt = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, "TABLE"})
                Return dt.AsEnumerable.Select(Function(row) row.Field(Of String)("TABLE_NAME")).ToArray
            End Using
        End Function
    End Class
    

    Someplace in the form

    Dim ops As New Operations
    ComboBox1.Items.AddRange(ops.TableNames)


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Monday, August 7, 2017 10:30 PM
    Moderator
  • Karen,

    Do have a similar trick for return MS-Access table relationships?

    Thanks,


    MRM256

    Tuesday, August 22, 2017 6:27 PM
  • I am playing around with your Operations Class and added this property.

    Public Property Name As String
            Get
                _DBName = Name
            End Get
            Set(value As String)
                _DBName = value
            End Set
    End Property

    I declared Private _DBName As String and modified .DataSource = IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, _DBName) inside the class.

    When I set the Name property back in the form to ops.DBName = Me.txtDB.Text. I get a NullExceptionError and I don't know why.

    What am I doing wrong? 


    MRM256

    Tuesday, August 22, 2017 10:54 PM