Does The Field Exist

[Hackster]

I know how to tell whether a table within an Access database exists or not (using VB and ADO). My question is, using VB and ADO, how can I tell whether or not a specific field within a specific table exists?

 

[jebenson]

You can use ADOX, just do a search of this forum or Google for "ADOX" and you will get a lot of info. Also, here's a thread in this forum that might help you get started:

thread222-846120

Another way is possible if you know the table name and the field name:

Dim rs as New Recordset
Dim FieldFound as Boolean
dim f as integer

FieldFound = False

rs.Open "SELECT * FROM TableName WHERE 1=0", ConnectionObject

For f = 0 to rs.Fields.Count - 1
    If rs.Fields(f).Name = "FieldName" Then
        FieldFound = True
    End If
Next f

Hope this helps.

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

 

[bjd4jc]

I would use ADOX to do this. Add a reference to Microsoft ADO Ext. 2.x for DDL And Security.

Dim cat as Catalog
Dim fld as Field

Set cat = New ADOX.Catalog

cat.ActiveConnection = con 'Open ADO Connection object

For Each fld in cat.Tables("TableName")
    If fld.Name = "Your Field Name" Then
         'Field Exists
    End If
Next

Or if you already have a recordset of the able open you can use the recordset object for this

dim fld As Field
set rs = con.Execute("SELECT * FROM Table1")

For Each fld in rs.Fields
    If fld.Name = "Your name" Then
         'field exists
    End If
Next

 

[Hackster]

Thank you both. I like both approaches and will see which best suites my current needs. Thanks again!