Check for & delete column name

[Guest]

How can I check to see if a table has a specific column name? If the column
exists I want to drop the column, otherwise I want to leave it alone.

I thought that the column names would be in the MSysObjects table, but I
cannot find it. Can anyone poitn me in the right direction?

Thanks,

TK

 

[Guest]

If you have a Reference set to the DAO library, then the following code will
drop a column if it exists, but fail silently if the column doesn't exist.

Public Function dropCol(sTableName As String, sCol As String) As Boolean

On Error GoTo ErrHandler

Dim db As Database
Dim tbl As TableDef
Dim fld As DAO.Field
Dim fOpenedDB As Boolean
Dim fSuccess As Boolean

Set db = CurrentDb()
fOpenedDB = True
Set tbl = db.TableDefs(sTableName)
Set fld = tbl.Fields(sCol)

db.Execute "ALTER TABLE " & sTableName & " DROP " & sCol, dbFailOnError
fSuccess = True

CleanUp:

Set fld = Nothing
Set tbl = Nothing

If (fOpenedDB) Then
db.Close
fOpenedDB = False
End If

Set db = Nothing
dropCol = fSuccess

Exit Function

ErrHandler:

If (Err.Number <> 3265) Then ' Item not found in this
collection.
MsgBox "Error in dropCol( )." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
End If

Err.Clear
fSuccess = False
GoTo CleanUp

End Function

Sample usage:

Public Sub testDropCol()

MsgBox "Successfully dropped column = " & dropCol("tblPersonnel",
"NickName")

End Sub

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

 

[Brian Wilson]

How can I check to see if a table has a specific column name? If the
column
exists I want to drop the column, otherwise I want to leave it alone.

I thought that the column names would be in the MSysObjects table, but I
cannot find it. Can anyone poitn me in the right direction?

Thanks,

TK

Obviously writing your own subs and functions, you can structure your code
as you please, but here is one possibility... Paste this into a new module:


Option Compare Database
Option Explicit

Public Sub DeleteColumnIfExists(strTable As String, strColumn As String)

On Error GoTo Err_Handler

If ColumnExists(strTable, strColumn) Then
If Not DeleteColumn(strTable, strColumn) Then
' Leave function to display error message
End If
End If

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Sub


Public Function ColumnExists(strTable As String, strColumn As String) As
Boolean

On Error GoTo Err_Handler

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

Set dbs = CurrentDb

Set tdf = dbs.TableDefs(strTable)

Set fld = tdf.Fields(strColumn)

ColumnExists = True

Exit_Handler:
On Error Resume Next
Set fld = Nothing
Set tdf = Nothing
Set dbs = Nothing
Exit Function

Err_Handler:

Select Case Err.Number

Case 3265
' No Error message - just return false

Case Else
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number

End Select

Resume Exit_Handler

End Function

Public Function DeleteColumn(strTable As String, strColumn As String) As
Boolean

On Error GoTo Err_Handler

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

Set dbs = CurrentDb

Set tdf = dbs.TableDefs(strTable)

Set fld = tdf.Fields(strColumn)

tdf.Fields.Delete fld.Name

DeleteColumn = True

Exit_Handler:
On Error Resume Next
Set fld = Nothing
Set tdf = Nothing
Set dbs = Nothing
Exit Function

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Function

 

[Guest]

Thanks to both of you. Thats a big help.