Check if field exists - SQL

[mrabrams2]

Hello,

I am using the following code to add a field to an existing table.

DoCmd.RunSQL "ALTER TABLE [AES GROUP AUDIT] ADD COLUMN GROUP TEXT", -1

I would like to be able to check the table first, to see if the field is already there.
pseudocode:
If fieldMyField exists then don't add the new field

Can someone guide me to the actual sql code to do this ?

Thank you,

Michael

 

[ByteMyzer]

Enter the following text into a new Code Module:

Public Function fieldExists(tableName As String, fieldName As String) As Boolean

Dim x As Integer

For x = 0 To CurrentDb.TableDefs(tblName).Fields.Count - 1
    If CurrentDb.TableDefs(tblName).Fields(x).NAME = fieldName Then fieldExists = True
Next x

End Function

Save the module as basFieldExists.

I would like to be able to check the table first, to see if the field is already there.
pseudocode:
If fieldMyField exists then don't add the new field

Use:

If fieldExists("AES GROUP AUDIT", "TEXT") = False Then
    DoCmd.RunSQL "ALTER TABLE [AES GROUP AUDIT] ADD COLUMN GROUP TEXT", -1
End If

 

[mrabrams2]

Thanks !!

Thank you ByteMyzer....

It works perfectly !!

Much appreciated!!

Michael

 

[mrabrams2]

Slight issue

Before I go and post all the code, is there a reason why this code works great in one database, but bombs out with the message: Item not found in collection" in a similar database?
Both are on the same server.
One is actually a copy of the other.
The module/function is exactly the same, and so is the code that calls it !

I can post more, but maybe this is enough to ring a bell with you?

Thank you very much.

 

[ByteMyzer]

Ahh, forgot to error-trap this one. It should be:

Public Function fieldExists(tableName As String, fieldName As String) _
    As Boolean
On Error GoTo fieldExists_Error

Dim x As Integer

For x = 0 To CurrentDb.TableDefs(tableName).Fields.Count - 1
    If CurrentDb.TableDefs(tableName).Fields(x).Name = fieldName Then _
        fieldExists = True
Next x

fieldExists_Error:

End Function

 

[mrabrams2]

Thanks again!

Now I can't wait to get back to work to fix it!!

Thank you very much ByteMyzer for sharing with all of us.

Michael

 

[mark26]

Thank you ByteMyzer. its realy helpful.

one thing i need in this

if field name already exits. its throwing error.

My requirement is.

Want to check th:confused field name in table. if exits skip and move to next query.

Thanks in advance

 

[ByteMyzer]

if field name already exits. its throwing error

What error is it throwing?

Want to check th:confused field name in table. if exits skip and move to next query.

This statement is confusing. Are you trying to use this function on tables or queries?

 

[MarkK]

Wow, this may be the oldest thread I have ever seen. In a few months that original post will be 13 years old.