100分了：需要在一个ACCESS表中增加一个字段，如何先判断这个字段存不存在，而后如何添加这个字段

开发环境：VB6+ACCESS2003+ADO
需要在一个ACCESS表中增加一个字段，如何先判断这个字段存不存在，而后如何添加这个字段。

10 个解决方案

#1

判断字段下面是一种思路...
[code=V]
Function IsField(pTableName As String, pFieldName As String) As Boolean
    'pTableName         要搜索的表名
    'pFieldName         要判断的字段名称
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim i As Long

    Set cn = New ADODB.Connection
    cn.Open "......"    '连接数据库
    Set rs = cn.Execute("Select * From " & Trim(pTableName) & " Where 1=0")
    For i = 0 To rs.Fields.Count - 1
        If rs.Fields(i).Name = pFieldName Then
            IsField = True
            Exit For
        End If
    Next
    Set rs = Nothing
    Set cn = Nothing
End Function
[/code]

#2

添加字段也要？
执行：
cn.execute "Alter Table tableName Add Column FieldName Text(20);"
具体自己查Alter Table语句

#3

该回复于2011-02-21 10:26:13被版主删除

#4

使用dao, 非常简单:

public function fieldExist(db as Databse,tabName as string,fldName as string) as boolean
dim v
on error goto errr
v=db.tablerefs(tabName).fields(fldname)
fieldExist=true
errr:
end function

#5

on error 代码可读性差

#6

ado还可以用OpenSchema ：
[code=V]
'查询字段是否存在：存在true
Function IsField(pTableName As String, pFieldName As String) As Boolean
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Set cn = New ADODB.Connection
    cn.Open "......"    '连接数据库
    Set rs = cn.OpenSchema(adSchemaColumns, Array(Empty, Empty, pTableName, Empty))
    rs.Find "COLUMN_NAME='" & pFieldName & "'"
    IsField = Not rs.EOF
    Set rs = Nothing
    Set cn = Nothing
End Function
[/code]

也简单吧？

#7

[code=V]
'查询字段是否存在：存在true
Function IsField(pTableName As String, pFieldName As String) As Boolean
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Set cn = New ADODB.Connection
    cn.Open "......"    '连接数据库
    Set rs = cn.OpenSchema(adSchemaColumns, Array(Empty, Empty, pTableName, Empty))
    rs.Find "COLUMN_NAME='" & pFieldName & "'"
    IsField = Not rs.EOF
    Set rs = Nothing
    Set cn = Nothing
End Function

[/code]

#8

csdn总让人郁闷....
[code=VB.NE]
'查询字段是否存在：存在true
Function IsField(pTableName As String, pFieldName As String) As Boolean
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Set cn = New ADODB.Connection
    cn.Open "......"    '连接数据库
    Set rs = cn.OpenSchema(adSchemaColumns, Array(Empty, Empty, pTableName, Empty))
    rs.Find "COLUMN_NAME='" & pFieldName & "'"
    IsField = Not rs.EOF
    Set rs = Nothing
    Set cn = Nothing
End Function

[/code]

#9

首先返回字段数量
然后循环检查要添加的字段名称是否存在

#10

引用ADOX 2.8，然后用ADOX处理。

#1

#2