Fields in an Access database table

DataTable class members is the answer.

sorry,,,....

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

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

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

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);
    }
  }
}

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

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/.....

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.

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......

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

Uploaded my code trial project

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.

k....

m lookin into this....

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

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.../???

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