views:

46

answers:

1

I have a Microsoft Access database that I connect to with the Jet Database Engine, using VB.NET. I want to programmatically get all of the column names for a particular table.

I would like to do the equivalent of this MS SQL statement:

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME = 'TableName' 

Is this possible in Access? If not, what are my options for getting the column names?

+1  A: 

I found a way to do this using the GetSchema method of the .NET Connection class.

I wrote a method that returns column names for a particular table.

Private Function GetColumnNamesInTable(ByVal connectionString As String, ByVal tableName As String) As List(Of String)
    Dim connection As OleDbConnection = New OleDbConnection(connectionString)
    Dim restrictions As String() = New String() {Nothing, Nothing, tableName, Nothing}
    connection.Open()
    Dim dataTable As DataTable = connection.GetSchema("Columns", restrictions)
    connection.Close()
    Dim returnList As List(Of String) = New List(Of String)
    For Each dataRow As DataRow In dataTable.Rows
        returnList.Add(dataRow("Column_Name"))
    Next
    Return returnList
End Function
Jeremy
That was quick.
Jeff O
It was my highest priority. I continued to try to find a solution after I wrote the question. I am hoping that someone will respond with a query I can use so that I don't have to do it this way.
Jeremy
Why wasn't .NET mentioned in your original question? It would have helped a lot of people figure out the best answer to your question. You should really edit your question to fix that, since others will be helped more if the full context is clear.
David-W-Fenton
@David: It was an oversight. I have edited the question.
Jeremy
I've searched for well over 2 years for a query to do this and it appears there's none. I eventually settled on this procedure and it works. Besides, the `GetSchema` allows you to retrieve a whole lot of information about any Access database.
Alex Essilfie
@Alex: Thanks for your input. GetSchema does seem pretty powerful.
Jeremy