how do I check if a field exists in a table with sql? (sql Access database)

Answers

PeterNZ

May 29, 2007 - 09:09 PM

Try

CurrentDB.TableDefs("Table").Fields("Field").Name

this will return the field name if the column exists. Otherwise it will throw an error.

Cheers

Peter

Report it

Comments (0) | New Comment

nidhi

May 30, 2007 - 02:31 AM

There is nothing u can do in SQL, it has to be done via VBA.

quick way is as Peter suggests. But if u trap for errors then u need to make sure u get the right error number.

eg
On Error Resume Next

Debug.Print CurrentDb.TableDefs("MyTbl").Fields("MyField").Name
If Err.Number = 0 Then
Debug.Print "Field Exists"
ElseIf Err.Number = 3265 Then
Debug.Print "Field Does Not Exist"
Else
Debug.Print Err.Number, Err.Description
End If

The other way if u dont want to catch certain errors is to loop thru the field collection in the table then add it that way. U can add fields in vba as well, just in case u didnt want to bother with sql
eg

'This bit loops thru field collection and sets a flag
Dim db As DAO.Database
Dim tbl As DAO.TableDef
Dim i As Integer
Dim bFound As Boolean

bFound = False
Set db = CurrentDb
Set tbl = db.TableDefs("MyTbl")
For i = 0 To tbl.Fields.Count - 1

'I do a lowercase comparison just in case
If LCase$(tbl.Fields(i).Name) = LCase$("MyField") Then bFound = True
Next i
Debug.Print "Found", bFound

'This bit adds that field. In this case, I am adding it as text size 20
Dim fld As DAO.Field
If bFound = False Then
Set fld = tbl.CreateField("MyField", DB_TEXT, 20)
tbl.Fields.Append fld
End If

Report it

Comments (0) | New Comment

dustPuppy

Jun 03, 2007 - 10:29 PM

thanks guys.
I read that in the case of SQL Server it is actually possible, if you query the sys-tables. Anyway that option doesn't work in the access-case, so I guess I'll have to do it by code as you suggested.

Report it

Comments (0) | New Comment