Check Column is exist

[Goh]

Hi,
How can we check column in MS Access database table is exist or not?
The action that I would like to do is when the column is not exist in the
database then I will alert add column to that database. But I found know why
for me check the column exist or not.


Please help
Thanks in advance,
Goh

 

[david epsom dot com dot au]

dim db as dao.database
dim rs as dao.recordset
dim fld as dao.field

set rs = db.openrecordsource("tablename")
for each fld in rs.fields
if fld.name = "fred" then blnFound = true
next fld

if not blnFound then msgbox "not found"


(david)

 

[Brendan Reynolds]

There are several ways to do this. Here's one of them ...

Public Function DoesColExist( _
ByVal TableName As String, _
ByVal ColName As String) As Boolean

Dim rst As ADODB.Recordset
Set rst = CurrentProject.Connection.OpenSchema( _
adSchemaColumns, Array(Null, Null, TableName, ColName))
If rst.BOF And rst.EOF Then
DoesColExist = False
Else
DoesColExist = True
End If

End Function

Examples of use, in Immediate Window ...

? doescolexist("Table1", "TestText")
True
? doescolexist("Table1", "NoSuchCol")
False