首页 > 学术百科

vb如何在对Access创建新字段(列)的过程中自动添加该列的缺省值

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

'查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

本文发布于:2024-09-20 20:23:26，感谢您对本站的认可！

本文链接：https://www.17tex.com/xueshu/685133.html

上一篇：浅析《围城》英译的文化缺省及补偿对策——以珍妮·凯利(

下一篇：关联顺应论下《伤寒论》英译本文化缺省对比研究

标签：数据库存在添加希望

留言与评论（共有 0 条评论）