ExcelVBA与数据库Access整合笔记.docx
《ExcelVBA与数据库Access整合笔记.docx》由会员分享，可在线阅读，更多相关《ExcelVBA与数据库Access整合笔记.docx（304页珍藏版）》请在冰豆网上搜索。
ExcelVBA与数据库Access整合笔记
一、创建数据库
1-1利用DAO创建数据库和数据表
首先建立对DAO对象库MicrosoftDAO3.6ObjectLibrary的引用.在VBA界面下:
工具-à引用,选中”MicrosoftDAO3.6ObjectLibrary”
PublicSub1_1（）
DimmyDbAsDAO.Database‘定义DAO的Database（数据库）对象变量
DimmyTblAsDAO.TableDef‘定义DAO的TableDef（数据表）对象变量
DimmyDataAsString‘定义数据库名称变量
DimmyTableAsString‘定义数据表名称变量
‘设置要创建的数据库名称（包括完整路径）
myData=ThisWorkbook.Path&“\学生成绩管理.mdb”
‘设置要创建的数据表名称
myTable=”期末成绩”
‘删除已经存在的数据库文件
onerrorresumenext
killmyData
onerrorgoto0
‘创建数据库
SetmyDb=CreateDatabase（myData,dbLangChineseSimplified）
‘创建数据表
SetmyTbl=myDb.CreateTableDef（myTable）
‘为创建的数据表添加各个字段
WithmyTbl
.Fields.Append.CreateField（“学号”,dbText,10）
.Fields.Append.CreateField（“姓名”,dbText,6）
.Fields.Append.CreateField（“性别”,dbText,1）
.Fields.Append.CreateField（“班级”,dbText,10）
.Fields.Append.CreateField（“数学”,dbSingle）
.Fields.Append.CreateField（“语文”,dbSingle）
.Fields.Append.CreateField（“物理”,dbSingle）
.Fields.Append.CreateField（“化学”,dbSingle）
.Fields.Append.CreateField（“英语”,dbSingle）
.Fields.Append.CreateField（“总分”,dbSingle）
EndWith
‘将创建的数据表添加到数据库的TableDefs集合中
myDb.TableDefs.AppendmyTbl
‘关闭数据库，并释放变量
myDb.Close
SetmyDb=Nothing
SetmyTbl=Nothing
Endsub
1-2利用ADOX创建数据库和数据表
引用：
microsoftADOExt.2.XforDDLandSecurity
代码：
publicsub1_2（）
dimmycatasnewadox.catalog‘定义ADOX的Catalog对象变量
dimmytblasnewtable‘定义table对象变量
dimmydataasstring‘定义数据库名称变量
dimmytableasstring‘定义数据表名称变量
‘设置要创建的数据库名称（包括完整路径）
mydata=thisworkbook.path&“\学生成绩管理.mdb”
‘设置要创建的数据表名称
mytable=”期末成绩”
‘删除已经存在的数据库文件
onerrorresumenext
killmydata
onerrorgoto0
‘创建新的数据库
mycat.create”provider=microsoft.jet.oledb.4.0;datasource=”&mydata
‘创建数据表，并添加字段
withmytbl
.name=mytable
.columns.append“学号”,advarwchar,10
.columns.append“姓名”,advarwchar,6
.columns.append“性别”,advarwchar,1
.columns.append“班级”,advarwchar,10
.columns.append“数学”,adSingle
.columns.append“语文”,adSingle
.columns.append“物理”,adSingle
.columns.append“化学”,adSingle
.columns.append“英语”,adSingle
.columns.append“总分”,adSingle
Endwith
‘将创建的数据表添加到ADOX的Tables集合中
mycat.tables.appendmytbl
‘释放变量
setmycat=nothing
setmytbl=nothing
endsub
注：
在VB中，常用的数据访问接口有下列三种：
数据库访问对象（DAO，DataAccessobject）、
远程数据库对象（RDO,RemoteDataObject）和ActiveX数据对象（ADO,ActiveXDataObject）
ADOX常用方法
•Append方法：
可以创建columns,groups,indexes,keys,procedures,tables,users,views等
为数据表添加字段：
mytbl.columns.append字段名，数据类型，字段长度
将创建的数据表添加到ADOX的Tables集合中的语句是：
Mycat.tables.appendmytbl
•Create方法：
创建一个新的数据库的语句：
Mycat.create“provider=Microsoft.jet.oledb.4.0;datasource=”&mydata
•Delete方法：
删除数据表：
Mycat.tables.delete数据表名
•Refresh方法：
用于更新集合中的对象
1-3利用SQL语句创建数据库和数据表
首先引用：
microsoftactiveXdataobjects2.Xlibrary和microsoftadoext.2.xforddlandsecurity”
代码：
publicsub1_3（）
dimmycatasnewadox.catalog‘定义ADOX的Catalog对象变量
Dimmycmdasnewmand‘定义Command对象变量
dimmydataasstring‘定义数据库名称变量
dimmytableasstring‘定义数据表名称变量
dimSQLasstring
‘设置要创建的数据库名称（包括完整路径）
mydata=thisworkbook.path&“\学生成绩管理.mdb”
‘设置要创建的数据表名称
mytable=”期末成绩”
‘删除已经存在的数据库文件
onerrorresumenext
killmydata
onerrorgoto0
‘创建数据库文件
mycat.create“provider=microsoft.jet.oledb.4.0;Datasource=”&mydata
‘设置数据库连接
setmycmd.activeconnection=mycat.activeconnection
‘设置创建数据表的SQL语句
SQL="CREATETABLE"&myTable_
&"（学号text（10）,姓名text（6）,性别text
（1）,班级text（10）,"_
&"数学Single,语文Single,物理Single,化学Single,"_
&"英语Single,总分Single）"
‘利用execute方法创建数据表
withmycmd
.commandtext=sql
.execute,,adcmdtext
endwith
‘释放变量
setmycat=nothing
setmycmd=nothing
endsub
注：
有两种方法来创建数据表：
•利用ADODB.Command对象的commandtext属性和execute方法：
dimmycmdasnewmand
setmycmd.activeconnection=mycat.activeconnection
withmycmd
.commandtext=SQL
.execute,,adcmdtext
endwith
•利用ADODB.Connection对象的execute方法来生成几个记录集
Dimcnnasnewadodb.connection
dimrsasnewadodb.recordset
setcnn=mycat.activeconnection
setrs=cnn.execute（sql）
1-4在已有的数据库中创建数据表（DAO）
引用DAO对象库：
microsoftDAO3.6objectlibrary
代码：
publicsub1_4（）
dimmydbasdao.database‘定义DAO的database（数据库）对象变量
dimmydataasstring‘定义数据库名称变量
dimmytableasstring‘定义数据表名称变量
‘设置数据库名称（包括完整路径）
mydata=thisworkbook.path&“\学生成绩管理.mdb”
‘设置要创建的数据表名称
mytable=”期末成绩”
‘打开数据库
setmydb=opendatabase（mydata）
‘删除数据库中已经存在的数据表
mydb.tabledefs.deletemytable
‘创建新的数据表
setmytbl=mydb.createtabledef（mytable）
‘以下与1-1相同
endsub
•补充：
opendatabase方法用来打开一个已有的数据库，返回一个数据库对象，并自动将该数据库对象加入到数据库对象集中。
setdatabase=workspace.opendatabase（databasename,options,read-only,connect）
workspace:
定义的Workspace类型变量，它表示所使用的工作环境，将包含新的数据库对象
databasename:
一个有效的Jet数据库文件或ODBC数据源
options:
T/F,T表示以独占方式打开数据库，而F表示以共享方式打开数据库
read-only:
是否以只读方式打开数据库，为T/F
connect:
说明不同连接方式以及密码
•扩展：
利用DAO打开有密码的Access数据库
setmydb=opendatabase（mydata,true,false,”;pwd=12345”）
1-5在已有的数据库中创建数据表（ADOX）
引用：
microsoftADOExt.2.xforddlandsecurity
代码：
publicsub1_5（）
Dimmycatasnewadox.catalog‘定义ADOX的catalog对象变量
dimmytblasnewtable‘定义table对象变量
dimmydataasstring‘定义数据库名称变量
dimmytableasstring‘定义数据表名称变量
‘设置数据库名称（包括完整路径）
mydata=thisworkbook.path&“\学生成绩管理.mdb”
‘设置要创建的数据表名称
mytable=”期末成绩”
‘建立与数据库的连接
mycat.activeconnection=”provider=microsoft.jet.oledb.4.0;”_
&“datasource=”&mydata
‘删除数据库中已经存在的数据表
mycat.table.deletemytable
‘创建数据表，并添加字段
‘以下与1-2同
endsub
注：
Activeconnection属性用来指示catalog所属的ADO
Connection对象，表示到数据源的打开的连接。
1-6在已有的数据库中创建数据表（SQL，Command对象）
引用:
microsoftactivexdataobjects2.xlibrary和microsoftadoext.2.xforddlandsecurity
代码：
publicsub1_6（）
dimmycatasnewadox.catalog‘定义adox的catalog对象变量
dimmycmdasnewmand‘定义command对象变量
dimmydataasstring‘定义数据库名称变量
dimmytableasstring‘定义数据表名称变量
dimsqlasstring
‘设置数据库名称（包括完整路径）
mydata=thisworkbook.path&“\学生成绩管理.mdb”
‘设置要创建的数据表名称
mytable=”期末成绩”
‘建立与数据库的连接
mycat.activeconnection=”provider=microsoft.jet.oledb.4.0;”_
&“datasource=”&mydata
‘删除数据库中已经存在的数据表
mycat.tables.deletemytable
‘设置数据库连接
setmycmd.activeconnection=mycat.activeconnection
‘设置创建数据表的SQL语句
SQL=”CREATETABLE”&mytable_
&“（学号text（10）,姓名text（6）,性别text
（1）,班级text（10）,”_
&“数学single,语文single,物理single,化学single,“_
&“英语single,总分single）”
‘利用Execute方法创建数据表
Withmycmd
.commandtext=sql
.execute,,adcmdtext
Endwith
‘释放变量
Setmycat=nothing
Setmycmd=nothing
‘弹出信息
Msgbox“数据表<”&mytable&“>创建成功！
”,vbinformation,”创建数据表”
Endsub
1-7在已有的数据库中创建数据表（SQL，Recordset对象）
引用：
MicrosoftActiveXDataObjects2.Xlibrary
代码：
publicsub1_7（）
dimcnnasnewadodb.connection‘定义connection对象变量
dimrsasnewadodb.recordset‘定义Recordset对象变量
dimmydataasstring‘定义数据库名称变量
dimmytableasstring‘定义数据表名称变量
dimsqlasstring
‘设置数据库名称（包括完整路径）
mydata=thisworkbook.path&“\学生成绩管理.mdb”
‘设置要创建的数据表名称
mytable=”期末成绩”
‘建立与数据库的连接
withcnn
.provider=”microsoft.jet.oledb.4.0”
.openmydata
endwith
‘删除数据库中已经存在的数据表
SQL=”droptable”&mytable
setrs=cnn.execute（sql）
‘设置创建数据表的SQL语句
sql=”createtable”&mytable_
&“（学号text（10）,姓名text（6）,性别text
（1）,班级text（10）,”_
&“数学single,语文single,物理single,化学single,”_
&“英语single,总分single）”
‘利用execute方法创建数据表
setrs=cnn.execute（sql）
‘关闭数据库
cnn.close
‘释放变量
setmycat=nothing
setrs=nothing
setcnn=nothing
‘弹出信息
msgbox“数据表<”&mytable&“>创建成功!
”,vbinformation,”创建数据表”
endsub
1-10利用工作表数据创建数据表（ADOX）
数据表结构
A
B
C
D
1
数据表名称
期中成绩
2
3
字段名称
字段类型
字段大小
是否索引
4
学号
adVarWChar
10
是
5
姓名
adVarWChar
6
6
性别
adVarWChar
1
7
班级
adVarWChar
10
8
数学
adSingle
9
语文
adSingle
10
物理
adSingle
11
化学
adSingle
12
英语
adSingle
13
总分
adSingle
14
考试日期
adDate
代码：
PublicSub1_10（）
DimmyCatAsNewADOX.Catalog‘定义catalog变量
DimmyTableAsNewADOX.Table‘定义table变量
DimmyColumnAsADOX.Column‘定义column变量
DimmyIdxAsNewADOX.Index‘定义index变量
DimwsAsWorksheet‘定义worksheet变量
DimiAsLong
DimmyDataAsString‘
myData=ThisWorkbook.Path&"\学生成绩管理.mdb"‘指定数据文件
‘判断是否有保存数据表资料的工作表存在
OnErrorResumeNext
Setws=Worksheets（"数据表设计"）
OnErrorGoTo0
IfwsIsNothingThen
MsgBox"没有数据表资料存在！
",vbCritical,"警告"
ExitSub
EndIf
ws.Activate
‘建立与数据库的连接
myCat.ActiveConnection="provider=microsoft.jet.oledb.4.0;"_
&"datasource="&myData
‘删除已经存在的数据表
OnErrorResumeNext
myCat.Tables.Deletews.Range（"B1"）.Value
OnErrorGoTo0
‘建立索引
myIdx.Name="PrimaryKey"
myIdx.PrimaryKey=True
‘开始根据工作表的数据创建数据表
WithmyTable
.Name=ws.Range（"B1"）.Value
Fori=4Tows.Range（"A65536"）.End（xlUp）.Row
SetmyColumn=NewColumn
WithmyColumn
.Name=ws.Cells（i,1）.Value
.Type=GetConstNo（ws.Cells（i,2）.Value）
Ifws.Cells（i,3）.Value>0Then
.DefinedSize=ws.Cells（i,3）.Value
.Attributes=adColNullable
EndIf
EndWith
.Columns.AppendmyColumn
Ifws.Cells（i,4）.Value="是"Then
myIdx.Columns.Appendws.Cells（i,1）.Value
EndIf
Next
EndWith
‘将表定义进行保存
myCat.Tables.AppendmyTable
myTable.Indexes.AppendmyIdx
‘弹出信息
MsgBox"数据表<"&ws.Range（"B1"）.Value&">创建成功！
",_
vbOKOnly+vbInformation,"创建数据表"
‘关闭连接，并释放变量
Setws=Nothing
SetmyIdx=Nothing
SetmyTable=Nothing
SetmyCat=Nothing
EndSub
‘将工作表中定义的数据类型（字符串型）转换为字段类型VBA常量，即编制一个自定义函数GetConstNo
FunctionGetConstNo（myStrAsString）AsInteger
SelectCasemyStr
Case"adBigInt":
GetConstNo=20
Case"adBinary":
GetConstNo=128
Case"adBoolean":
GetConstNo=11
Case"adBSTR":
GetConstNo=8
Case"adChapter":
GetConstNo=136
Case"adChar":
GetConstNo=129
Case"adCurrency":
GetConstNo=6
Case"adDate":
GetConstNo=7
Case"adDBDate":
GetConstNo=133
Case"adDBTime":
GetConstNo=134
Case"adDBTimeStamp":
GetConstNo=135
Case"adDecimal":
GetConstNo=14
Case"adDouble":
GetConstNo=5
Case"adEmpty":
GetConstNo=0
Case"adError":
GetConstNo=10
Case"adFileTime":
升级会员 