none
What are the selects to get metadata from Access RRS feed

  • Question

  • Hi all;

    I have been using OdbcConnection.GetSchema() but I am finding that for views that does not return all columns in views. And for stored procedures it does not work at all. So I think I have to go with selects that return the metadata. So, what are the selects in acces to return:

      • All tables including their schema, name, description, and if they are a system table
      • For each table, all columns including their name, description, DbType.
      • And if a column is a foreign key, the schema.table.column of the primary key it points to.
      • Same for views and their columns.
      • All stored procedures including their schema, name, description, DbType return type (if any), and if it is a system proc.
      • For each parameter in each stored procedure, its name, description, DbType, direction, and if it has a default.

    I'm hoping there's a url someone can point me to that has all this.

    thanks - dave


    Who will win The International Collegiate Programming Championships?

    Thursday, September 6, 2012 4:30 PM

Answers

  • Several years ago I needed similar information and ended up using OleDb methods and DAO methods. Below is code from a VS2008 VB.NET project which requires a reference to “Microsoft Office 12.0 access database engine Object Library” where 12 may be different on your machine. Note at the code of the form I alias the library to shorten things up later in the code and makes it clear what is going on. For all operations I have a private variable FIleName for referencing a accdb file throughout.  Form load event calls a function which returns a DataTable populated using DAO methods and uses the DataTable as the DataSource for a DataGridView for viewing which you may or may not want but that is your choice. There are two buttons, one gets all rows in the DataTable for the DataGridView and shows our information. The next button is more down to the bone for getting querydef information. There is a try/catch if hit means you don’t have proper authority to read specific information so you can try using the function provided ReadMSysObjectsTable which I did for another post on another forum. Another point of interest is creating and getting QueryDef information in this button event.

    I don’t give you everything you are after but do give you proper direction so you will need to work with the code to get what you want and don’t be surprised if there are some roadblocks similar to the authority issue or nothing being returned for a parameter value yet you can get the name and type.

    Imports AccessDao = Microsoft.Office.Interop.Access.Dao
    ''' <summary>
    ''' 
    ''' </summary>
    ''' <remarks>
    ''' Resource
    ''' http://allenbrowne.com/func-dao.html#CreateQueryDAO
    ''' </remarks>
    Public Class frmMainForm
       Private FileName As String = IO.Path.Combine(Application.StartupPath, _
                                                "ProductionDatabase.accdb")
       Private Sub Form1_Load( _
          ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
          If IO.File.Exists(FileName) Then
             Dim dtSchema = GetSchema(FileName, "Customers")
             If dtSchema.Rows.Count > 0 Then
                dtSchema.Columns("Identifier").ColumnMapping = MappingType.Hidden
                DataGridView1.DataSource = dtSchema
                cmdShowItems.Enabled = DataGridView1.Rows.Count > 0
             End If
          Else
             MessageBox.Show("Failed to located your database")
          End If
       End Sub
       ''' <summary>
       ''' Get column information for a specific table in a MS-Access database.
       ''' Columns are returned in the ordinal position as in the database table.
       ''' </summary>
       ''' <param name="DataBaseName">Full path and database name</param>
       ''' <param name="TableName">Table name in database</param>
       ''' <returns>DataTable</returns>
       ''' <remarks>
       ''' The main reason for using this rather than OleDb.GetOleDbSchemaTable is
       ''' that GetOleDbSchemaTable sees Attachments and Memos as strings. So if you 
       ''' are not dealing with Attachments or Memos then use GetOleDbSchemaTable.
       ''' </remarks>
       Public Function GetSchema(ByVal DataBaseName As String, ByVal TableName As String) As DataTable
          ' Give the table a name in the event we want to write it to disk
          Dim dt As New DataTable With {.TableName = String.Concat("Schema_", TableName)}
          dt.Columns.AddRange(New DataColumn() _
              { _
               New DataColumn("Identifier", GetType(System.Int32)), _
               New DataColumn("ColumnName", GetType(System.String)), _
               New DataColumn("DataType", GetType(System.String)), _
               New DataColumn("Size", GetType(System.Int32)), _
               New DataColumn("AllowZeroLength", GetType(System.Boolean)) _
              } _
          )
          dt.Columns("Identifier").AutoIncrement = True
          dt.Columns("Identifier").AllowDBNull = False
          dt.Columns("Identifier").AutoIncrementSeed = 1
          dt.Columns("Identifier").ReadOnly = True
          Dim db As AccessDao.Database
          Dim TheTableDef As AccessDao.TableDef
          Dim Engine As New AccessDao.DBEngine()
          db = Engine.OpenDatabase(DataBaseName)
          TheTableDef = db.TableDefs(TableName)
          With TheTableDef
             For Each f As AccessDao.Field In .Fields
                Dim d As AccessDao.DataTypeEnum
                Dim FieldType = CType( _
                    System.ComponentModel.TypeDescriptor _
                        .GetConverter(d) _
                        .ConvertFromString(f.Type.ToString),  _
                        AccessDao.DataTypeEnum)
                dt.Rows.Add(New Object() _
                   { _
                      Nothing, _
                      f.Name, _
                      FieldType.ToString, _
                      f.Size, _
                      f.AllowZeroLength _
                   } _
                )
             Next
          End With
          Return dt
       End Function
       ''' <summary>
       ''' Tinkering with creating simple query and
       ''' getting basic information back
       ''' </summary>
       ''' <param name="sender"></param>
       ''' <param name="e"></param>
       ''' <remarks></remarks>
       Private Sub cmdTinkering_Click( _
          ByVal sender As System.Object, _
          ByVal e As System.EventArgs) Handles cmdTinkering.Click
          Dim db As AccessDao.Database
          Dim Engine As New AccessDao.DBEngine()
          db = Engine.OpenDatabase(FileName)
          If db.QueryDefs.Count > 0 Then
             Dim QueryExistsCheck = _
             ( _
                From T In db.QueryDefs.Cast(Of AccessDao.QueryDef)() _
                Where T.Name = "Simple1" _
             ).FirstOrDefault
             If QueryExistsCheck Is Nothing Then
                Console.WriteLine("Creating Query!!!")
                db.CreateQueryDef("Simple1", _
                   "SELECT Customers.Identifier, Customers.FirstName, Customers.LastName " & _
                   "FROM Customers ORDER BY Customers.LastName;")
             Else
                Console.WriteLine("Simple1 exists already")
             End If
             For x As Integer = 0 To db.QueryDefs.Count - 1
                Dim test = db.QueryDefs(x)
                Console.WriteLine(db.QueryDefs(x).Name)
                Console.WriteLine("   [{0}]", db.QueryDefs(x).SQL)
                Try
                   Dim Params = db.QueryDefs(x).Parameters
                   Console.WriteLine("Param count: {0}", Params.Count)
                   For y As Integer = 0 To Params.Count - 1
                      Console.WriteLine("     Name [{0}]", Params(y).Name)
                      Console.WriteLine("     Type [{0}]", CType(CInt(Params(y).Type),  _
                                                            AccessDao.DataTypeEnum))
                   Next
                Catch ex As Exception
                   '
                   ' If you land here it may be you do not have proper authority, see also ReadMSysObjectsTable
                   '
                   Console.WriteLine(ex.Message)
                End Try
                Console.WriteLine()
             Next
          End If
       End Sub
       Private Sub cmdShowItems_Click( _
          ByVal sender As System.Object, _
          ByVal e As System.EventArgs) Handles cmdShowItems.Click
          If DataGridView1.Rows.Count > 0 Then
             Dim sb As New System.Text.StringBuilder
             Dim dtSchema = DirectCast(DataGridView1.DataSource, DataTable)
             For Each row As DataRow In dtSchema.Rows
                sb.AppendLine(String.Join(",", row.ItemArray.Select(Function(s) s.ToString).ToArray))
             Next
             MessageBox.Show(sb.ToString)
             dtSchema.Columns("Identifier").ColumnMapping = MappingType.Hidden
             DataGridView1.DataSource = dtSchema
          End If
       End Sub
       ''' <summary>
       ''' </summary>
       ''' <returns></returns>
       ''' <remarks>
       ''' A posting I did on granting authority
       ''' http://www.vbforums.com/showthread.php?681784-talking-to-an-MS-Access-db-from-vb.net
       ''' </remarks>
       Public Function ReadMSysObjectsTable() As DataTable
          Dim Builder As New OleDb.OleDbConnectionStringBuilder With _
             { _
                .Provider = "Microsoft.ACE.OLEDB.12.0", _
                .DataSource = FileName _
             }
          Builder.Add("Jet OLEDB:System Database", _
                      System.Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData) & _
                      "\Microsoft\Access\System.MDW")
          Using cn As New OleDb.OleDbConnection With _
          { _
             .ConnectionString = Builder.ConnectionString _
          }
             Using cmd As New OleDb.OleDbCommand With {.Connection = cn}
                cmd.CommandText = "GRANT SELECT ON TABLE MSysObjects TO PUBLIC"
                Dim dt As New DataTable
                cn.Open()
                cmd.ExecuteNonQuery()
                cmd.CommandText = "SELECT * FROM MSysObjects"
                dt.Load(cmd.ExecuteReader)
                Return dt
             End Using
          End Using
       End Function
    End Class
    Hope this helps.


    KSG

    • Marked as answer by DavidThi808 Friday, September 7, 2012 6:08 PM
    Friday, September 7, 2012 5:44 PM

All replies

  • Did you try OleDbConnection.GetSchema instead? I don't think the Access ODBC driver is really appropriate for obtaining schema info.

    Also, the method in which Access identifies it's objects is not standard. You may need to resort to the DAO library instead to obtain some of this info.

    http://www.devx.com/dbzone/Article/27131


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Thursday, September 6, 2012 6:33 PM
  • Hi Paul;

    We previously tried OleDb for Access and found even more problems with it. Is there any way to get this working for ODBC? It is just one database we're having trouble with. All others our customers have hit for years have done fine.

    We are using the GetSchema() call referenced in your link.

    ??? - thanks - dave


    Who will win The International Collegiate Programming Championships?

    Friday, September 7, 2012 3:37 AM
  • Have you tried GetOleDbSchemaTable?

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Friday, September 7, 2012 4:29 PM
  • Several years ago I needed similar information and ended up using OleDb methods and DAO methods. Below is code from a VS2008 VB.NET project which requires a reference to “Microsoft Office 12.0 access database engine Object Library” where 12 may be different on your machine. Note at the code of the form I alias the library to shorten things up later in the code and makes it clear what is going on. For all operations I have a private variable FIleName for referencing a accdb file throughout.  Form load event calls a function which returns a DataTable populated using DAO methods and uses the DataTable as the DataSource for a DataGridView for viewing which you may or may not want but that is your choice. There are two buttons, one gets all rows in the DataTable for the DataGridView and shows our information. The next button is more down to the bone for getting querydef information. There is a try/catch if hit means you don’t have proper authority to read specific information so you can try using the function provided ReadMSysObjectsTable which I did for another post on another forum. Another point of interest is creating and getting QueryDef information in this button event.

    I don’t give you everything you are after but do give you proper direction so you will need to work with the code to get what you want and don’t be surprised if there are some roadblocks similar to the authority issue or nothing being returned for a parameter value yet you can get the name and type.

    Imports AccessDao = Microsoft.Office.Interop.Access.Dao
    ''' <summary>
    ''' 
    ''' </summary>
    ''' <remarks>
    ''' Resource
    ''' http://allenbrowne.com/func-dao.html#CreateQueryDAO
    ''' </remarks>
    Public Class frmMainForm
       Private FileName As String = IO.Path.Combine(Application.StartupPath, _
                                                "ProductionDatabase.accdb")
       Private Sub Form1_Load( _
          ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
          If IO.File.Exists(FileName) Then
             Dim dtSchema = GetSchema(FileName, "Customers")
             If dtSchema.Rows.Count > 0 Then
                dtSchema.Columns("Identifier").ColumnMapping = MappingType.Hidden
                DataGridView1.DataSource = dtSchema
                cmdShowItems.Enabled = DataGridView1.Rows.Count > 0
             End If
          Else
             MessageBox.Show("Failed to located your database")
          End If
       End Sub
       ''' <summary>
       ''' Get column information for a specific table in a MS-Access database.
       ''' Columns are returned in the ordinal position as in the database table.
       ''' </summary>
       ''' <param name="DataBaseName">Full path and database name</param>
       ''' <param name="TableName">Table name in database</param>
       ''' <returns>DataTable</returns>
       ''' <remarks>
       ''' The main reason for using this rather than OleDb.GetOleDbSchemaTable is
       ''' that GetOleDbSchemaTable sees Attachments and Memos as strings. So if you 
       ''' are not dealing with Attachments or Memos then use GetOleDbSchemaTable.
       ''' </remarks>
       Public Function GetSchema(ByVal DataBaseName As String, ByVal TableName As String) As DataTable
          ' Give the table a name in the event we want to write it to disk
          Dim dt As New DataTable With {.TableName = String.Concat("Schema_", TableName)}
          dt.Columns.AddRange(New DataColumn() _
              { _
               New DataColumn("Identifier", GetType(System.Int32)), _
               New DataColumn("ColumnName", GetType(System.String)), _
               New DataColumn("DataType", GetType(System.String)), _
               New DataColumn("Size", GetType(System.Int32)), _
               New DataColumn("AllowZeroLength", GetType(System.Boolean)) _
              } _
          )
          dt.Columns("Identifier").AutoIncrement = True
          dt.Columns("Identifier").AllowDBNull = False
          dt.Columns("Identifier").AutoIncrementSeed = 1
          dt.Columns("Identifier").ReadOnly = True
          Dim db As AccessDao.Database
          Dim TheTableDef As AccessDao.TableDef
          Dim Engine As New AccessDao.DBEngine()
          db = Engine.OpenDatabase(DataBaseName)
          TheTableDef = db.TableDefs(TableName)
          With TheTableDef
             For Each f As AccessDao.Field In .Fields
                Dim d As AccessDao.DataTypeEnum
                Dim FieldType = CType( _
                    System.ComponentModel.TypeDescriptor _
                        .GetConverter(d) _
                        .ConvertFromString(f.Type.ToString),  _
                        AccessDao.DataTypeEnum)
                dt.Rows.Add(New Object() _
                   { _
                      Nothing, _
                      f.Name, _
                      FieldType.ToString, _
                      f.Size, _
                      f.AllowZeroLength _
                   } _
                )
             Next
          End With
          Return dt
       End Function
       ''' <summary>
       ''' Tinkering with creating simple query and
       ''' getting basic information back
       ''' </summary>
       ''' <param name="sender"></param>
       ''' <param name="e"></param>
       ''' <remarks></remarks>
       Private Sub cmdTinkering_Click( _
          ByVal sender As System.Object, _
          ByVal e As System.EventArgs) Handles cmdTinkering.Click
          Dim db As AccessDao.Database
          Dim Engine As New AccessDao.DBEngine()
          db = Engine.OpenDatabase(FileName)
          If db.QueryDefs.Count > 0 Then
             Dim QueryExistsCheck = _
             ( _
                From T In db.QueryDefs.Cast(Of AccessDao.QueryDef)() _
                Where T.Name = "Simple1" _
             ).FirstOrDefault
             If QueryExistsCheck Is Nothing Then
                Console.WriteLine("Creating Query!!!")
                db.CreateQueryDef("Simple1", _
                   "SELECT Customers.Identifier, Customers.FirstName, Customers.LastName " & _
                   "FROM Customers ORDER BY Customers.LastName;")
             Else
                Console.WriteLine("Simple1 exists already")
             End If
             For x As Integer = 0 To db.QueryDefs.Count - 1
                Dim test = db.QueryDefs(x)
                Console.WriteLine(db.QueryDefs(x).Name)
                Console.WriteLine("   [{0}]", db.QueryDefs(x).SQL)
                Try
                   Dim Params = db.QueryDefs(x).Parameters
                   Console.WriteLine("Param count: {0}", Params.Count)
                   For y As Integer = 0 To Params.Count - 1
                      Console.WriteLine("     Name [{0}]", Params(y).Name)
                      Console.WriteLine("     Type [{0}]", CType(CInt(Params(y).Type),  _
                                                            AccessDao.DataTypeEnum))
                   Next
                Catch ex As Exception
                   '
                   ' If you land here it may be you do not have proper authority, see also ReadMSysObjectsTable
                   '
                   Console.WriteLine(ex.Message)
                End Try
                Console.WriteLine()
             Next
          End If
       End Sub
       Private Sub cmdShowItems_Click( _
          ByVal sender As System.Object, _
          ByVal e As System.EventArgs) Handles cmdShowItems.Click
          If DataGridView1.Rows.Count > 0 Then
             Dim sb As New System.Text.StringBuilder
             Dim dtSchema = DirectCast(DataGridView1.DataSource, DataTable)
             For Each row As DataRow In dtSchema.Rows
                sb.AppendLine(String.Join(",", row.ItemArray.Select(Function(s) s.ToString).ToArray))
             Next
             MessageBox.Show(sb.ToString)
             dtSchema.Columns("Identifier").ColumnMapping = MappingType.Hidden
             DataGridView1.DataSource = dtSchema
          End If
       End Sub
       ''' <summary>
       ''' </summary>
       ''' <returns></returns>
       ''' <remarks>
       ''' A posting I did on granting authority
       ''' http://www.vbforums.com/showthread.php?681784-talking-to-an-MS-Access-db-from-vb.net
       ''' </remarks>
       Public Function ReadMSysObjectsTable() As DataTable
          Dim Builder As New OleDb.OleDbConnectionStringBuilder With _
             { _
                .Provider = "Microsoft.ACE.OLEDB.12.0", _
                .DataSource = FileName _
             }
          Builder.Add("Jet OLEDB:System Database", _
                      System.Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData) & _
                      "\Microsoft\Access\System.MDW")
          Using cn As New OleDb.OleDbConnection With _
          { _
             .ConnectionString = Builder.ConnectionString _
          }
             Using cmd As New OleDb.OleDbCommand With {.Connection = cn}
                cmd.CommandText = "GRANT SELECT ON TABLE MSysObjects TO PUBLIC"
                Dim dt As New DataTable
                cn.Open()
                cmd.ExecuteNonQuery()
                cmd.CommandText = "SELECT * FROM MSysObjects"
                dt.Load(cmd.ExecuteReader)
                Return dt
             End Using
          End Using
       End Function
    End Class
    Hope this helps.


    KSG

    • Marked as answer by DavidThi808 Friday, September 7, 2012 6:08 PM
    Friday, September 7, 2012 5:44 PM
  • Friday, September 7, 2012 5:50 PM
  • Friday, September 7, 2012 6:08 PM
  • thank you

    Who will win The International Collegiate Programming Championships?


    Your welcome.

    KSG

    Friday, September 7, 2012 7:29 PM