Use SQL to retrieve the field names in an Access table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Could someone please post back example SQL to retrieve the field names in an
Access table (i.e. not DAO and not ADO, but SQL)?

Is it possible?

Thanks in advance.
 
AFAIK, there's no way to retrieve the field names for an Access table using
SQL.
 
Thank you, Mr. Steele. What is the easiest way to accomplish this in code?
Could you please post an example? Thanks much in advance.
 
Using DAO, it would be something like the following untested aircode:

Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim fldCurr As DAO.Field

Set dbCurr = CurrentDb()
For Each tdfCurr In dbCurr.TableDefs
If (tdfCurr.Attributes And dbSystemObject) = 0 Then
Debug.Print "Fields in " & tdfCurr.Name & ":"
For Each fldCurr In tdfCurr.Fields
Debug.Print fldCurr.Name
Next fldCurr
Debug.Print
End If
Next tdfCurr

Set fldCurr = Nothing
Set tdfCurr = Nothing
Set dbCurr = Nothing
 
I have created a query which looks at a single table with some fields that
look up a person's family name by personal ID number and associates those
individuals with a file. I want to take the name fields and concatonate them
into one for reporting; however, I get something strange even though the
concatenated field and the family name fields refer to the same relations in
the lookup table.

The query return will return 5 fields that look something like

File # Smith Jones George 2, 3, 1

where the 1,2,3 are personal ID numbers autonumbered in the personal
information file where they are looked up. Why can't I get Smith, Jones,
George returned in the last column?

Access 2000
XP Professional
 
where the 1,2,3 are personal ID numbers autonumbered in the personal
information file where they are looked up. Why can't I get Smith, Jones,
George returned in the last column?
Click to expand...

Because they do not exist in the table you're concatenating.

Lookup fields (as suggested elsethread) are often more trouble than
they're worth. They CONCEAL the actual contents of your table. What's
stored in your table *is the ID number* - that basic fact is hidden
from view, but that's what's there; so when you concatenate the
content of the table field, it's concatenating what's really there,
not what the Lookup misfeature is showing you.

To get the concatenation, create a Query joining your main table to
the lookup table (or tables); select the fields *from the table where
the value actually exists* - the lookup table - rather than the ID
field from the main table.

And... if you have split first names, middle names, and last names out
into lookup tables, I fear you're overnormalizing. Personal names are
(in most instances) valid attributes of a Person entity, and as such
should be stored as simple text fields with in the table of people. I
doubt very much that the set of all "W" middle names is a valid Entity
Class (though I know several of us who are pretty interesting people).

John W. Vinson[MVP]
 
I've just come upon a godsend of an object (ResultSetMetaData) and written a
function to use it, although it's in java:

// **************************************************************
// Return the names of the columns of the given result set in a
// hashtable
// **************************************************************
private static Hashtable GetTableColumnNames(ResultSet rs)
{
Hashtable ht = null;
int intColumnIndex = 0;
int intNumberOfColumns = 0;
ResultSetMetaData rsmd;
String strColumnName = null;
String strTable = null;

ht = new Hashtable();

try
{
// Get the metadata
rsmd = rs.getMetaData();
intNumberOfColumns = rsmd.getColumnCount();

strTable = rsmd.getTableName(2);

// For each column...
for (intColumnIndex = 1; intColumnIndex <= intNumberOfColumns;
intColumnIndex++)
{
// Get the column name and put it in the hashtable
strColumnName = rsmd.getColumnName(intColumnIndex);
ht.put("Column" + intColumnIndex, strColumnName);
}

// Put the number of columns in the hashtable
ht.put("intCount", Integer.toString(intColumnIndex - 1));
}
catch (SQLException e)
{
System.out.println("GetTableColumnNames SQLException Error: " +
e.getMessage());
}
catch(Exception e)
{
System.out.println("GetTableColumnNames Exception Error: " +
e.getMessage());
}

return ht;
 
Back
Top