We help IT Professionals succeed at work.

How to chech if a column exists using SQL ?


I would like to know how to check if a column exists in a table using SQL (I am using a ms access database).

Watch Question

Top Expert 2016

you will get an error if you use a field name in SQL statement that does not exists in the table..

in QBE, you will get a parameter prompt
in VBA, you will get too many parameter expected .. error

please clarify your question..
Owner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
Unlike SQL Server, there is no direct SQL statement that will determine whether a specific field exists in a table.  However, you could write a function:

Public Function FieldExists(FieldName As String, TableName As String) As Variant

    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    On Error GoTo ProcError
    FieldExists = True
    Set db = CurrentDb
    Set tdf = db.TableDefs(TableName)
    Set fld = tdf.Fields(FieldName)
    If Not fld Is Nothing Then Set fld = Nothing
    If Not tdf Is Nothing Then Set tdf = Nothing
    If Not db Is Nothing Then Set db = Nothing
    Exit Function
    FieldExists = False
    Resume ProcExit
End Function

Open in new window

And if you need to do this in a SQL statement, then you could call the function from within a SQL query.
You can return all attributes without values by using something like:

FROM Table1
where 1=2


I need to create the field in case the table doesn´t contain it, I was wondering if I use SQL I could do this, but I believe I can´t

I am using VB 6 to get that information, would you mind to post an example in that language?


		  FROM sys.columns 
		  WHERE  Name = 'columnName' AND Object_ID = Object_ID('tableName'))

   -- here you can do what you need if column not exists


Open in new window


isn´t that MS SQL Code?

Of course, it is. My mistake, sorry.
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

I am sure the Access experts will give you a more precise answer, but you can always try building a function and then using the function in the query :

Function DoesFieldExist(TblName As String, Fldname As String) As Boolean
    Dim R As Recordset
    Set R = CurrentDb.OpenRecordset(TblName)
    On Error Resume Next
    If R.Fields(Fldname) Is Nothing Then DoesFieldExist = False Else DoesFieldExist = True
End Function

Open in new window

and then your query might look something like :

SELECT iif(DoesFieldExist("TBL_Name_Goes_Here","My_Random_Column_Name"),"Yep","Nope") as Column_Exists

Open in new window

or even more simply (remember 0 = false and -1 = true)

SELECT DoesFieldExist("TBL_Name_Goes_Here","My_Random_Column_Name") as Column_Exists

Open in new window

but we dont first check for a valid table name - that is assumed to be a real one (and you can check against the access system table MSysObjects)

Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018
Oh, just noticed the need to create a field... Which really is a different question... And looking back, my solution above is not so different from fyed's (sorry about that)

Anyway, for your new question, have a quick read of : http://www.eraserve.com/tutorials/MS_ACCESS_VBA_Create_Field.asp
Top Expert 2016

i am used to make updates of database fields and tables by running update scripts depending on a version number i have stored in a separate table. so when my application starts it checks database version. if that is not up-to-date it runs an update script programmatically or alternatively gives an error message and exits.

"I need to create the field in case the table doesn´t contain it, I was wondering if I use SQL I could do this, but I believe I can´t"

you can, using ALTER TABLE statement (details in Access Help)

CurrentDb.Execute "alter table table1 add column c2 integer"

"I am using VB 6 to get that information, would you mind to post an example in that language?"

Access is using VBA, which is the subset of VB; so the code from http:#37243760 should work, after you add DAO reference to VB project.
Top Expert 2016
here is function that will return true or false

Function HasField(sTable As String, sFld As String) As Boolean
On Error Resume Next
    HasField = (CurrentDb.TableDefs(sTable).Fields(sFld).Name = sFld)

End Function

to use the function place this codes in a sub or click event of a button

dim sTable as string, sFld as string

if hasfield(sTable, sFld) then
   'do nothing
  'add the field
    CurrentDb.TableDefs(sTable).Fields.Append _
         CurrentDb.TableDefs(stable).CreateField(sFld, dbText)
end if

Here's a sample of the code in VB6:

    Dim dbe As New DBEngine, mydb As Database
    Set mydb = dbe.OpenDatabase("C:\Documents and Settings\myself\Desktop\db.mdb")
    On Error Resume Next
    mydb.Execute "alter table table1 add column c3 integer"
Top Expert 2009

Yet another method, that I use:

I feed the table name to the function as Table$, the field as field$, then open the database. Then...

   SQL$ = "SELECT * FROM [" & Table$ & "]"
   rs.Open SQL$, ssiDB, adOpenForwardOnly, adLockReadOnly
   For i% = 0 To rs.Fields.Count - 1
      If UCase$(rs.Fields(i%).Name) = UCase$(field$) Then
         booFound = True
         Exit For
      End If
   Next i%
@VBClassicGuy - I would add where 1=0 to the sql  :-)


Sorry guys I haven´t tried your code yet, tomorrow I will do this.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
The best way I have found to deal with this is to just try to add the new column.  Here is a subroutine I have in the code that I use to field new software:
Public Sub AddField(TableName as String, FieldName as String, Datatype as string)

    Dim strSQL as string

    On Error GoTo ProcError

    strSQL = "ALTER Table [" & TableName & "] Add Column [" & FieldName & "] " & Datatype
    'remove potential extra brackets, in case user passed the table or field with brackets
    strSQL = Replace(Replace(strSQL, "[[", "["), "]]", "]")
    currentdb.Execute strSQL, dbfailonerror 

    Exit Sub

    If Err.Number = 3380 then
        msgbox "Field already exists"
        msgbox Err.number & vbcrlf & err.description & vbcrlf & vbcrlf & strsql, vbokonly, "Unable to add field"
    End If

End Sub

Open in new window

You can just call this subroutine to add the column and if it generates error 3380, you know the field already exists.  Make sure you use the correct string to define the data type, you can see the appropriate values for these in Access Help.
VB6 code:
Public Function FieldExists(ByVal TableName As String, ByVal FieldName As String,conn As ADODB.Connection) As Boolean
   Dim rst As ADODB.Recordset
   Set rst = conn.OpenSchema(adSchemaColumns, Array(Empty, Empty, TableName, FieldName))
   FieldExists = Not rst.EOF
End Function

Open in new window

PS. Using (assuming you already open your connection);
  If Not FieldExists("Users", "Remarks",myConn) Then
      myConn.Execute "ALTER TABLE Users ADD COLUMN Remarks TEXT"
   End If