query to retrieve list of field names belonging to an access table

[Guest]

Hi,
I am trying to populate a dropdown box with a list of field names once a
table is selected. I am using the following query to populate the list of
tables from the access database:

SELECT MSysObjects.Name as tname FROM MSysObjects WHERE MSysObjects.Flags =
0 AND MSysObjects.Type = 1 ORDER BY MSysObjects.Name

However, I can not find an SQL example showing how to retrieve a list of
field names for an access table. Does anyone know if this is possible? and if
so how to do it?

 

[Marshall Barton]

I am trying to populate a dropdown box with a list of field names once a
table is selected. I am using the following query to populate the list of
tables from the access database:

SELECT MSysObjects.Name as tname FROM MSysObjects WHERE MSysObjects.Flags =
0 AND MSysObjects.Type = 1 ORDER BY MSysObjects.Name

However, I can not find an SQL example showing how to retrieve a list of
field names for an access table. Does anyone know if this is possible? and if
so how to do it?

I don't know of a way to query for a table's field names.

However you can get the result you want a couple of other
ways. The most obvious is to use the CallBack function
approach, which is kind of tedious to code.

A different way is to set the combo box's RowSourceType to
Field List and then you can just stuff the table's name into
the RowSource.

 

[Brendan Reynolds]

I couldn't find a way to do it with a query, but you can get the field names
into a recordset using the ADO OpenSchema method. The example below
retrieves all columns from all tables and then filters the recordset. The
OpenSchema method takes a 'Criteria' method that can be used to limit the
data retrieved, and that would probably be more efficient, but in my limited
tests I could not get it to work. I see from the documentation that
providers are not required to support that feature.

Public Sub ListFields()

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim fld As ADODB.Field

Set cnn = CurrentProject.Connection
Set rst = cnn.OpenSchema(adSchemaColumns)
rst.Filter = "TABLE_NAME = 'Employees'"
Do Until rst.EOF
Debug.Print rst.Fields("COLUMN_NAME")
rst.MoveNext
Loop
rst.Close

End Sub

 

[Brendan Reynolds]

Oops! For 'method' please read 'argument' ...

--
Brendan Reynolds (MVP)

OpenSchema method takes a 'Criteria' method that can be used to limit the

<snip>