Get field name of a table.

[Guest]

Hi

I am trying to msgbox the field name of a table.
I tried:

Dim strSQL As String
Dim rs As DAO.Recordset
Dim fld As Field

strSQL = "SELECT * FROM [dbo_Item Number List]"
Set rs = CurrentDb.OpenRecordset(strSQL)

For Each fld In rs.Fields
MsgBox fld.Name
Next

But I get a mismatch error on the line of "For Each...."
Is there any other way to get the fields name?

Thanks

 

[Roger Carlson]

This is a References issue. Both DAO and ADO have a Field object, but they
are not compatible. You probably have the ADO reference higher in the list
than the DAO reference. This causes the field to be defined as an ADO field
instead of a DAO field. The solution is to explicitly reference which
object model you are using, just like for the Recordset:

Dim fld as DAO.Field

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Jeff Conrad]

Hi Den,

Are you trying to list all the fields for that table in a message box?
Your code works fine, but you need to explicity tell Access that
you want to return a DAO field object. Since both ADO and DAO
have a Field object, Access is confused at this point. Making that
quick change will allow your code to run just fine.

A question though. Is there only one field in that table?
If there is more than one field, than you will get a separate message
box for each field. How about this alternative (with error handling
and cleanup code which you should have by the way).

'**********Start of Code**********
Public Sub ListTableFields()
On Error GoTo ErrorPoint

Dim rst As DAO.Recordset
Dim fld As DAO.Field
Dim strSQL As String
Dim strMsg As String
Dim strStart As String

strSQL = "SELECT * FROM [dbo_Item Number List]"

Set rst = CurrentDb.OpenRecordset(strSQL)

For Each fld In rst.Fields
strMsg = strMsg & fld.Name & vbNewLine
Next

strStart = "The table dbo_Item Number List contains " _
& "the following field(s):" & vbNewLine & vbNewLine

MsgBox strStart & strMsg, vbInformation, "Table Field List"

ExitPoint:
On Error Resume Next
' Cleanup Code
rst.Close
Set rst = Nothing
Exit Sub

ErrorPoint:
' Unexpected Error
MsgBox "The following error has occurred:" _
& vbNewLine & "Error Number: " & Err.Number _
& vbNewLine & "Error Description: " & Err.Description _
, vbExclamation, "Unexpected Error"
Resume ExitPoint

End Sub
'**********End of Code**********