Fields in an Access database table

[Arunabh Nag]

How do i find out the number, names and datatype of fields in an Access table.....using code.....

[__avd]

DataTable class members is the answer.

[Arunabh Nag]

sorry,,,....

m kinda new to this....
can u please explain...??

[GeekByChoiCe]

why not just google for what he told you?

i straight came to http://msdn.microsoft.com/en-us/library/system.data.datatable_members.aspx

[HuubVanOers]

Hi
the most easiest way is via DAO
- add reference to ms DAO (3.6)
- set vars for database and tabledef and field
dim db as dao.database, dim tdf as dao.tabledef
- get the current database: set db = currentdb()
- get the tabledef as object: set tdf = db.tabledefs("tblName")
- loop the fields for each fld in tdf.fields etc.
- debug.print fld.name etc.

further see the internal help: F1 on as word in the code

good luck

huub

[sknake]

Arunabh Nag: Here is the C# code I use to enumerate tables and columns. Let me know if you have trouble translating it and I will get back with you tomorrow. I figure a C# answer today is better than a VB.NET answer tomorrow :)

using System;
using System.Data;
using System.Data.Common;
using System.Windows.Forms;

namespace daniweb
{
  public partial class frmDbSearch : Form
  {
    public frmDbSearch()
    {
      InitializeComponent();
    }

    private void button1_Click(object sender, EventArgs e)
    {
      //if (_conn.ConnectionType == ConnectionWrapperType.ODBC)
      //{
      //  SetTableList(@"System.Data.Odbc");
      //}
      //else if (_conn.ConnectionType == ConnectionWrapperType.OleDb)
      //{
      //  SetTableList(@"System.Data.OleDb");
      //}
      //else if (_conn.ConnectionType == ConnectionWrapperType.SQL)
      //{
      //  SetTableList(@"System.Data.SqlClient");
      //}
      //else if (_conn.ConnectionType == ConnectionWrapperType.Oracle)
      //{
      //  SetTableList(@"System.Data.OracleClient");
      //}

      const string fName = @"C:\dotnetwin\dxDelete\dxSample_Q216730(1)\WebSite214\App_Data\nwind.mdb";
      string connStr = BuildAccessConnectionString(fName, "Admin", string.Empty, string.Empty);
      DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");
      using (DbConnection connection = factory.CreateConnection())
      {
        connection.ConnectionString = connStr;
        connection.Open();
        DataTable _dtTables = connection.GetSchema("Tables");
        DataTable _dtColumns = connection.GetSchema("Columns");
        //At this point you have the tables, columns, views, etc.
        System.Diagnostics.Debugger.Break();
      }

    }


    public static string BuildAccessConnectionString(string Filename, string Username, string Password, string DatabasePassword)
    {
      return string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='{0}';User Id={1};Password={2};Jet OLEDB:Database Password={3};",
                                   Filename.Replace("'", "''"),
                                   Username,
                                   Password,
                                   DatabasePassword);
    }
  }
}

[sknake]

Also note -- you can just call connection.GetSchema() off of an existing OleDb or ODBC connection to access... its a lot less work.

[Arunabh Nag]

Using Wrong code....

Module Module1

    'How to get a list of columns from an access database given a table name	    	 
    Dim oleConn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\users.mdb")
    Dim restrictions() As String = {"", "", "userinfo", ""}
    Dim SchemaTable As DataTable
    Public Sub connect()
        oleConn.Open()
        SchemaTable = oleConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Columns, _
                    New Object() {Nothing, Nothing, Nothing, Nothing})
        Dim int As Integer
        For int = 0 To SchemaTable.Rows.Count - 1
            If SchemaTable.Rows(int)!TABLE_TYPE.ToString = "TABLE"
'EXCEPTION : Table is not part of table column
Then
                'Add items to list box
                Form1.comboselecttable.Items.Add(SchemaTable.Rows(int)!COLUMN_NAME.ToString())
            End If
            '// Get list of tables columns
        Next

        'Form1.comboselecttable.Items.Add(dbRet.Rows(0)!TABLE_NAME.ToString())
        '// dbRet will have a column name called "COLUMN_NAME" which will contain all the columns for the table
    End Sub

    Public Sub endconn()
        oleConn.Close()
    End Sub

End Module

plz help/.....

[Arunabh Nag]

have used another variation that gives a list of the columns....

But the list also contains a set of non-existent columns (Varying with diff databases).Can anyone help..??

Module Module1

    'How to get a list of columns from an access database given a table name	    	 Jereme E. Guenther   |   Edit   |   Show History
    Dim oleConn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\password.mdb;Jet OLEDB:Database Password=admin")
    'Dim oleConn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\chatusers.mdb;Jet OLEDB:Database Password=admin")
    'Dim n As Integer
    Dim restrictions() As String = {"", "", "password", ""}
    Dim SchemaTable As DataTable
    Public Sub connect()
        oleConn.Open()
        SchemaTable = oleConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Columns, _
                    New Object() {Nothing, Nothing, Nothing, Nothing})
        Dim int As Integer
        For int = 0 To SchemaTable.Rows.Count - 1
            'If SchemaTable.Rows(int)!TABLE_TYPE.ToString = "TABLE" Then
            'Add items to list box
            Form1.comboselecttable.Items.Add(SchemaTable.Rows(int)!COLUMN_NAME.ToString())
            'End If
            '// Get list of tables columns
        Next

        'Form1.comboselecttable.Items.Add(dbRet.Rows(0)!TABLE_NAME.ToString())
        '// dbRet will have a column name called "COLUMN_NAME" which will contain all the columns for the table
    End Sub

    Public Sub endconn()
        oleConn.Close()
    End Sub

End Module

some extra column names that appear are...::
Data
ID
Id
LValue
ObjectGuid
ObjectName
Property e.t.c.

[Arunabh Nag]

Used the following code to get the field names.

Module Module1

    'How to get a list of columns from an access database given a table name	    	 Jereme E. Guenther   |   Edit   |   Show History
    'Dim oleConn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\password.mdb;Jet OLEDB:Database Password=admin")
    'Dim oleConn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\chatusers.mdb;Jet OLEDB:Database Password=admin")
    Dim oleConn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\contacts.mdb")
    'Dim n As Integer
    'Dim restrictions() As String = {"", "", "tablename", ""}
    Dim SchemaTable As DataTable
    Dim countcols As Integer
    Public Sub connect()
        oleConn.Open()
        SchemaTable = oleConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Columns, _
                    New Object() {Nothing, Nothing, Nothing, Nothing})
        Dim int As Integer
        For int = 0 To SchemaTable.Rows.Count - 1
            'If SchemaTable.Rows(int)!TABLE_TYPE.ToString = "TABLE" Then
            'Add items to list box
            Form1.comboselecttable.Items.Add(SchemaTable.Rows(int)!COLUMN_NAME.ToString())
            'End If
            '// Get list of tables columns
        Next
        countcols = SchemaTable.Rows.Count
        Form1.TextBox1.Text = CStr(countcols)
        'Form1.comboselecttable.Items.Add(dbRet.Rows(0)!TABLE_NAME.ToString())
        '// dbRet will have a column name called "COLUMN_NAME" which will contain all the columns for the table
    End Sub

    Public Sub endconn()
        oleConn.Close()
    End Sub

End Module

the problem now is that apart from the actual field names, i get 7-8 more column names that don't exist in the database.

The extra field names and numbers vary from database to database.
I have uploaded a screenshot showing the fieldnames.
only the password column actually exists
Plz help......

[sknake]

Upload a sample project with your database so we can review.

[Arunabh Nag]

Uploaded my code trial project

[sknake]

The problem is you're looking at all columns in the database, you need to be looking for all columns in a specific table. In this case you have a system table called "MSysAccessStorage" in the database and you're seeing the columns from it. Filter based on the "TABLE_NAME" column of the schema table.

[Arunabh Nag]

k....

m lookin into this....

[Arunabh Nag]

The problem is you're looking at all columns in the database, you need to be looking for all columns in a specific table. In this case you have a system table called "MSysAccessStorage" in the database and you're seeing the columns from it. Filter based on the "TABLE_NAME" column of the schema table.

Couldn't understand how to use the filter.
But modified the code to display the list of tables in an database file....
I guess all the tables starting with MSys... are access definition tables.

Module Module1

    Dim oleConn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\contacts.mdb")
    Dim SchemaTable1 As DataTable
    Dim SchemaTable2 As DataTable
    Dim countcols As Integer
    Public Sub connect()
        oleConn.Open()
        SchemaTable1 = oleConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Columns, _
                    New Object() {Nothing, Nothing, Nothing, Nothing})
        SchemaTable2 = oleConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, _
                    New Object() {Nothing, Nothing, Nothing, Nothing})
        Dim int1 As Integer
        Dim int2 As Integer
        For int2 = 0 To SchemaTable2.Rows.Count - 1
            Form1.ComboBox1.Items.Add(SchemaTable2.Rows(int2)!TABLE_NAME.ToString())
            For int1 = 0 To SchemaTable1.Rows.Count - 1
                'If SchemaTable2.Rows(int2)!TABLE_TYPE.ToString = "TABLE" Then
                If SchemaTable2.TableName Is Form1.ComboBox1.SelectedItem Then
                    Form1.comboselecttable.Items.Add(SchemaTable1.Rows(int1)!COLUMN_NAME.ToString())
                End If
                'End If
            Next
        Next
        countcols = SchemaTable1.Rows.Count
        Form1.TextBox1.Text = CStr(countcols)
      End Sub

    Public Sub endconn()
        oleConn.Close()
    End Sub

End Module

[Arunabh Nag]

Module Module1

    Dim oleConn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\contacts.mdb")
    Dim SchemaTable1 As DataTable
    Dim SchemaTable2 As DataTable
    Dim countcols As Integer
    Public Sub connect()
        oleConn.Open()
        SchemaTable2 = oleConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, _
                    New Object() {Nothing, Nothing, Nothing, Nothing})
        Dim int1 As Integer
        Dim int2 As Integer
        For int2 = 0 To SchemaTable2.Rows.Count - 1
            Form1.ComboBox1.Items.Add(SchemaTable2.Rows(int2)!TABLE_NAME.ToString())
            Dim tabname As String = CStr(Form1.ComboBox1.SelectedItem)
            SchemaTable1 = oleConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Columns, _
                   New Object() {Nothing, Nothing, "table1", Nothing})
            For int1 = 0 To SchemaTable1.Rows.Count - 1
                If SchemaTable2.Rows(int2)!TABLE_TYPE.ToString = "TABLE" Then
                    'If SchemaTable2.TableName Is Form1.ComboBox1.SelectedItem Then
                    Form1.comboselecttable.Items.Add(SchemaTable1.Rows(int1)!COLUMN_NAME.ToString())
                    'Form1.comboselecttable.Items.Add(SchemaTable1.Columns(int1).ToString())
                    End If
                    'End If
            Next
        Next
        countcols = SchemaTable1.Rows.Count
        Form1.TextBox1.Text = CStr(countcols)
      End Sub

    Public Sub endconn()
        oleConn.Close()
    End Sub

End Module

This code works...
thanx

Is it possible to use a variable in the line ::

SchemaTable1 = oleConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Columns, _
                   New Object() {Nothing, Nothing, "table1", Nothing})

instead of the table name.../???

[SadiSerdari]

Imports System.Data
Imports System.Data.OleDb

Module Module1

    Dim oleconn As OleDbConnection

    Dim StrPth As String = Application.StartupPath + "\Contacts.mdb"
    Dim Conn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & StrPth

    Dim SchemaTable1 As DataTable
    Dim SchemaTable2 As DataTable

    Dim countcols As Integer

    Dim Restriction() As String
    Public Sub connect()

        oleconn = New OleDbConnection(Conn)

        oleconn.Open()

        SchemaTable2 = oleconn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New String() {Nothing, Nothing, Nothing, "TABLE"})

        For int2 As Integer = 0 To SchemaTable2.Rows.Count - 1

            Form1.ComboBox1.Items.Add(SchemaTable2.Rows(int2)!TABLE_NAME.ToString())


            Form1.ComboBox1.SelectedIndex = 0
            Form1.TextBox2.Text = Form1.ComboBox1.Text

            SchemaTable1 = oleconn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, New String() {Nothing, Nothing, Form1.ComboBox1.Text})

            For int1 As Integer = 0 To SchemaTable1.Rows.Count - 1


                Form1.comboselecttable.Items.Add(SchemaTable1.Rows(int1)!COLUMN_NAME.ToString())


            Next : Next

        countcols = SchemaTable1.Rows.Count

        Form1.TextBox1.Text = CStr(countcols)

    End Sub

    Public Sub endconn()

        oleconn.Close()

    End Sub

End Module