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).

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
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..
Dale FyeOwner, Developing Solutions LLCCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
You can return all attributes without values by using something like:

FROM Table1
where 1=2
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

dimensionavAuthor Commented:
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

dimensionavAuthor Commented:
isn´t that MS SQL Code?
Of course, it is. My mistake, sorry.
Mark WillsTopic AdvisorCommented:
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 AdvisorCommented:
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 :
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.

Vadim RappCommented:
"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.
Rey Obrero (Capricorn1)Commented:
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

Vadim RappCommented:
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"
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%
Vadim RappCommented:
@VBClassicGuy - I would add where 1=0 to the sql  :-)
dimensionavAuthor Commented:
Sorry guys I haven´t tried your code yet, tomorrow I will do this.
Dale FyeOwner, Developing Solutions LLCCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.