How to chech if a column exists using SQL ?

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

capricorn1:
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

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

IF NOT EXISTS(SELECT * 
		  FROM sys.columns 
		  WHERE  Name = 'columnName' AND Object_ID = Object_ID('tableName'))
BEGIN

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

END

Select allOpen in new window

eridanix:
isn´t that MS SQL Code?

Of course, it is. My mistake, sorry.

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
    R.Close
End Function

Select allOpen 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

Select allOpen 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

Select allOpen 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)

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.

Sara

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%

@VBClassicGuy - I would add where 1=0 to the sql  :-)

Sorry guys I haven´t tried your code yet, tomorrow I will do this.
Thanks!

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