• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 503
  • Last Modified:

Get field names from MS Access table

Dear All

I wish to construct a function whereby I pass the table name and it retreives the field names, so that I can verify if a particular field name is existent in that particular table.

I found this piece of code on the net :-

        Dim SchemaTable As DataTable

        'Retrieve schema information about Table1 Primary Keys.
        SchemaTable = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Primary_Keys, _
        New Object() {Nothing, Nothing, tableName})

        Dim RowCount As Int32
        For RowCount = 0 To SchemaTable.Rows.Count - 1
        Next RowCount


however it was not retreiving any field names.  Sorry but I am quite new to VB.NET

Can you help me out please?

Thanks for your help and time

  • 2
  • 2
1 Solution
one way of achieveing this would be to use a SQL as follows

SELECT * FROM yourtablename WHERE 1=0

this query would return no results but if you create a dataset from it you can get the structure of the table in the dataset, then from the dataset you can obtain the columns
monfuAuthor Commented:
Hello Checoo

I tried this :-

    Function get_fields(ByVal tableName As String) As Boolean
        Dim comp As New Components
        Dim dst As New DataSet
        Dim strSQL As String = "SELECT * FROM " & tableName & " WHERE 1=0"

        dst = comp.get_dataset(strSQL)

        If dst.Tables("ds").Rows.Count > 0 Then
            Dim i As Integer
            For i = 0 To dst.Tables("ds").Rows.Count - 1
                If dst.Tables("ds").Rows(i)("categoryID") Then
                    Return True
                End If
        End If

    End Function

but its not working
the following code works for me, replace the appropriate connectionstring for your case

    Private Sub getcolumns(ByVal strTablename As String)
        Dim conn As New System.Data.OleDb.OleDbConnection
        Dim da As System.Data.OleDb.OleDbDataAdapter
        Dim ds As New DataSet
        Dim i As Integer
        Dim strcolname As String
        Dim strsql As String = "SELECT * FROM " & strTablename & " WHERE 1=0"
        conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\StudentDetails.mdb;Persist Security Info=False"
        da = New System.Data.OleDb.OleDbDataAdapter(strsql, conn)
        da.Fill(ds, strTablename)
        For i = 0 To ds.Tables(strTablename).Columns.Count - 1
            strcolname = strcolname & ds.Tables(strTablename).Columns(i).ColumnName & " "
        MessageBox.Show("List of columns : " & strcolname)
    End Sub
monfuAuthor Commented:
Hey Checoo

Thanks, this code worked perfectly.

Thanks for your help

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now