check if field exists

[laailalalaa]

hello,

before adding a column to a table i want to check if that column doesn't already exist in the table. is there a way to do this in access?

the only way i can think of is through vb.net (populate datatable and check the columns), which is pretty straightforward, but i'd like to know whether it is possible to perform this task using plain sql.

thank you

 

[namliam]

if executing a query with a column name fails... you know the column doesnt exist

VB = VBA though.... what ever is easy in VB is easy in VBA

 

[laailalalaa]

i know the query would fail, i failed to specify i don't wanna take this approach.

i thought there is some SqlServer-like table schema data. but i don't know of any access "system views" that make available this kind of data.

 

[DCrake]

A sloppy way to do this is to have a hidden listbox on your form that has the query as the rowsource. Set the rowsource type to be field list. Then if you use vba to enumerate throught the lists contents to see if the named field exists.

David

 

[namliam]

In the menu while in the database screen: Tools > Options
The check the "System objects" to be visible, you can then see and browse the tables.
NOTE: The use of these tables is "not supported by M$"

 

[laailalalaa]

In the menu while in the database screen: Tools > Options
The check the "System objects" to be visible, you can then see and browse the tables.
NOTE: The use of these tables is "not supported by M$"

not quite what i'm looking for, but thanks for your answer

 

[ghudson]

Verify field in table exists

Not SQL but this works...

Public Function VerifyFieldExists(ByVal sfieldName As String, ByVal stableName As String) As Boolean

    Dim db As Database
    Dim tbl As TableDef
    Dim fld As Field
    Dim sName As String
    
    Set db = CurrentDb
    Set tbl = db.TableDefs(stableName)
    
    For Each fld In tbl.Fields
        If fld.Name = sfieldName Then
            VerifyFieldExists = True
            Exit For
        End If
    Next

     If VerifyFieldExists Then
         MsgBox "Field Name " + sfieldName + " Exists in " + stableName
     Else
        MsgBox "Field Name Does Not Exist"
     End If

End Function

 

[gemma-the-husky]

this should work

function fieldexists(tablename as string, fieldname as string) as boolean
dim exists as boolean

   exists=false
   on error resume next
   exists = currentdb.tabledefs(tablename).fields(fieldname).name = fieldname

   fieldexists = exists
end sub