VB数据库添加字段

Private Sub Command1_Click()

Dim TabExit As Boolean

TabExit = False

Dim cn As New ADODB.Connection

cn.CursorLocation = adUseClient

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0Data Source=" &App.Path &"\db1.mdbMode=ReadWritePersist Security Info=FalseJet OLEDB:Database Password=123456"

'cn.Execute "ALTER TABLE 库存表 ADD COLUMN 生产厂家 CHAR(20)"

'cn.Execute "ALTER TABLE 库存表 ADD COLUMN 生产日期 DATETIME"

'cn.Execute "ALTER TABLE 库存表 ADD COLUMN 库存上限 Single"

cn.Execute "ALTER TABLE 库存表 ADD COLUMN ID COUNTER"

cn.Close

Set cn = Nothing

End Sub

首先打开数据源，然后使用alter table语句即可，具体范例如下：

Set cn = New ADODB.ConnectionSet rs = New ADODB.Recordsetconstr = "DRIVER=Microsoft Access Driver (*.mdb)DBQ=" &数据库路径及文件名 &""cn.Open constrDim str As Stringstr = "alter table 表名 add 字段 类型"rs.Open str, cn, adOpenStaticmsgbox"添加字段成功",vbokonly

'引用对象库“Microsoft ActiveX Data Objects 2.8 Library”

'引用对象库“Microsoft ADO Ext 2.8. For DDL Security”，简称为ADOX

Dim cat As New ADOX.Catalog

Dim pstr As String

pstr = "Provider=Microsoft.Jet.OLEDB.4.0"

pstr = pstr &"Data Source=" &App.Path &"\dat.mdb"

pstr = pstr &"Jet OLEDB:Database Password=123"

cat.Create pstr '创建数据库

Dim cat As New ADOX.Catalog '不用cat用另外一个名字也可以

Dim pstr As String

Dim tb1 As New Table

pstr = "Provider=Microsoft.Jet.OLEDB.4.0" '不能把这里的4.0改为3.51

pstr = pstr &"Data Source=" &App.Path &"\dat.mdb"

pstr = pstr &"Jet OLEDB:Database Password=123"

cat.ActiveConnection = pstr

tb1.Name = "学生信息" '表的名称

tb1.Columns.Append "姓名", adVarWChar, 20 '表的第一个字段

tb1.Columns.Append "年龄", adInteger'表的第二个字段

tb1.Columns.Append "性别", adVarWChar, 2 '表的第三个字段

tb1.Columns.Append "出生年月", adDate '表的第四个字段

cat.Tables.Append tb1'建立数据表

Dim conn As ADODB.Connection 'conn为连接

Dim rs As New ADODB.Recordset 'rs为记录集

Dim sql As String

Set conn = CreateObject("adodb.connection")

conn.Open "Provider=Microsoft.Jet.OLEDB.4.0Data Source=" &App.Path + "\dat.mdb" &"Jet OLEDB:Database Password=123" '打开数据源

sql = "select * from 学生信息 "

rs.CursorLocation = adUseClient

rs.Open sql, conn, adOpenKeyset, adLockPessimistic

'rs.MoveLast

rs.AddNew'往表中添加新记录

rs.Fields(0).Value = "李振"

rs.Fields(1).Value = 22

rs.Fields(2).Value = "男"

rs.Fields(3).Value = #6/8/1984#

rs.Update

rs.Close

conn.Close

---