Enumerate names of 'fields' in recoedset?

[geralf]

Hi,

How do I enumerate all 'field' names in a recordset?

Thanks in advance.

 

[raskew]

Gerhard-

This is probably overkill, but it should provide sufficient example.

Bob

Function GetField2Description()
' This procedure:
' (1) deletes and recreates a table (tblFields)
' (2) uses a query of (table) MSysObjects to
'     get the names of all tables and linked tables in the
'     database
' (3) Populates tblFields with info about each field

Dim db As DATABASE, td As TableDef
Dim rs As Recordset, rs2 As Recordset
Dim test As String, namehold As String
Dim typehold As String, SizeHold As String
Dim fieldName As String
Dim fielddesc As String, tName As String
Dim n As Long, i As Long, recis As Variant
Dim found As Boolean, prpNew As Property
Dim fld As Field, strSQL As String, ordHold As Integer
n = 0
Set db = CurrentDb
' Trap for any errors.
    On Error Resume Next
tName = "tblFields"
'Does table "tblFields" exist?  If true, delete it;
found = False
test = db.TableDefs(tName).Name
If Err <> 3265 Then
   found = True
   docmd.DeleteObject acTable, "tblFields"
End If
'Create new tblTable
db.Execute "CREATE TABLE tblFields(Object TEXT (55), FieldName TEXT (55), FieldDesc TEXT (55), FieldType TEXT (20), FieldSize Long, FieldAttributes Long, FieldOrd Long);"

strSQL = "SELECT MSysObjects.Name, MSysObjects.Type From MsysObjects WHERE"
strSQL = strSQL + "((MSysObjects.Type)=1)"
strSQL = strSQL + "ORDER BY MSysObjects.Name;"

Set rs = db.OpenRecordset(strSQL)
n = 0
If Not rs.BOF Then
   ' Get number of records in recordset
   rs.MoveLast
   n = rs.RecordCount
   rs.MoveFirst
End If

Set rs2 = db.OpenRecordset("tblFields")

For i = 0 To n - 1
  fielddesc = " "
  Set td = db.TableDefs(i)
    'Skip over any MSys objects
    If Left(rs!Name, 4) <> "MSys" Then
       namehold = rs!Name
       found = False
       On Error Resume Next
       For Each fld In td.Fields
          fieldName = fld.Name
          fielddesc = fld.Properties("Description")
          If Err = 3270 Or fielddesc = " " Then  '3270 = object not found
             fielddesc = "No description provided."
          End If
          Err = 0
          typehold = FieldType(fld.Type)
          SizeHold = fld.Size
          rs2.AddNew
          rs2!Object = namehold
          rs2!fieldName = fieldName
          rs2!fielddesc = fielddesc
          rs2!FieldType = typehold
          rs2!FieldSize = SizeHold
          rs2!FieldAttributes = fld.Attributes

          rs2.Update
       Next fld
  
       Resume Next
    End If
    rs.MoveNext
Next i
rs.Close
rs2.Close
db.Close
End Function

Function FieldType(intType As Integer) As String

Select Case intType
    Case dbBoolean
        FieldType = "dbBoolean"
    Case dbByte
        FieldType = "dbByte"
    Case dbInteger
        FieldType = "dbInteger"
    Case dbLong
        FieldType = "dbLong"
    Case dbCurrency
        FieldType = "dbCurrency"
    Case dbSingle
        FieldType = "dbSingle"
    Case dbDouble
        FieldType = "dbDouble"
    Case dbDate
        FieldType = "dbDate"
    Case dbText
        FieldType = "dbText"
    Case dbLongBinary
        FieldType = "dbLongBinary"
    Case dbMemo
        FieldType = "dbMemo"
    Case dbGUID
        FieldType = "dbGUID"
End Select

End Function

 

[geralf]

Hi Bob!

Awesome Code.....as you said it's an overkill for my case. It's probably my mistake here - saying enumerate 'fields' with fields in single quotes. Here's a better explanation from my side (I hope).

I was after an easy way to use a For Each.....loop, something like this

For Each 'fld' in rst
strNameOfField=fld.Name
Debug.Print strNameOfField
Next

Here rst is a recordset created in a SQL statement. 'fld' is the returned variable or reference name holding the data in the recordset. ( I don't know the proper name of variable names returned in a recordset, so that's why i used 'field' with quotes).

So in short term: How do I get the names of each field in a record of the recordset, not the records in a recordset?

I'm going to use this in a crosstab query, and the column headings of the crosstab query can change. I need to know the names, because I'm using hem in a new select query.

Thanks for your reply Bob!

 

[geralf]

Bob,

Found it!

When it comes to syntaxes, it's no use 'discussing' it with the machine. You will always loose.

Here's what I was after:

With rst
For Each fld In rst.Fields
Debug.Print fld.Name
Next fld
End With

See you here and there Bob . Have a nice weekend.

Best wishes
Gerhard

 

[indyaries]

Looks Great....but...

Raskew (Bob),

Your code looks great, but I have a couple of questions...:

1. How would one implement the code you provided?

2. I'm trying to get the Objects and their Descriptions placed into a new table, called t_Database_Objects. It holds the name of the object (Tables, Querys, Forms, etc.). Even has Type and RecordCounts. However, I cannot seem to find a method to capture the Description of the Object.

Here is the SQL to get the Table info:
SELECT MsysObjects.Name, DCount("*",[Name]) AS TableCount
FROM MsysObjects
WHERE (((Left$([Name],1))<>"~") AND ((Left$([Name],4))<>"Msys") AND ((MsysObjects.Type)=1 Or (MsysObjects.Type)=6))
ORDER BY MsysObjects.Name;

Here is the SQL for Queries:
SELECT MsysObjects.Name, DCount("*",[Name]) AS TableCount
FROM MsysObjects
WHERE (((Left$([Name],1))<>"~") AND ((Left$([Name],4))<>"Msys") AND ((MsysObjects.Type)=5))
ORDER BY MsysObjects.Name;


Is there a method to capture the Descriptions that I've entered for the DB Objects?

TIA !!

Bob

 

[indyaries]

Thanks, Pat...!!

Pat,

Thanks for the query. Another question...

How do I use the below expression that you provided? Does it somehow fit into the SQL, or somewhere else?

TempSet1!DESCRIPTION = Nz(fldLoop.Properties("Description"),"")

Thanks again !!

Bob

 

[raskew]

Re Descriptions:

As Pat pointed out, Description is a user-defined member of the properties collection. However, if you'll look back at the previously posted code, you'll find that description is addressed, i.e.

fielddesc = fld.Properties("Description")
   If Err = 3270 Or fielddesc = " " Then  '3270 = object not found
   fielddesc = "No description provided."
End If

 

[indyaries]

Raskew (Bob),

Thanks again for your reply. I suspect that I botched my explaination of what I was attempting.

BTW, I pasted your code into a module, and it worked flawlessly.

What I'm after is a bit different.

At the primary database window, one can see Tables, Queries, Forms, etc. These have the Object Name, Description, Modified, Created, and Type.

What I was hoping for is a method of capturing the elements displayed on the database window...specifically the Descriptions...(Modified and Created would be a bonus).

Your code is great for documenting the elements of tables. I was merely looking for a method to document what appears at the main database window.

The SQL I posed above will show the Object name, Record Counts, etc., but it doesn't capture the Description element.

I will certainly be using your code to facilitate documenting my tables, as my bosses want this information.

Thanks again to Bob and Pat,

Sincerely,

Bob in Indy