Need to edge out the competition for your dream job? Train for certifications today.
Experts Exchange Solution brought to you by
"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.
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
FieldExists = False
Open in new window
Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.
Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.
IF NOT EXISTS(SELECT *
WHERE Name = 'columnName' AND Object_ID = Object_ID('tableName'))
-- here you can do what you need if column not exists
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
SELECT iif(DoesFieldExist("TBL_Name_Goes_Here","My_Random_Column_Name"),"Yep","Nope") as Column_Exists
SELECT DoesFieldExist("TBL_Name_Goes_Here","My_Random_Column_Name") as Column_Exists
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
If Err.Number = 3380 then
msgbox "Field already exists"
msgbox Err.number & vbcrlf & err.description & vbcrlf & vbcrlf & strsql, vbokonly, "Unable to add field"
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
From novice to tech pro — start learning today.
Premium members can enroll in this course at no extra cost.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Please enter a first name
Please enter a last name
Must be at least 4 characters long.
Join and Comment