Ad:

VB.NET Discussion Thread
Marked Solved
Views: 2794

Page 1
2
Next

You are currently viewing page 1 of this multi-page discussion thread

Permalink

Aug 30th, 2009

Fields in an Access database table

Expand Post »

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

Similar Threads

Reading an Access Database in VB.NET
Connection.Execute command to Delete Records from MS Access Database Table in Visual Basic 4 / 5 / 6
problem getting short time from an MS access database in C#
how to save information from a online form to MS access database in ASP
Connect MS Access database table to crystal report, Pls help me in VB.NET

Arunabh Nag

Reputation Points: 10

Solved Threads: 1

Light Poster

Offline

40 posts
since Apr 2009

Permalink

Aug 30th, 2009

Re: Fields in an Access database table

DataTable class members is the answer.

adatapost

Moderator

Reputation Points: 2136

Solved Threads: 1228

Posting Genius

Offline

6,527 posts
since Oct 2008

Permalink

Aug 31st, 2009

Re: Fields in an Access database table

sorry,,,....

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

Arunabh Nag

Reputation Points: 10

Solved Threads: 1

Light Poster

Offline

40 posts
since Apr 2009

Permalink

Aug 31st, 2009

Re: Fields in an Access database table

why not just google for what he told you?

i straight came to http://msdn.microsoft.com/en-us/libr...e_members.aspx

GeekByChoiCe

Featured Poster

Reputation Points: 208

Solved Threads: 168

Practically a Master Poster

Offline

692 posts
since Jun 2009

Permalink

Aug 31st, 2009

Re: Fields in an Access database table

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

HuubVanOers

Reputation Points: 10

Solved Threads: 1

Newbie Poster

Offline

1 posts
since Aug 2009

Permalink

Aug 31st, 2009

Re: Fields in an Access database table

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

VB.NET Syntax (Toggle Plain Text)
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);
    }
  }
}
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

Featured Poster

Reputation Points: 1749

Solved Threads: 735

Senior Poster

Offline

3,948 posts
since Feb 2009

Permalink

Aug 31st, 2009

Re: Fields in an Access database table

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

sknake

Featured Poster

Reputation Points: 1749

Solved Threads: 735

Senior Poster

Offline

3,948 posts
since Feb 2009

Permalink

Sep 2nd, 2009

Re: Fields in an Access database table

Using Wrong code....

VB.NET Syntax (Toggle Plain Text)
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
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

Reputation Points: 10

Solved Threads: 1

Light Poster

Offline

40 posts
since Apr 2009

Permalink

Sep 2nd, 2009

Re: Fields in an Access database table

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

VB.NET Syntax (Toggle Plain Text)
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
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

Reputation Points: 10

Solved Threads: 1

Light Poster

Offline

40 posts
since Apr 2009

Permalink

Sep 6th, 2009

Re: Fields in an Access database table

Used the following code to get the field names.

VB.NET Syntax (Toggle Plain Text)
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
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......

Attached Thumbnails

Click image for larger version

Name: untitled.JPG
Views: 528
Size: 9.7 KB
ID: 11489

Arunabh Nag

Reputation Points: 10

Solved Threads: 1

Light Poster

Offline

40 posts
since Apr 2009

Page 1
2
Next

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in VB.NET Forum Timeline: report in vb.net using reportviewer+ms access database

Next Thread in VB.NET Forum Timeline: Error in my code, dont know how to fix it?

DaniWeb Message

Cancel Changes

User Name
Password