[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More
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.
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.
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.