VB创建access数据表字段代码

VB创建access数据表字段代码

Private Sub Form_Load()

'菜单“工程”-->"引用"-->"Microsoft ActiveX Data Objects 2.7 Library"

'Microsoft ADO Ext.2.7 for DDL ado Security

Dim cat As ADOX.Catalog

Set cat = New ADOX.Catalog

cat.Create ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + App.Path & "\new data.mdb" + ";")

MsgBox "数据库已经创建成功！"

Dim tbl As ADOX.Table

Set tbl = New ADOX.Table

tbl.ParentCatalog = cat

= "MyTable"

'增加一个自动增长的字段

Dim col As ADOX.Column

Set col = New ADOX.Column

col.ParentCatalog = cat

col.Type = ADOX.DataTypeEnum.adInteger ' // 必须先设置字段类型

= "id"

col.Properties("Jet OLEDB:Allow Zero Length").Value = False

col.Properties("AutoIncrement").Value = True

tbl.Columns.Append col, ADOX.DataTypeEnum.adInteger, 0

'增加一个文本字段

Dim col2 As ADOX.Column

Set col2 = New ADOX.Column

col2.ParentCatalog = cat

= "Description"

col2.Properties("Jet OLEDB:Allow Zero Length").Value = False

tbl.Columns.Append col2, ADOX.DataTypeEnum.adVarChar, 25

'增加一个货币型字段

Dim col4 As ADOX.Column

Set col4 = New ADOX.Column

col4.ParentCatalog = cat

col4.Type = ADOX.DataTypeEnum.adCurrency

= "xx"

tbl.Columns.Append col4, ADOX.DataTypeEnum.adCurrency

'增加一个OLE字段

Dim col5 As ADOX.Column

Set col5 = New ADOX.Column

col5.ParentCatalog = cat

col5.Type = ADOX.DataTypeEnum.adLongVarBinary

= "OLD_FLD"

tbl.Columns.Append col5, ADOX.DataTypeEnum.adLongVarBinary

'增加一个数值型字段

Dim col3 As ADOX.Column

Set col3 = New ADOX.Column

col3.ParentCatalog = cat

col3.Type = ADOX.DataTypeEnum.adDouble

= "ll"

tbl.Columns.Append col3, ADOX.DataTypeEnum.adDouble

Dim p As ADOX.Property

For Each p In col3.Properties

Debug.Print & ":" & p.Value & ":" & p.Type & ":" & p.At tributes

Next

'设置主键

tbl.Keys.Append "PrimaryKey", ADOX.KeyTypeEnum.adKeyPrimary, "id", "", "" cat.Tables.Append tbl

MsgBox "数据库表：" + + "已经创建成功！"

Set tbl = Nothing

Set cat = Nothing

Dim adocon As ADODB.Connection

Set adocon = New ADODB.Connection

adocon.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\n ewdata.mdb;" & "Mode=Share Deny Read|Share Deny Write;Persist Security Info= False;Jet OLEDB:Database Password="

Set cat = New ADOX.Catalog

Set cat.ActiveConnection = adocon

For i = 0 To cat.Tables.Count - 1

Debug.Print cat.Tables(i).Name '取出表名

If cat.Tables(i).Name = "MyTable" Then

For j = 0 To cat.Tables(i).Columns.Count - 1

Debug.Print cat.Tables(i).Columns(j) '取出列名

Debug.Print cat.Tables(i).Columns(j).Type '取出数据类型

For Each p In cat.Tables(i).Columns(j).Properties

Debug.Print p.Type & & p.Attributes '取出列的属性

Next

Next j

End If

Next i