vb如何在对Access创建新字段（列）的过程中⾃动添加该列
的缺省值
搞了⼀下午，终于解决了，希望能帮助到他⼈。
'通⽤-声明
Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim sql$
'连接Data.mdb数据库
Public Sub openConn()
conn.Open "driver={Microsoft Access Driver (*.mdb)};dbq=" & App.Path & "\Data.mdb;Uid=;Pwd=123;"
End Sub
'关闭Data.mdb数据库
Public Sub closeConn()
conn.Close
End Sub
array在vb什么意思啊
'查Data.mdb数据库是否存在某列字段，若不存在则添加
Sub AddColIfNotExists(tblName As String, colName As String, colType As String, DefaultValue As String)
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim cnStr As String, sqlStr As String
Dim i As Integer, addCol As Boolean
'此⽰例ACCESS数据库放在本VB程序同⼀⽬录下
cnStr = "driver={Microsoft Access Driver (*.mdb)};dbq="
cnStr = cnStr & App.Path & "\Data.mdb;Uid=;Pwd=123;"
cn.Open cnStr
rs.CursorLocation = adUseClient
sqlStr = "select * from " & tblName & " where false;"
rs.Open sqlStr, cn, adOpenStatic, adLockReadOnly
addCol = True
For i = 0 To (rs.Fields.Count - 1)
If rs(i).Name = colName Then
addCol = False
Exit For
End If
Next i
rs.Close
Set rs = Nothing
If addCol = True Then  '字段不存在才添加
sqlStr = "alter table " & tblName
sqlStr = sqlStr & " add column " & colName
sqlStr = sqlStr & " " & colType & ";"
cn.Execute sqlStr
'设置缺省值
Call AddDefaultValue(tblName, colName, DefaultValue)
End If
cn.Close
Set cn = Nothing
End Sub
'查Data.mdb数据库是否存在某列字段，若不存在则添加
Sub AddDefaultValue(tblName As String, colName As String, DefaultExt As String)
'打开数据库
Call openConn
Dim AddValue As String
AddValue = "select * from " & tblName
rs.Open AddValue, conn, adOpenKeyset, adLockOptimistic
Set rs = Nothing
sql = "update " & tblName & " set " & colName & " = " & "'" & DefaultExt & "'"  rs.Open sql, conn
'关闭数据库
Call closeConn
End Sub
'添加新列并设置该列缺省值
Private Sub Command1_Click()
'(表名，新增字段，类型，缺省值)
Call AddColIfNotExists("学⽣信息表", "添加的新字段", "varchar(50)", "abc")
End Sub

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。