Is there a way to determine if a column exists in a table?

[Ron]

My code is building SQL based on user input. Is there a way I can test to see
if a specified column really exists in a table before executing SQL that
references it?
Thanks

 

[John Nurick]

Hi Ron,

How about something like this:


Public Function DoesFieldExist( _
TableName As String, _
FieldName As String) As Boolean

Dim SQL As String
Dim rsR As DAO.Recordset
Dim F As DAO.Field

SQL = "SELECT * FROM " & TableName & " WHERE FALSE;"
Set rsR = CurrentDb.OpenRecordset(SQL)
On Error Resume Next
Set F = rsR.Fields(FieldName)
If Err.Number = 0 Then
DoesFieldExist = True
Else
DoesFieldExist = False
End If
On Error GoTo 0
rsR.Close
End Function

 

[Marshall Barton]

My code is building SQL based on user input. Is there a way I can test to see
if a specified column really exists in a table before executing SQL that
references it?

Here's some logic that applies:

Set db = CurrentDb()
Set tdf = db.TableDefs!tablename
. . .
For Each fld In tdf
If fld.Name = strfieldname Then
bolFieldExists = True
End If
Next fld
If bolFieldExists Then
'all is good
. . .
Else
'bad field name
. . .
End If
. . .
Set tdf = Nothing
Set db = Nothing

 

[Ron]

Hi John,
Thank you for the information, I'll try it.
Regards,
Ron

 

[Ron]

Thanks for your help!
Regards,
Ron

Here's some logic that applies:

Set db = CurrentDb()
Set tdf = db.TableDefs!tablename
. . .
For Each fld In tdf
If fld.Name = strfieldname Then
bolFieldExists = True
End If
Next fld
If bolFieldExists Then
'all is good
. . .
Else
'bad field name
. . .
End If
. . .
Set tdf = Nothing
Set db = Nothing