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

        conn.Open()
        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
            Trace.Write(SchemaTable.Rows(RowCount)!PK_NAME.ToString)
            Trace.Write(SchemaTable.Rows(RowCount)!COLUMN_NAME.ToString)
        Next RowCount

        conn.Close()

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

Johann
monfuAsked:
Who is Participating?
 
checooConnect With a Mentor Commented:
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"
        conn.Open()
        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 & " "
        Next
        MessageBox.Show("List of columns : " & strcolname)
    End Sub
0
 
checooCommented:
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
0
 
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
            Next
        End If

    End Function

but its not working
0
 
monfuAuthor Commented:
Hey Checoo

Thanks, this code worked perfectly.

Thanks for your help

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

All Courses

From novice to tech pro — start learning today.