This should be simple?? Get Field names

[Dale]

I'm trying to write a procedure to output the field names in a table, can
someone point out where I'm going wrong in A2K...as always thanks for any
enlightment!

Function GetFields()
Dim dbs As Database, rst As Recordset, fld As Field, x As Integer
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblMyTable")
' set the field...
Set fld = rst.Fields
' loop thru the recordset...
While Not rst.EOF
fldcount = fld.Count
For x = 0 To fldcount - 1
Field = fld.Item(x)
Debug.Print Field.Name & ", " & Field.Value
Next
rst.MoveNext
Wend
rst.Close
dbs.Close
End Function

 

[Douglas J. Steele]

The code looks as though it should work. What problem are you having?

Of course, that routine is going to do more than output the field names in a
table: it's going to list every value stored in the table as well.

And while you don't indicate what problem you're running into, is it a case
that you're getting a "User type not defined" error? If so, that would
probably be because you're using DAO code, and by default Access 2000 uses
ADO.

With any code module open, select Tools | References from the menu bar,
scroll through the list of available references until you find the one for
Microsoft DAO 3.6 Object Library, and select it. If you're not going to be
using ADO, uncheck the reference to Microsoft ActiveX Data Objects 2.1
Library

If you have both references, you'll find that you'll need to "disambiguate"
certain declarations, because objects with the same names exist in the 2
models. For example, to ensure that you get a DAO recordset, you'll need to
use Dim rsCurr as DAO.Recordset (to guarantee an ADO recordset, you'd use
Dim rsCurr As ADODB.Recordset)

The list of objects with the same names in the 2 models is Connection,
Error, Errors, Field, Fields, Parameter, Parameters, Property, Properties
and Recordset

That means you'd need

Dim dbs As DAO.Database, rst As DAO.Recordset, fld As DAO.Field, x As
Integer

 

[Dirk Goldgar]

I'm trying to write a procedure to output the field names in a table,
can someone point out where I'm going wrong in A2K...as always thanks
for any enlightment!

Function GetFields()
Dim dbs As Database, rst As Recordset, fld As Field, x As Integer
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblMyTable")
' set the field...
Set fld = rst.Fields
' loop thru the recordset...
While Not rst.EOF
fldcount = fld.Count
For x = 0 To fldcount - 1
Field = fld.Item(x)
Debug.Print Field.Name & ", " & Field.Value
Next
rst.MoveNext
Wend
rst.Close
dbs.Close
End Function

First, you may have to make sure that your objects are all drawn from
the DAO library. If you're using Access 97, you don't have to worry
about that, but if you're using Access 2000 or later, it's a good idea
to explicitly identify them as such:

Dim dbs As DAO.Database, rst As DAO.Recordset, fld As DAO.Field

However, your other main problem is that you have defined fld as a Field
object (representing a single field), not a Fields object (representing
a collection of fields). You also refer to a variable named "Field",
which is not defined and is not a good name for a variable anyway. Try
this:

'----- start of code -----
Function GetFields()

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim fld As DAO.Field

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblMyTable")

While Not rst.EOF
For Each fld in rst.Fields
Debug.Print fld.Name & ", " & fld.Value
Next fld
rst.MoveNext
Wend

rst.Close
set rst = Nothing
set dbs = Nothing

End Function
'----- end of code -----

 

[Douglas J. Steele]

However, your other main problem is that you have defined fld as a Field
object (representing a single field), not a Fields object (representing
a collection of fields).

Good eye, Dirk. I missed that!

 

[Dale]

Thanks guys...ahh..the power of a little "s"...........from field to
field(s)!!
And you were right Doug...I didn't want a list of all fields, only the
column names really.
But thanks all...

It should not be taken for granted how much each of you and others
contribute to this newsgroup(s) and its appreciated although not always
spoken.

 

[Dirk Goldgar]

Thanks guys...ahh..the power of a little "s"...........from field to
field(s)!!

And you were right Doug...I didn't want a list of all fields, only the
column names really.
But thanks all...

Dale, if you only want a list of the names of the fields, you can do
more efficiently by using the DAO TableDef object instead of opening a
recordset on the table:

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

Set dbs = CurrentDb
Set tdf = dbs.TableDefs("tblMyTable")

For Each fld in tdf.Fields
Debug.Print fld.Name
Next fld

set tdf = Nothing
set dbs = Nothing