Getting all fields for a certain table

[aps]

Hi,

Is it possible to dynamically get a list of all the fields for a specified
table? If so, how is it done.

Many thanks in advance.
Arthur.

 

[Robert Morley]

Are you looking to do this via ADO, DAO, in a query, or something else I
haven't thought of?



Rob

 

[aps]

Hi Rob,

I connect using ADODB.

Thanks.

Are you looking to do this via ADO, DAO, in a query, or something else I
haven't thought of?



Rob

 

[Douglas J Steele]

You can use the OpenSchema method.

To get all of the columns in table Employees, you'd use something like:

Set rstSchema = Cnxn.OpenSchema(adSchemaColumns, Array(Null, Null,
"Employees", Null))

Do Until rstSchema.EOF
Debug.Print rstSchema!TABLE_NAME & ": " & rstSchema!COLUMN_NAME
rstSchema.MoveNext
Loop

(where Cnxn is an already opened Connection to the database in question)

 

[aps]

Hi Doug,

Thanks for the response. I have checked out the OpenSchema through the help,
really useful. Trouble is that on opening the scema, there is no result, ie
immediately I have EOF as true, therefore there is nothing to display. Tried
changing to list of tables and had similar results. Even setting all criteria
to NULL has a similar effect.

Arthur.

 

[Douglas J Steele]

What's the exact code you're using?

 

[aps]

Hi Doug,

Tried a few other things;

The following works
Set rstSchema = dbCon.OpenSchema(adSchemaColumns)

but this one does not work
Set rstSchema = dbCon.OpenSchema(adSchemaColumns, Array(Null, Null,
"Tender", Null))
where "Tender" is a valid table name.

The tables are linked, so could the name be different from "Tender"?

Thanks,
Arthur.

 

[Douglas J Steele]

Just to find out, try running:

Set rstSchema = Cnxn.OpenSchema(adSchemaColumns)

Do Until rstSchema.EOF
Debug.Print rstSchema!TABLE_NAME & ": " & rstSchema!COLUMN_NAME
rstSchema.MoveNext
Loop

and see what it thinks the table name is.

 

[aps]

Hi Doug,

Code is;

Dim rstSchema As ADODB.Recordset

Set dbCon = CreateObject("ADODB.Connection")
Set dbCon = CurrentProject.Connection

Set rstSchema = dbCon.OpenSchema(adSchemaColumns, Array(Null, Null,
"Tender", Null))

Do Until rstSchema.EOF
Debug.Print rstSchema!TABLE_NAME & ": " & rstSchema!COLUMN_NAME
rstSchema.MoveNext
Loop

dbCon.Close
Set rstSchema = Nothing
Set dbCon = Nothing

 

[Douglas J Steele]

Did you try my advice to run without any condition

Set rstSchema = Cnxn.OpenSchema(adSchemaColumns)

and see what table name it uses?

 

[aps]

Hi Doug,

Yes, I did that and the table display looks normal. I even ran the following

If rstSchema!TABLE_NAME = "Tender" Then
a = 1
End If

with a break on the middle line. The app did indeed break. Looks like I will
have to just test for the table I want like this and allow the inefficient
processing of the other 100+ tables.

Thanks for your help, at least I have an answer now.

Regards,
Arthur.

 

[aps]

Hi Doug,

I have found how to make it work, instead of the following

Set rstSchema = dbCon.OpenSchema(adSchemaColumns, Array(Null, Null,
"Tender", Null))

I tried;

Set rstSchema = dbCon.OpenSchema(adSchemaColumns, Array(Empty, Empty,
"Tender", Empty))

And it works beautifully.

Thanks,
Arthur.

 

[DavidAtCaspian]

Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset(Recordset)
Dim fld As DAO.Field

For Each fld In rs.Fields

MsgBox fld.Name

Next fld


This works for DAO, and should work OK for ADO.
You could substitute the msgbox line for an out put to a table with the
fields "RecordsetName" and "FieldName", and then read your 'fields' table

If you make this a sub receiving a recordset object, it could be put into a
utilitie module and called from anywhere.

like this

Sub FLDNames(RS As DAO.Recordset)

Dim fld As DAO.Field

For Each fld In RS.Fields

MsgBox fld.Name

Next fld

End Sub


Any good???

 

[aps]

Hi David,

Thanks for your suggestion, looks really useful.

 

[DavidAtCaspian]

Just found something that might be even better. Using a scripting.dictionary
object, you can not only get all the field names, but also store and retrieve
their values.
Or you can try an array, or a collection, depending on how you want to
retrieve them afterwards.
Have a quick look at at the thread dealing with an equivalent to the Foxpro
Scatter/Gather on this board.
Brendan has sent an idea and I've posted both of my subs. (Not fully error
trapped yet, but basically functional).

David