?
Solved

Get field names from MS Access table

Posted on 2004-10-13
4
Medium Priority
?
501 Views
Last Modified: 2010-04-23
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
0
Comment
Question by:monfu
  • 2
  • 2
4 Comments
 
LVL 9

Expert Comment

by:checoo
ID: 12295784
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
 

Author Comment

by:monfu
ID: 12295882
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
 
LVL 9

Accepted Solution

by:
checoo earned 400 total points
ID: 12296485
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
 

Author Comment

by:monfu
ID: 12296589
Hey Checoo

Thanks, this code worked perfectly.

Thanks for your help

Johann
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question