Column Name from ADODB Recordset

Paula222 · February 8th, 2006, 06:58 PM

Hi ,
I have few columns in table (and few rows) , but the columns has different specific names . I also have Adodb connection , and a recordset - and i go through the rows in a loop . Now i want to get every time the name of the column and to use it in the format :
rsRecords!columnName
where column name is what i want to know how to get .

I also have another question :
i created by wizard dynamic changed text boxes from specific table . I need to know how to attach to each of these columns event OnChange for validating (or is there any other way to do validating?) input in each text box in form .
How to implement this ?

Paula .

mmcdonal · February 10th, 2006, 08:36 AM

You can do this by using this sort of code:

rsRecords.Fields(i)

where i is the number of the column, starting with 0, of course.

I don't understand your second question. You do validation at the table level, or you use regular expressions (See msdn.microsoft.com regular expressions reference) to check for values entered.

mmcdonal

Bob Bedell · February 10th, 2006, 09:40 AM

rsRecords.Field(i) syntax will get you the value of the recordset's field, but not the name of the table column. To get the name of the table column using ADO/ADOX, you have two oprions: open a second recordset set and populate it with the ADO OpenSchema metheod, or use an ADOX Table object's Column property.

1. Using ADODB.Connection and OpenSchema method

Code:

Sub ListTableAndColumnNamesADO()
  Dim Conn As New ADODB.Connection
  Dim TablesSchema As ADODB.Recordset
  Dim ColumnsSchema As ADODB.Recordset

  'Open connection you want To get database objects
  Conn.Provider = "MSDASQL"
  Conn.Open "DSN=...;Database=...;", "UID", "PWD"

  'Get all database tables.
  Set TablesSchema = Conn.OpenSchema(adSchemaTables) 
  Do While Not TablesSchema.EOF
    'Get all table columns.
    Set ColumnsSchema = Conn.OpenSchema(adSchemaColumns, _
      Array(Empty, Empty, "" & TablesSchema("TABLE_NAME")))
    Do While Not ColumnsSchema.EOF
      Debug.Print TablesSchema("TABLE_NAME") & ", " & _
        ColumnsSchema("COLUMN_NAME")
      ColumnsSchema.MoveNext
    Loop
    TablesSchema.MoveNext
  Loop
End Sub

TablesSchema fields : TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, TABLE_GUID, DESCRIPTION, TABLE_PROPID, DATE_CREATED, DATE_MODIFIED

ColumnsSchema fields : TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLUMN_GUID, COLUMN_PROPID, ORDINAL_POSITION, COLUMN_HASDEFAULT, COLUMN_DEFAULT, COLUMN_FLAGS, IS_NULLABLE, DATA_TYPE, TYPE_GUID, CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE, DATETIME_PRECISION, CHARACTER_SET_CATALOG, CHARACTER_SET_SCHEMA, CHARACTER_SET_NAME, COLLATION_CATALOG, COLLATION_SCHEMA, COLLATION_NAME, DOMAIN_CATALOG, DOMAIN_SCHEMA, DOMAIN_NAME, DESCRIPTION, SS_DATA_TYPE

2. Using ADOX.Catalog and its collections

Code:

Sub ListTableandColumNamesADOX()
  Dim Conn As New ADODB.Connection

  'Open connection you want To get database objects
  Conn.Provider = "MSDASQL"
  Conn.Open "DSN=...;Database=...;", "UID", "PWD"

  'Create catalog object
  Dim Catalog As New ADOX.Catalog
  Set Catalog.ActiveConnection = Conn

  'List tables And columns
  Dim Table As ADOX.Table, Column As ADOX.Column
  For Each Table In Catalog.Tables
    For Each Column In Table.Columns
      Debug.Print Table.Name & ", " & Column.Name
    Next
  Next
End Sub

Bob

Paula222 · February 10th, 2006, 12:08 PM

Thanks ,
the ADOX method was useful with what i wanted .