常用VB操作ACCESS数据库代码

常⽤VB操作ACCESS数据库代码

'VB引⽤项⽬如下:

'Microsoft ADO Ext. 2.8 for DDL and Security'Microsoft ActiveX Data Objects 2.8 Library'COM+ Services Type Library'Microsoft DAO 3.6 Object Library

Sub CreateDatabase(mdbPath, mdbPassword)

Dim cat As New ADOX.CatalogIf mdbPassword = \"\" Then

cat.Create \"Provider=Microsoft.Jet.OLEDB.4.0;Password=;Data Source\" & mdbPath & \";\"Else

cat.Create \"Provider=Microsoft.Jet.OLEDB.4.0;Password=\" & mdbPassword & \";Data Source=\"& mdbPath & \";\"

'cat.Create \"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\" & mdbPath & \";\"End If

MsgBox \"数据库\" & mdbPath & \"建⽴成功\'Set wspDefault = DBEngine.Workspaces(0)

'Set dbs = wspDefault.CreateDatabase(\"Newdb.mdb\'dbLangGeneral & \";pwd=NewPassword\End Sub

Sub CreateTable(mdbPath, mdbTableName, mdbSqlColumns, mdbPrimaryKey)'建⽴列的sql语句

'mdbPath=\"c:\est.mdb\"'mdbTableName=\"User\"

'mdbSqlColumns=\"ID,adInteger;UserName,adVarWChar,20;Password,adVarWChar,20\"'mdbPrimaryKey=\"ID\"Dim tbl As New Table

Dim cat As New ADOX.CatalogDim con As ADODB.Connection

cat.ActiveConnection = \"Provider=Microsoft.Jet.OLEDB.4.0;Password=;Data Source=\" &mdbPath & \";\"

Dim ArrayColumn As Stringtbl.Name = mdbTableName

'添加数据表字段(字段格式:字段名 字段类别，字段长度；最末字段没有分号) ArrayTotalColumn = Split(mdbSqlColumns, \";\ For i = 0 To UBound(ArrayTotalColumn)

ArraySingleColumn = Split(ArrayTotalColumn(i), \ If UBound(ArraySingleColumn) = 1 Then

'tbl.Columns.Append ArraySingleColumn(0), ArraySingleColumn(1) tbl.Columns.Append ArraySingleColumn(0), adInteger Else

'tbl.Columns.Append ArraySingleColumn(0), ArraySingleColumn(1), ArraySingleColumn(2) tbl.Columns.Append ArraySingleColumn(0), adVarWChar, CInt(ArraySingleColumn(2)) End If Next

'设置数据表主键

'tbl.Columns(mdbPrimaryKey).Properties(\"AutoIncrement\") = Truecat.Tables.Append tbl

'设置列的必填属性为“否”

'tbl.Columns(\"Weight\").Attributes = adColNullable'设置列的允许空字符串为“是”

'tbl.Columns(\"FirstName\").Properties(\"Jet OLEDB:Allow Zero Length\") = True Set tbl = Nothing Set cat = Nothing

MsgBox \"数据表\" & mdbPath & \"-\" & mdbTableName & \"建⽴成功\End Sub

'这个准备编写成⼀个类。

'其实作⽤跟我上次编写的ASP操作ACCESS数据库⼤全性质和功能基本相似[Page]'数据库--新建，删除，压缩，修复，设置密码，数据库⽂件存在判断，读取''数据表--新建，删除，数据表名称读取，判断，删除，更新

'数据字段--新建，修改，主键，允许为空，长度，外键，索引，字段名列表，判断，属性设置