Excel_VBA与数据库(Access)整合笔记

精品

目录

一、

1-1

创建数据库...........................................................................................................................7

利用DAO创建数据库和数据表.................................................................................7

1-2利用ADOX创建数据库和数据表..................................................................................8

ADOX常用方法...........................................................................................................................9

1-3利用SQL语句创建数据库和数据表.............................................................................9

1-4在已有的数据库中创建数据表（DAO）......................................................................10

1-5在已有的数据库中创建数据表（ADOX）...................................................................11

1-6在已有的数据库中创建数据表（SQL，Command对象）....................................12

1-7在已有的数据库中创建数据表（SQL，Recordset对象）.....................................13

1-10利用工作表数据创建数据表（ADOX）.....................................................................14

1-11利用工作表数据创建数（ADO+SQL）..................................................17

1-12利用工作表数据创建数据表（DAO）........................................................................18

二、获取数据库中的信息.................................................................................................................20

2-1检查数据表是否存在（ADO）.......................................................................................20

实例2-2检查数据表是否存在（ADOX）.........................................................................23

实例2-3检查数据表是否存在（DAO）............................................................................24

实例2-5获取数据库中所有表的名称和类型（ADO）...................................................25

实例2-6获取数据库中所有表的名称和类型（ADOX）................................................25

实例2-7获取数据库中所有的表的名称（DAO）...........................................................26

实例2-12检查某字段是否存在（ADO）..........................................................................27

·扩展：使用connection对象的find方法来查某个字段是否存在........................28

获取数据库中某数据表的所有字段信息................................................................................29

感谢下载载

精品

实例2-20获取数据库的所有查询信息（ADOX）..........................................................29

实例2-21获取数据库的所有查询信息（DAO）.............................................................30

实例2-22获取数据库的模式信（openschema）.....................................31

实例2-23获取表的创建日期和最后更新日期（ADOX）..............................................32

实例2-24获取表的创建日期和最后更新日期（DAO）.................................................33

三、SQL各种查询操作....................................................................................................................34

实例3-1将数据库数据导入到excel工作表（ADO之一）.........................................34

实例3-2数据库数据导入到excel工作表（ADO，之三）..........................................36

实例3-4将数据库数据导入到Excel工作表（DAO,之一）.........................................37

实例3-6将数据库数据导入工作表（QueryTable集合）............................................38

实例3-7数据库某些字段数据导入到Excel工作表（ADO）.....................................39

实例3-9查询前面的若干条记录..........................................................................................40

实例3-11查询不重复的字段记录（DISTICT）.......................................41

实例3-12利用Like运算符进行模糊查询.......................................................................42

实例3-13查询某一区间内的记（BETWEE）...........................................44

实例3-14查询存在于某个集合里面的记录（I）..........................................................45

实例3-15将查询结果进行排序（ORDERBY）..............................................................46

实例3-16进行复杂条件的查询（WHERE）.................................................47

实例3-17利用合计函数进行查询（查询最大值和最小值）.........................................49

实例3-18利用合计函数进行查询(查询合计值和平均值)...............................................50

实例3-19将一个查询结果作为查询条件进行查询..........................................................51

实例3-20将查询结果进行分组（GROUPBY）.............................................................52

实例3-21查询结果进行分组（HAVIG）.....................................................................54

感谢下载载

精品

实例3-22通过计算列进行查询...........................................................................................55

实例3-23使用ISULL运算符进行查询........................................................................56

实例3-24使用COUT函数进行查询............................................................................57

实例3-25使用FIRST函数与LAST函数查询..............................................................58

实例3-26使用Parameters参数动态查询记录（DAO）指定单个参数..................59

实例3-27使用parameters参数动态查询记录（DAO）：指定多个参数................60

实例3-28使用parameters参数动态查询记录（ADO）：指定单个参数................61

实例3-29使用Parameters参数动态查询记录（ADO）：指定多个参数................63

实例3-30使用别名查询数据库...........................................................................................64

实例3-31将查询结果作为窗体控件的源数据..................................................................65

实例3-32通过窗体控件查询浏览数据库记录..................................................................68

实例3-33多表查询（WHERE连接）...............................................................................79

实例3-34多表查询（内连接IERJOIT）................................................................79

实例3-35多表查询（左外连接LEFTOUTERJOIT）.................................................79

实例3-36多表查询（右外连接RIGHTOUTERJOIT）.............................................79

实例3-37多表查询（子查询WHERE，AY，SOME）.............................................79

实例3-38多表查询（子查询EXISTS，OTEXISTS）................................................79

实例3-39从两个数据表中查询出都存在的记录分析:.....................................................79

实例3-40从两个数据表查询出只存在于某数据表的记录.............................................79

实例3-41将查询结果生成一个数据表...............................................................................80

实例3-42将查询结果保存为一个XML文件...................................................................82

实例3-43利用工作表实现记录的分页显示.......................................................................83

实例3-44利用窗体实现记录的分页显示...........................................................................85

感谢下载载

精品

四、添加更新及删除记录.................................................................................................................88

实例4-1添加新记录（ADO+addnew）..........................................................................88

实例4-2添加新记录（ADO+SQL）..................................................................................89

实例4-3添加新记录（DAO+addnew）..........................................................................90

实例4-4添加新记录（DAO+SQL）..................................................................................90

实例4-5添加新记录（Access+SQL）..............................................................................91

实例4-6修改更新特定记录（ADO+SQL）.....................................................................91

实例4-7修改更新特定记录（DAO+SQL）.....................................................................92

实例4-8修改更新特定记录（Access+SQL）.................................................93

实例4-9修改更新全部记录（ADO+SQL）.....................................................................93

实例4-10修改更新全部记录（DAO+SQL）..................................................94

实例4-11修改更新全部记录（Access+SQL）..............................................94

实例4-12删除特定记录（ADO+SQL）...........................................................................94

实例4-13删除特定记录（DAO+SQL）...........................................................................94

实例4-14删除特定记录（Acess+SQL）.........................................................................95

实例4-15删除全部记录（ADO+SQL）...........................................................................95

实例4-16删除全部记录（DAO+SQL）...........................................................................96

实例4-17删除全部记录（Access+SQL）.......................................................................96

实例4-18通过窗体编辑记录................................................................................................96

五、将数据保存为数据库数据.........................................................................................................98

实例5-1将整个工作表数据都保存为新的Access数据库...........................................98

实例5-2将工作表的某些区域数据保存为新Access数据库（Access）...............100

实例5-3将工作簿的所有工作表数据分别保存为不同的数据表（Access）............101

感谢下载载

精品

实例5-4将多个工作簿的某个工作表数据汇总为新Access数据库（Access）...102

实例5-5将多个工作簿的某个工作表数据保存为不同的数据表（Access）............103

实例5-6将工作表数据保存到已有的Access数据库（循环方式）（ADO）..........105

实例5-7将工作表数据保存到已有的Access数据库（循环方式）（DAO）..........107

实例5-8将工作表数据保存到已有的Access数据库（数组方式）（ADO）..........109

实例5-9将工作表数据保存到已有的Access数据库（数组方式）（DAO）..........111

实例5-10将工作簿的所有工作表数据分别保存为不同的数据表（ADO）.............112

实例5-11将工作簿的所有工作表数据分别保存为不同的数据表（DAO）.............114

六、数据库中的数据表操作...........................................................................................................115

实例6-1打开数据库和数据表（Getobject函数）.......................................................115

实例6-2打开数据库数据表（createobject函数）..................................116

实例6-3删除数据表（ADO）...........................................................................................117

实例6-4删除数据表（ADOX）........................................................................................118

实例6-5删除数据表（DAO+DELETE）.........................................................................118

实例6-6删除数据表（DAO+SQL）................................................................................118

实例6-7删除数据表（Access）.......................................................................................118

实例6-8为数据表增加字段（ADO）..............................................................................119

实例6-9为数据表增加字段（ADOX）·实例6-10为数据表增加字段（DAO）

....................................................................................................................................................120

实例6-11为数据表增加字段（Access）........................................................................120

实例6-12删除字段（ADO）............................................................................................121

实例6-13删除字段（ADOX）..........................................................................................122

实例6-14删除字段（DAO）............................................................................................122

感谢下载载

精品

实例6-15删除字段（Access）.........................................................................................124

实例6-16改变字段的类型（ADO）................................................................................125

实例6-17改变字段的类型（DAO）................................................................................126

实例6-18改变字段的类型（Access）............................................................................126

实例6-19改变字段的长度（ADO）................................................................................127

实例6-20改变字段的长度（DAO）................................................................................127

实例6-21改变字段的长度（Access）............................................................................128

实例6-22重命名数据表（Access）................................................................................128

实例6-23复制数据表（Access）....................................................................................129

实例6-24复制数据表（ADO）........................................................................................130

实例6-25复制数据表（DAO）........................................................................................130

实例6-26通过窗体维护数据库.........................................................................................131

七、sqlserver数据库有关...........................................................................................................141

八、FoxPro数据库有关................................................................................................................157

九、....................................................................................................................................................162

实例9-1从工作簿的某个工作表中查询获取数据（ADO）.........................................162

实例9-2从工作簿的全部工作表中查询获取数据（ADO）.........................................163

实例9-3利用DAO从工作表中查询数据......................................................................164

实例9-4查询其他工作簿的数据（ADO）......................................................................165

实例9-5获取其他工作簿中的工作表名称清单（ADOX）..........................................166

实例9-6利用ADO对工作表数据进行多重排序.........................................................167

实例9-7利用ADO按照字符的长度对数据进行排序.................................................168

实例9-8比较两张表，将两个表中相同的行数据抓取出来..........................................169

感谢下载载

精品

实例9-9比较两张表，将只存在于某个表中的行数据抓取出来.................................170

实例9-10删除工作表数据区域内的所有空行................................................................171

十、关于文本文件...................................................................................................................173

一、创建数据库

1-1利用DAO创建数据库和数据表

首先建立对DAO对象库MicrosoftDAO3.6ObjectLibrary的引用.在VBA界面下:

工具-à引用,选中”MicrosoftDAO3.6ObjectLibrary”

PublicSub1_1()

se‘定义DAO的Database(数据库)对象变量

ef‘定义DAO的TableDef(数据表)对象变量

DimmyDataAsString‘定义数据库名称变量

DimmyTableAsString‘定义数据表名称变量

‘设置要创建的数据库名称（包括完整路径）

myData=&“学生成绩管理.mdb”

‘设置要创建的数据表名称

myTable=”期末成绩”

‘删除已经存在的数据库文件

onerrorresumenext

killmyData

onerrorgoto0

‘创建数据库

SetmyDb=CreateDatabase(myData,dbLangChineseSimplified)

‘创建数据表

SetmyTbl=TableDef(myTable)

‘为创建的数据表添加各个字段

WithmyTbl

.Field(“学号”,dbText,10)

.Field(“姓名”,dbText,6)

.Field(“性别”,dbText,1)

.Field(“班级”,dbText,10)

.Field(“数学”,dbSingle)

.Field(“语文”,dbSingle)

感谢下载载

精品

.Field(“物理”,dbSingle)

.Field(“化学”,dbSingle)

.Field(“英语”,dbSingle)

.Field(“总分”,dbSingle)

EndWith

‘将创建的数据表添加到数据库的TableDefs集合中

myTbl

‘关闭数据库，并释放变量

SetmyDb=othing

SetmyTbl=othing

Endsub

1-2利用ADOX创建数据库和数据表

引用：LandSecurity

代码：

publicsub1_2()

g‘定义ADOX的Catalog对象变量

dimmytblasnewtable‘定义table对象变量

dimmydataasstring‘定义数据库名称变量

dimmytableasstring‘定义数据表名称变量

‘设置要创建的数据库名称（包括完整路径）

mydata=&“学生成绩管理.mdb”

‘设置要创建的数据表名称

mytable=”期末成绩”

‘删除已经存在的数据库文件

onerrorresumenext

killmydata

onerrorgoto0

‘创建新的数据库

”provider=.4.0;datasource=”&mydata

‘创建数据表，并添加字段

withmytbl

.name=mytable

.“学号”,advarwchar,10

.“姓名”,advarwchar,6

.“性别”,advarwchar,1

.“班级”,advarwchar,10

.“数学”,adSingle

.“语文”,adSingle

.“物理”,adSingle

.“化学”,adSingle

.“英语”,adSingle

.“总分”,adSingle

Endwith

感谢下载载

精品

‘将创建的数据表添加到ADOX的Tables集合中

mytbl

‘释放变量

setmycat=nothing

setmytbl=nothing

endsub

注：在VB中，常用的数据访问接口有下列三种：数据库访问对象（DAO，DataAccessobject）、

远程数据库对象（RDO,RemoteDataObject）和ActiveX数据对象（ADO,ActiveXDataObject）

ADOX常用方法

•Append方法：可以创建columns,groups,indexes,keys,procedures,tables,users,views等

为数据表添加字段：

字段名，数据类型，字段长度

将创建的数据表添加到ADOX的Tables集合中的语句是：

mytbl

•Create方法：创建一个新的数据库的语句：

“provider=.4.0;datasource=”&mydata

•Delete方法：删除数据表：

数据表名

•Refresh方法：用于更新集合中的对象

1-3利用SQL语句创建数据库和数据表

首先引用：ry和landsecurity”

代码：

publicsub1_3()

g‘定义ADOX的Catalog对象变量

d‘定义Command对象变量

dimmydataasstring‘定义数据库名称变量

dimmytableasstring‘定义数据表名称变量

dimSQLasstring

‘设置要创建的数据库名称（包括完整路径）

mydata=&“学生成绩管理.mdb”

‘设置要创建的数据表名称

mytable=”期末成绩”

‘删除已经存在的数据库文件

onerrorresumenext

killmydata

onerrorgoto0

‘创建数据库文件

“provider=.4.0;Datasource=”&mydata

‘设置数据库连接

connection=connection

‘设置创建数据表的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

注：有两种方法来创建数据表：

•利用d对象的commandtext属性和execute方法：

d

connection=connection

withmycmd

.commandtext=SQL

.execute,,adcmdtext

endwith

•利用tion对象的execute方法来生成几个记录集

tion

set

setcnn=connection

setrs=e(sql)

1-4在已有的数据库中创建数据表（DAO）

引用DAO对象库：microsoftDAO3.6objectlibrary

代码：

publicsub1_4()

se‘定义DAO的database(数据库)对象变量

dimmydataasstring‘定义数据库名称变量

dimmytableasstring‘定义数据表名称变量

‘设置数据库名称（包括完整路径）

mydata=&“学生成绩管理.mdb”

‘设置要创建的数据表名称

mytable=”期末成绩”

‘打开数据库

setmydb=opendatabase(mydata)

‘删除数据库中已经存在的数据表

mytable

‘创建新的数据表

setmytbl=tabledef(mytable)

感谢下载载

精品

‘以下与1-1相同

endsub

•补充：

opendatabase方法用来打开一个已有的数据库，返回一个数据库对象，并自动将该数据库对象加入到数

据库对象集中。

setdatabase=tabase(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）

引用：landsecurity

代码：

publicsub1_5()

g‘定义ADOX的catalog对象变量

dimmytblasnewtable‘定义table对象变量

dimmydataasstring‘定义数据库名称变量

dimmytableasstring‘定义数据表名称变量

‘设置数据库名称（包括完整路径）

mydata=&“学生成绩管理.mdb”

‘设置要创建的数据表名称

mytable=”期末成绩”

‘建立与数据库的连接

connection=”provider=.4.0;”_

&“datasource=”&mydata

‘删除数据库中已经存在的数据表

mytable

‘创建数据表，并添加字段

‘以下与1-2同

endsub

注：Activeconnection属性用来指示catalog所属的ADO

Connection对象，表示到数据源的打开的连接。

感谢下载载

精品1-6在已有的数据库中创建数据表（SQL，Command对

象）

引用:ry和landsecurity

代码：

publicsub1_6()

g‘定义adox的catalog对象变量

d‘定义command对象变量

dimmydataasstring‘定义数据库名称变量

dimmytableasstring‘定义数据表名称变量

dimsqlasstring

‘设置数据库名称（包括完整路径）

mydata=&“学生成绩管理.mdb”

‘设置要创建的数据表名称

mytable=”期末成绩”

‘建立与数据库的连接

connection=”provider=.4.0;”_

&“datasource=”&mydata

‘删除数据库中已经存在的数据表

mytable

‘设置数据库连接

connection=connection

‘设置创建数据表的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对

象）

引用：ry

代码：

publicsub1_7()

tion‘定义connection对象变量

set‘定义Recordset对象变量

dimmydataasstring‘定义数据库名称变量

dimmytableasstring‘定义数据表名称变量

dimsqlasstring

‘设置数据库名称(包括完整路径)

mydata=&“学生成绩管理.mdb”

‘设置要创建的数据表名称

mytable=”期末成绩”

‘建立与数据库的连接

withcnn

.provider=”.4.0”

.openmydata

endwith

‘删除数据库中已经存在的数据表

SQL=”droptable”&mytable

setrs=e(sql)

‘设置创建数据表的SQL语句

sql=”createtable”&mytable_

&“(学号text(10),姓名text(6),性别text(1),班级text(10),”_

&“数学single,语文single,物理single,化学single,”_

&“英语single,总分single)”

‘利用execute方法创建数据表

setrs=e(sql)

‘关闭数据库

‘释放变量

setmycat=nothing

setrs=nothing

setcnn=nothing

‘弹出信息

msgbox“数据表<”&mytable&“>创建成功!”,vbinformation,”创建数据表”

endsub

感谢下载载

精品1-10利用工作表数据创建数据表（ADOX）

数据表结构

A

1

2

3

4

5

6

7

8

9

10

11

12

13

14

代码：

PublicSub1_10()

B

数据表名称

字段名称

学号

姓名

性别

班级

数学

语文

物理

化学

英语

总分

考试日期

C

期中成绩

字段类型

adVarWChar

adVarWChar

adVarWChar

adVarWChar

adSingle

adSingle

adSingle

adSingle

adSingle

adSingle

adDate

D

字段大小

10

6

1

10

是否索引

是

g‘定义catalog变量

‘定义table变量

‘定义column变量

‘定义index变量

DimwsAsWorksheet‘定义worksheet变量

DimiAsLong

DimmyDataAsString‘

myData=&"学生成绩管理.mdb"‘指定数据文件

感谢下载载

精品

‘判断是否有保存数据表资料的工作表存在

OnErrorResumeext

Setws=Worksheets("数据表设计")

OnErrorGoTo0

IfwsIsothingThen

MsgBox"没有数据表资料存在！",vbCritical,"警告"

ExitSub

EndIf

te

‘建立与数据库的连接

Connection="provider=.4.0;"_

&"datasource="&myData

‘删除已经存在的数据表

OnErrorResumeext

("B1").Value

OnErrorGoTo0

‘建立索引

="PrimaryKey"

yKey=True

‘开始根据工作表的数据创建数据表

WithmyTable

.ame=("B1").Value

Fori=("A65536").End(xlUp).Row

SetmyColumn=ewColumn

WithmyColumn

.ame=(i,1).Value

.Type=GetConsto((i,2).Value)

(i,3).Value>0Then

.DefinedSize=(i,3).Value

.Attributes=adColullable

EndIf

EndWith

.myColumn

(i,4).Value="是"Then

(i,1).Value

EndIf

ext

EndWith

‘将表定义进行保存

myTable

myIdx

‘弹出信息

MsgBox"数据表<"&("B1").Value&">创建成功！",_

vbOKOnly+vbInformation,"创建数据表"

‘关闭连接，并释放变量

Setws=othing

SetmyIdx=othing

感谢下载载

精品

SetmyTable=othing

SetmyCat=othing

EndSub

‘将工作表中定义的数据类型（字符串型）转换为字段类型VBA常量，即编制一个自定义函数

GetConsto

FunctionGetConsto(myStrAsString)AsInteger

SelectCasemyStr

Case"adBigInt":GetConsto=20

Case"adBinary":GetConsto=128

Case"adBoolean":GetConsto=11

Case"adBSTR":GetConsto=8

Case"adChapter":GetConsto=136

Case"adChar":GetConsto=129

Case"adCurrency":GetConsto=6

Case"adDate":GetConsto=7

Case"adDBDate":GetConsto=133

Case"adDBTime":GetConsto=134

Case"adDBTimeStamp":GetConsto=135

Case"adDecimal":GetConsto=14

Case"adDouble":GetConsto=5

Case"adEmpty":GetConsto=0

Case"adError":GetConsto=10

Case"adFileTime":GetConsto=64

Case"adGUID":GetConsto=72

Case"adIDispatch":GetConsto=9

Case"adInteger":GetConsto=3

Case"adIUnknown":GetConsto=13

Case"adLongVarBinary":GetConsto=205

Case"adLongVarChar":GetConsto=201

Case"adLongVarWChar":GetConsto=203

Case"adumeric":GetConsto=131

Case"adPropVariant":GetConsto=138

Case"adSingle":GetConsto=4

Case"adSmallInt":GetConsto=2

Case"adTinyInt":GetConsto=16

Case"adUnsignedBigInt":GetConsto=21

Case"adUnsignedInt":GetConsto=19

Case"adUnsignedSmallInt":GetConsto=18

Case"adUnsignedTinyInt":GetConsto=17

Case"adUserDefined":GetConsto=132

Case"adVarBinary":GetConsto=204

Case"adVarChar":GetConsto=200

Case"adVariant":GetConsto=12

Case"adVarumeric":GetConsto=139

Case"adVarWChar":GetConsto=202

感谢下载载

精品

Case"adWChar":GetConsto=130

CaseElse:GetConsto=-1

EndSelect

EndFunction

1-11利用工作表数据创建数（ADO+SQL）

表结构同1-10

代码：

PublicSub1_11()

tion‘定义connection对象变量

set‘定义recordset对象变量

DimwsAsWorksheet‘定义worksheet对象变量

DimiAsLong

DimmyDataAsString,SQLAsString

myData=&"学生成绩管理.mdb"‘指定数据库文件

‘判断是否有保存数据表资料的工作表存在

OnErrorResumeext

Setws=Worksheets("数据表设计")

OnErrorGoTo0

IfwsIsothingThen

MsgBox"没有数据表资料存在！",vbCritical,"警告"

ExitSub

EndIf

te

‘建立与数据库的连接

Setcnn=tion

Withcnn

.Provider=".4.0"

.OpenmyData

EndWith

‘删除已经存在的数据表

OnErrorResumeext

SQL="droptable"&("B1").Value

Setrs=e(SQL)

OnErrorGoTo0

‘生成创建数据表的SQL语句字符串

SQL="createtable"&("B1").Value&"("

Fori=("A65536").End(xlUp).Row

SQL=SQL&(i,1).Value&""&(i,2).Value

(i,3).Value>0Then

SQL=SQL&"("&(i,3).Value&")"

EndIf

(i,4).Value="是"Then

感谢下载载

精品

SQL=SQL&"primarykey"

EndIf

SQL=SQL&","

ext

SQL=Left(SQL,Len(SQL)-1)&")"

‘创建数据表

Setrs=e(SQL)

MsgBox"数据表<"&("B1").Value&">创建成功！",_

vbOKOnly+vbInformation,"创建数据表"

‘关闭连接，并释放变量

Setws=othing

Setrs=othing

Setcnn=othing

EndSub

1-12利用工作表数据创建数据表（DAO）

引用：microsoftDAO3.6objectlibrary

代码：

PublicSub1_12()

se‘定义database变量

ef‘定义tabledef变量

‘定义index变量

DimwsAsWorksheet‘定义worksheet变量

DimiAsLong

DimmyDataAsString

myData=&"学生成绩管理.mdb"‘指定数据库文件

‘判断是否有保存数据表资料的工作表存在

OnErrorResumeext

Setws=Worksheets("数据表设计")

OnErrorGoTo0

IfwsIsothingThen

MsgBox"没有数据表资料存在！",vbCritical,"警告"

ExitSub

EndIf

te

‘建立与数据库的连接

SetmyDb=OpenDatabase(myData)

‘删除已经存在的数据表

OnErrorResumeext

Range("B1").Value

感谢下载载

精品

OnErrorGoTo0

‘创建数据表

SetmyTable=TableDef(Range("B1").Value)

‘创建索引

SetmyIndex=Index("PrimaryKey")

y=True

‘开始创建数据表字段等

Fori=4ToRange("A65536").End(xlUp).Row

WithmyTable

‘添加字段

.Field(Cells(i,1).Value,_

GetConsto(Cells(i,2).Value),Cells(i,3).Value)

‘对文本型字段设置是否允许零长度字符串

IfCells(i,2).Value="dbText"Then

IfCells(i,4).Value=TrueThen

.Fields(Cells(i,1).Value).AllowZeroLength=True

EndIf

EndIf

‘设置是否为必填字段

IfCells(i,5).Value=TrueThen

.Fields(Cells(i,1).Value).Required=True

Else

.Fields(Cells(i,1).Value).Required=False

EndIf

‘设置索引

IfCells(i,6).Value="是"Then

Field(Cells(i,1).Value)

EndIf

EndWith

extI

‘将数据表定义保存到数据库

myIndex

myTable

‘弹出信息

MsgBox"数据表<"&("B1").Value&">创建成功！",_

vbOKOnly+vbInformation,"创建数据表"

‘关闭数据库

‘设置变量

Setws=othing

SetmyIndex=othing

SetmyTable=othing

SetmyDb=othing

EndSub

感谢下载载

精品二、获取数据库中的信息

2-1检查数据表是否存在（ADO）

PublicSub检查数据表存在ado()

DimmydataAsString‘定义数据库名称变量

DimmytableAsString‘定义数据表名称变量

tion‘定义ADO的connection对象变量

set‘定义ADO的Recordset对象变量

‘指定数据库名称

mydata=&"客户管理.mdb"

‘指定要查询的数据表名称

mytable="发票管理"

‘建立与数据库的连接

Setcnn=tion

Withcnn

.Provider=".4.0"‘注○1

.Openmydata‘注○2

EndWith

‘创建数据表记录集

Setrs=hema(adSchemaTables)

‘开始查询是否存在该数据表

IfLCase(rs!table_name)=LCase(mytable)Then

MsgBox"数据表<"&mytable&">存在！"

GoTohhh

EndIf

‘继续下一条记录

xt

Loop

MsgBox"数据表<"&mytable&">不存在！"

hhh:

Setrs=othing

Setcnn=othing

EndSub

•解释说明：

•Connection对象：用于建立与数据源的连接，通过连接可以从应用程序中访问数据源。（只有连接后才

能使用其它对象）

常用属性：

感谢下载载

精品

1．Connectionstring属性：指示用于建立到数据源的连接的信息

2．povider属性：用来指示connection对象的提供者的名称。‘注○1

3．open方法：用于打开到数据源的连接‘注○2

语法：nnectionstring,userID,Password,Options

连接信息的字，建立，密码，符串连接时要使用的用户名称字符串

4．Close方法：用于关闭打开的数据库连接

5．State属性：用于检查Connection对象是打开的还是关闭的，或是否处于连接状态。

返回值：adStateOpen:对象已打开，adStateClosed:对象已关闭，adStateConnecting:正在连接

6．OpenSchema方法：允许用户打开包含模式信息的数据集Recordset.

Recordset将以只读、静态游标的模式打开

setrecordset=hema(Querytype,criteria,schemaID)

参数Querytype指定任意的SchemaEnum值，以表示要运行的模式查询的类型

常见类型：

要检查数据表时，值为adschematables

要检查数据表中的字段时，值为adschemacolumns

要检查数据表中的索引时，值为adschemaindexes

要检查数据表中的主键时，值为adschemaPrimarykeys

参数Criteria为可选参数，用于限制模式查询的结果（略）

参数SchemaID为OLEDB规范未定义的提供者模式查询的GUID（略）

•Command对象：发出命令（例如SQL语句）操作数据源。可在数据源中添加、删除

和更新数据，或在表中查询数据。

•Recordset对象：只代表记录集，是基于某个连接的表或Command对象的执行结果，在ADO中，

所有对数据源的操作几乎都是由其完成。其功能为可以浏览记录，修改，添加，删除特定记录。

1．Open方法：打开一个recordset对象。语法：

urce,activeconnection,cursortype,locktype,options

recordset代表recordset对象

source(可选):可以是command对象、SQL语句、数据库的表名等。

Activeconnection（可选）:variant或string型，variant用于connection对象变量的名

称；string包含connectionstring参数。

cursortype（可选）：用于指定当打开recordset时提供者应使用的游标类型，默认为

adopenforwardonly.

locktype(可选)：用于确定打开recordset时提供者应使用的锁定类型。如要对数据库

进行修改，删除，更新等操作，则locktype参数必须设定为adlockoptimistic,默认为

adlockreadonly(只读)。

2．close方法：关闭recordset对象

方法：用于保存对recordset对象的当前行或record对象的fields集合所做的

更改。

fields,values

record为recordset中的行（即某个记录）.fields(可选):表示要修改的字段名称或序号

位置。values(可选)表示新记录中字段的值。

属性和EOF属性。BOF属性表示当前记录的位置在recordset对象的第一个记录之前，EOF

属性表示当前记录的位置在recordset对象的最后一个记录之后。BOF，EOF属性返回boolean值。

当记录指针指向某个记录时，BOF属性和EOF属性值均为False，表示没有到达记录集的开始处和结束

处。当记录指针达到记录集的开始处时，BOF为True,而EOF为false；当记录指针达到记录集的结束

处时，EOF为True,而BOF为false.如记录集中没有记录，那么BOF和EOF均为True.

感谢下载载

精品

rstMovelastMovenextMoveprevious方法移动到指定的recordset对象中的

第一个、最后一个、下一个或上一个记录并使其成为当前记录。

recordset.{movefirst|movelast|movenext|moveprevious}

方法：用于创建可更新的recordset对象的新记录。

fieldlist,values

fieldlist(可选)：为新记录中字段的单个名称，名称数组或序号位置数组；

values（可选）：为新记录中字段的单人值或值的数组

7．delete方法：用于删除当前记录或记录组

affectrecords

affectrecords为affectenum值（当前记录）用于确定delete方法影响的记录数目。

pe属性：指示recordset对象中使用的游标的类型，可设置或返回

cursortypeenum值，默认值为adlockreadonly(只读记录),如要对记录进行修改，删除，添加等操作，

则需要将其设为adlockoptionistic或adlockpessimistic

集合：表示包含recordset或record对象的所有field对象，每个field对象对应于

recordset中的一列，如fields(0)表示recordset中的第一列（第一个字段，fields(1)表示recordset

中的第二列（第二个字段）………类推；

引用字段方法：使用记录集的fields(n)属性，其中n是一个记录中字段从左到右排

列序号，第一个字段的序号为0，第二个字段的序号为1，…….类推；

•使用记录集的fields(n)属性，使用字段名，即fields(“字段名”),如fields(“编号”)……

•直接在记录集对象中引用字段名称，如rs!姓名

•直接在记录集对象中引用字段名称，使用（），如rs(“编号”)……….

count属性：返回long值，表示recordset中的记录数目。

•ADO访问数据库的基本步聚：

1．先声明connection对象变量和recordset对象变量

2．建立与数据库的连接，通过声明connection对象来完成的。

tion‘声明一个connection对象变量cnn

setcnn=tion‘初始化该对象变量，即创建一个新的connection对象

使用open方法来建立与数据库的连接

withcnn

.provider=”.4.0”

.&“客户管理.mdb”

endwith

3.在建立与数据库的连接后，声明并初始化一个新的recordset对象，打开该对象，

就可以使用recordset对象的有关属性和方法来查询、浏览、编辑、删除记录。

set

setrs=set

L语句或数据表名，cnn，adopenkeyset，adlockoptimistic

4.当记录集使用完毕，应该将其关闭，以便从内存中删除recordset对象和

connection对象。

setrs=nothing

setcnn=nothing

•拓展：

•使用recordset对象的find方法来查某个数据表是否存在：用于在recordset记录集中搜索满足指

感谢下载载

精品

定条件的行。语法：

find(criteria,skiprows,searchdirection,start)

criteria指定用于搜索的列名、比较操作符和值。

skiprows(可选)默认值为0，指定当前行或start书签的行偏移量以开始搜索，默认从当前行开始搜索；

searchdirection(可选):指定搜索应从当前开始，还是从搜索方向的下一个有效行开始，如值为

adsearchforward，不成功的搜索将在recordset的结尾处停止。

如值为adsearchbackward，不成功的搜索将在recordset的开始处停止。

start(可选)用于标记搜索的开始位置。

PublicSub2_1_1()

DimmydataAsString

DimmytableAsString

tion

set

mydata=&"客户管理.mdb"

mytable="发票管理"

Setcnn=tion

Withcnn

.Provider=".4.0"

.Openmydata

EndWith

Setrs=hema(adSchemaTables)

"TABLE_AME='"&mytable&"'"

n

MsgBox"数据表<"&mytable&">不存在！"

Else

MsgBox"数据表<"&mytable&">存在！"

EndIf

Setrs=othing

Setcnn=othing

EndSub

实例2-2检查数据表是否存在（ADOX）

引用：landsecurity

PublicSub检查数据表存在adox()

DimmydataAsString‘定义数据库名称变量

DimmytableAsString‘定义数据表名称变量

g‘定义ADOX的catalog对象变量

‘定义ADOX的table对象变量

mydata=&"客户管理.mdb"‘指定数据库文件

感谢下载载

精品

mytable="发票管理"‘指定要查询的数据表名称

‘建立与数据库的连接

SetmyCat=g

Connection="Provider=.4.0;"_

&"DataSource="&mydata

‘开始查询是否存在该数据表

IfLCase()=LCase(mytable)Then

MsgBox"数据表<"&mytable&">存在！"

GoTohhh

EndIf

ext

MsgBox"数据表"&mytable&"不存在！"

hhh:

SetmyTbl=othing

SetmyCat=othing

EndSub

实例2-3检查数据表是否存在（DAO）

引用：microsoftDAO3.6objectlibrary

PublicSub2_3()

DimmydataAsString‘定义数据库名称变量

DimmytableAsString‘定义数据表名称变量

se‘定义dao的database对象变量

ef‘定义dao的tabledef对象变量

mydata=&"客户管理.mdb"‘指定数据库文件

mytable="发票管理"‘指定要查询的数据表名称

‘建立与数据库的连接

SetmyDb=OpenDatabase(mydata)

‘开始查询是否存在该数据表

efs

IfLCase()=LCase(mytable)Then

MsgBox"数据表<"&mytable&">存在！"

GoTohhh

EndIf

extmytbl

MsgBox"数据表"&mytable&"不存在！"

hhh:

Setmytbl=othing

SetmyDb=othing

感谢下载载

精品

EndSub

实例2-5获取数据库中所有表的名称和类型（ADO）

引用：ry

PublicSub获取表的名称和类型ADO()‘将access库中的表信息读入到excel文件中

DimiAsInteger

DimmydataAsString‘定义数据库名称变量

set‘定义ADODB的recordset变量

mydata=&"客户管理.mdb"‘指定数据库文件

‘建立与数据库的连接

Setcnn=tion

Withcnn

.Provider=".4.0"

.Openmydata

EndWith

‘准备工作表

i=2

Range("A1:B1")=Array("表名称","表类型")

‘开始获取数据库中所有表的名称和类型

Setrs=hema(adSchemaTables)

Cells(i,1)=rs!table_name‘获取表名称

Cells(i,2)=rs!table_type‘获取表类型

‘继续下一条记录

xt

i=i+1

Loop

‘关闭记录集和数据库

‘释放变量

Setrs=othing

Setcnn=othing

EndSub

实例2-6获取数据库中所有表的名称和类型（ADOX）

感谢下载载

精品

引用：landsecurity

PublicSub2_6()

g‘定义adox的catalog对象变量

‘定义adox的table对象变量

mydata=&"客户管理.mdb"‘指定数据库文件

DimiAsInteger

‘准备工作表

i=2

Range("A1:B1")=Array("表名称","表类型")

‘建立与数据库的连接

SetmyCat=g

Connection="Provider=.4.0;"_

&"DataSource="&mydata

‘开始获取表信息

Cells(i,1)=‘获取表名称

Cells(i,2)=‘获取表类型

i=i+1

ext

‘释放变量

Setmtb=othing

SetmyCat=othing

EndSub

实例2-7获取数据库中所有的表的名称（DAO）

引用：microsoftdao3.6objectlibrary

PublicSub2_7()

se‘定义dao的database变量

ef‘定义dao的tabledef变量

mydata=&"客户管理.mdb"‘指定数据库文件

DimiAsInteger

‘准备工作表

i=2

Range("A1:B1")=Array("表名称","表类型")

‘建立与数据库的连接

SetmyDb=OpenDatabase(mydata)

‘开始获取表信息

efs

Cells(i,1)=‘获取表名称

Cells(i,2)=TypeToTable(utes)‘获取数据表类型

感谢下载载

精品

‘由于Attributes属性返回值是一个long值，需要将这个long值转换为具体的类型

名称，使用自定义转换函数typetotable

i=i+1

extmytbl

‘关闭数据库

‘释放变量

Setmytbl=othing

SetmyDb=othing

EndSub

‘自定义转换函数

PublicFunctionTypeToTable(myValueAsLong)AsString

SelectCasemyValue

Case2:TypeToTable="ACCESSTABLE"

Case-2147483648#:TypeToTable="SYSTEMTABLE"

Case0:TypeToTable="TABLE"

EndSelect

EndFunction

实例2-12检查某字段是否存在（ADO）

PublicSub检查某字段是否存在ADO()

DimmydataAsString‘定义数据库字符串变量

DimmytableAsString‘定义数据表字符串变量

DimmycolumnAsString‘定义字段字符串变量

tion‘定义connection对象变量

set‘定义recordset对象变量

mydata=&"客户管理.mdb"‘指定数据库

mytable="客户信息"‘指定数据表

mycolumn="客户名称"‘指定字段名称

‘建立与数据库的连接

Setcnn=tion

Withcnn

.Provider=".4.0"

.Openmydata

EndWith

‘创建字段记录集

Setrs=hema(adSchemaColumns)‘设置为adschemacolumns就是获取字段

信息

‘开始检查该字段是否存在

感谢下载载

精品

IfLCase(rs!column_name)=LCase(mycolumn)Then

MsgBox"在数据表<"&mytable&">中存在字段<"&mycolumn&">！"

GoTohhh

EndIf

xt

Loop

MsgBox"在数据表<"&mytable&">中不存在字段<"&mycolumn&">！"

hhh:

Setrs=othing

Setcnn=othing

EndSub

•扩展：使用connection对象的find方法来查某个字

段是否存在

PublicSub2_12_1()

DimmydataAsString

DimmytableAsString

DimmycolumnAsString

tion

set

mydata=&"客户管理.mdb"

mytable="客户信息"

mycolumn="客户名称"

Setcnn=tion

Withcnn

.Provider=".4.0"

.Openmydata

EndWith

Setrs=hema(adSchemaColumns)

"column_name='"&mycolumn&"'"

n

MsgBox"在数据表<"&mytable&">中不存在字段<"&mycolumn&">！"

Else

MsgBox"在数据表<"&mytable&">中存在字段<"&mycolumn&">！"

EndIf

Setrs=othing

Setcnn=othing

EndSub

感谢下载载

精品

获取数据库中某数据表的所有字段信息

实例2-20获取数据库的所有查询信息（ADOX）

PublicSub获取数据库的所有查询信息ADOX()

DimmydataAsString‘定义数据库名称变量

DimmytableAsString‘定义数据表名称变量

g‘定义adox的catalog对象变量

DimmyCmdAsObject

‘定义adox的riew变量

DimiAsLong

mydata=&"客户管理.mdb"‘指定数据库文件

‘准备工作表

Columns(3).ColumnWidth=60

Range("A1:C1")=Array("查询名称","Command对象的类型","SQL语句")

‘建立与数据库的连接

SetmyCat=g

Connection="Provider=.4.0;"_

&"DataSource="&mydata

‘获取查询信息，并输出到工作表

i=2

SetmyCmd=d

Range("A"&i)=‘获取查询名称

Range("B"&i)=dType‘获取查询命令的类型

Range("C"&i)=dText‘获取查询命令的字符串

i=i+1

ext

‘释放变量

SetmyView=othing

SetmyCat=othing

EndSub

感谢下载载

精品实例2-21获取数据库的所有查询信息（DAO）

PublicSub获取数据库的所有查询信息DAO()

DimmydataAsString‘定义数据库名称变量

se‘定义database对象变量

ef‘定义querydef对象变量

DimiAsLong

mydata=&"客户管理.mdb"‘指定数据库文件夹

‘准备工作表

Columns(3).ColumnWidth=60

Range("A1:C1")=Array("查询名称","查询类型","SQL语句")

‘建立与数据库的连接

SetmyDb=OpenDatabase(mydata)

‘获取查询信息，并输出到工作表

i=2

efs

Range("A"&i)=‘获取查询名称

Range("B"&i)=GetQryConstStr()‘获取查询类型

Range("C"&i)=‘获取查询的SQL语句

i=i+1

ext

‘关闭数据库连接

‘释放变量

SetmyQry=othing

SetmyDb=othing

EndSub

‘查询类型的VBA常量与查询类型名称的转换自定义函数

FunctionGetQryConstStr(myIntAsInteger)AsString

DimmyStrAsString

SelectCasemyInt

Case0:myStr="dbQSelect"

Case224:myStr="dbQProcedure"

Case240:myStr="dbQAction"

Case16:myStr="dbQCrosstab"

Case32:myStr="dbQDelete"

Case48:myStr="dbQUpdate"

Case64:myStr="dbQAppend"

Case80:myStr="dbQMakeTable"

Case96:myStr="dbQDDL"

Case112:myStr="dbQSQLPassThrough"

感谢下载载

精品

Case128:myStr="dbQSetOperation"

Case144:myStr="dbQSPTBulk"

Case160:myStr="dbQCompound"

CaseElse:myStr=""

EndSelect

GetQryConstStr=myStr

EndFunction

实例2-22获取数据库

（openschema）

引用：ry

PublicSub2_22()

tion

set

DimmyDataAsString

DimwsAsWorksheet

DimiAsLong

myData=&"客户管理.mdb"‘指定数据库文件

'建立与数据库的连接

Withcnn

.Provider=".4.0"

.OpenmyData

EndWith

'打开数据记录集（根据参数的不同，所获取的数据库模式信息也不同）

Setrs=hema(adSchemaColumns)'列信息

'Setrs=hema(adSchemaProcedures)'过程信息

'Setrs=hema(adSchemaViews)'视图信息

'Setrs=hema(adSchemaPrimaryKeys)'主键信息

'Setrs=hema(adSchemaIndexes)'索引信息

'创建新工作表

Setws=

'将获取的信息输入到新建的工作表中

Withrs

'获取列名

Fori=

(1,i).Value=.Fields(i-1).ame

ext

'复制记录集数据

感谢下载载

模式信的

精品

("A2").CopyFromRecordsetrs

EndWith

‘关闭记录集和数据库连接，并释放变量

Setrs=othing

Setcnn=othing

EndSub

实例2-23获取表的创建日期和最后更新日期（ADOX）

引用：landsecurity

PublicSub2_23()

DimmydataAsString‘定义数据库名称变量

g‘定义adox的catalog对象变量

‘定义adox的table对象变量

DimiAsInteger

mydata=&"客户管理.mdb"‘指定数据库文件

‘建立与数据库的连接

SetmyCat=g

Connection="Provider=.4.0;"_

&"DataSource="&mydata

‘清除工作表数据

‘开始查询所有表的创建日期和最后更新日期，并输入到工作表

Range("A1:D1")=Array("表名","类型","创建日期","最后更新日期")

i=2

Range("A"&i)=‘获取表名

Range("B"&i)=‘获取表类型

Range("C"&i)=eated‘获取表创建日期

Range("D"&i)=dified‘获取表最后更新日期

i=i+1

ext

‘释放变量

Setmtb=othing

SetmyCat=othing

EndSub

·扩展应用：获取指定数据表的创建日期和最后更新日期

PublicSub2_23_1()

感谢下载载

精品

DimmydataAsString‘定义数据库名称变量

DimmytableAsString‘定义数据表名称变量

g‘定义adox的catalog对象变量

mydata=&"客户管理.mdb"‘指定数据库文件

mytable="发票管理"‘指定数据表

‘建立与数据库的连接

SetmyCat=g

Connection="Provider=.4.0;"_

&"DataSource="&mydata

‘显示指定数据表的创建日期和最后更新日期

MsgBox"数据表<"&mytable&">的"&vbCrLf_

&"创建日期为:"&(mytable).DateCreated_

&vbCrLf_

&"最后更新日期为:"&(mytable).DateModified

‘释放变量

SetmyCat=othing

EndSub

实例2-24获取表的创建日期和最后更新日期（DAO）

引用：microsoftdao3.6objectlibrary

PublicSub实例2_24()

DimmydataAsString‘定义数据库名称变量

se‘定义dao的database对象变量

ef‘定义dao的tabledef对象变量

mydata=&"客户管理.mdb"‘指定数据库文件

DimiAsInteger

‘清除工作表数据

Range("A1:C1")=Array("表名","创建日期","最后更新日期")

‘建立与数据库的连接

SetmyDb=OpenDatabase(mydata)

i=2

‘开始获取所有表的创建日期和最后更新日期，并输入到工作表

efs

Range("A"&i)=‘获取表名

Range("B"&i)=eated‘获取表的创建日期

Range("C"&i)=dated‘获取表的最后更新日期

i=i+1

extmytbl

‘关闭数据库并释放变量

Setmytbl=othing

SetmyDb=othing

感谢下载载

精品

EndSub

·扩展：获取指定数据表的创建日期和最后更新日期

PublicSub2_24_1()

DimmydataAsString‘定义数据库名称变量

DimmytableAsString‘定义数据表名称变量

se‘定义dao的database对象变量

mydata=&"客户管理.mdb"‘指定数据库文件

mytable="发票管理"‘指定数据表

‘建立与数据库的连接

SetmyDb=OpenDatabase(mydata)

‘显示指定数据表的创建日期和最后更新日期

MsgBox"数据表<"&mytable&">的"&vbCrLf_

&"创建日期为:"&efs(mytable).DateCreated_

&vbCrLf_

&"最后更新日期为:"&efs(mytable).LastUpdated

‘关闭数据库并释放变量

三、SQL各种查询操作

实例3-1将数据库数据导入到excel工作表（ADO之一）

PublicSub3_1()

DimmyDataAsString,myTableAsString,SQLAsString

tion

set

DimiAsInteger

‘清除工作表的全部数据

myData=&"职工管理.mdb"‘指定数据库

myTable="职工基本信息"‘指定数据表

‘建立与数据库的连接

Setcnn=tion

Withcnn

.Provider=".4.0"

.OpenmyData

EndWith

‘查询数据表

SQL="select*from"&myTable&"orderby职工编号"

Setrs=set

L,cnn,adOpenKeyset,adLockOptimistic

感谢下载载

精品

‘显示查询结果

MsgBox"数据库中的记录数为："&Count

‘复制记录数据

Count>0Then

‘复制字段名

Fori=

Cells(1,i)=(i-1).ame

extI

‘设置字段名字体为加粗并居中对齐

WithRange(Cells(1,1),Cells(1,))

.=True

.HorizontalAlignment=xlCenter

EndWith

‘复制全部数据

Range("A2").CopyFromRecordsetrs

‘设置工作表格式

=10

t

EndIf

‘关闭记录集及数据库连接，并释放变量

Setrs=othing

Setcnn=othing

EndSub

·recordset对象的copyfromrecordset方法:

用于将一个ADO或DAO的recordset对象的内容复制到工作表中，复制的起始位置在指定区域的左

上角。

语法：omrecordset(data,maxrows,maxcolumns)

expression表示一个工作表的range对象；

data为必需参数，指定复制到指定区域的recordset对象；

maxrows(可选):指定复制到工作表的记录个数上限，省略为复制所有记录；

maxcolumns(可选)：指定复制到工作表的字段个数上限，省略将复制对象的所有字段

copyfromrecordset方法只是复制记录集的记录数据，并不复制字段名，下列语句用fields

集合的count属性获取字段总数，在循环中利用field对象的name属性获取字段名。

forI=

cells(1,I)=(i-1).name

nextI

复制的记录是从当前行开始的内容，复制完成之后，recordset对象的EOF属性值为True,因此在复制

recordset对象内容后，如要重新浏览记录集的话，需使用rst将记录指针移到第一条记录。

·SQL语句：

sqlect字段列表from子句（表名）where条件groupby性别(分组)havingavg(工资总

额)>5000(与groupby一起用)orderby姓名asc(升序)/desc(降序)

感谢下载载

精品实例3-2数据库数据导入到excel工作表（ADO，之三）

PublicSub3_3()

DimmyDataAsString,myTableAsString

tion

set

DimiAsInteger

‘清除工作表的全部数据

myData=&"职工管理.mdb"‘指定数据库

myTable="职工基本信息"‘指定数据表

‘建立与数据库的连接

Setcnn=tion

Withcnn

.Provider=".4.0"

.OpenmyData

EndWith

‘查询数据表

Setrs=set

Table,cnn,adOpenKeyset,adLockOptimistic,adCmdTableDirect

‘显示查询结果

MsgBox"数据库中的记录数为："&Count

复制记录数据

Count>0Then

‘复制字段名

Fori=

Cells(1,i)=(i-1).ame

exti

WithRange(Cells(1,1),Cells(1,))

.=True

.HorizontalAlignment=xlCenter

EndWith

‘复制全部数据

Range("A2").CopyFromRecordsetrs

‘设置工作表格式

=10

t

EndIf

‘关闭记录集及数据库连接，并释放变量

Setrs=othing

Setcnn=othing

EndSub

·注：3-1与3-3的区别：

感谢下载载

精品

实例3-1中语句l,cnn,adopenkeyset,adlockoptimistic和实例3-3中语句

mytable,cnn,adopenkeyset,adlockoptimistic,adcmdtabledirect的区别：前者使用了sql语句来实

现对数据表的查询，因此可以实现各种条件下的查询；而后者则使用数据表名来实现对数据

表的查询，得到的数据是整个数据表，且无法对记录排序，除此之外两者的代码完全相同。

实例3-4将数据库数据导入到Excel工作表（DAO,之一）

引用：microsoftdao3.6objectlibrary

PublicSub3_4()

DimmyDataAsString,myTableAsString,SQLAsString

se

set

DimiAsInteger

‘清除工作表的全部数据

myData=&"职工管理.mdb"‘指定数据库

myTable="职工基本信息"‘指定数据表

‘建立与数据库的连接

SetmyDb=OpenDatabase(myData)

‘查询数据表

SQL="select*from"&myTable&"orderby职工编号"

SetmyRs=cordset(SQL)

‘显示查询结果

MsgBox"数据库中的记录数为："&Count

‘复制记录数据

Count>0Then

‘复制字段名

Fori=

Cells(1,i)=(i-1).ame

extI

‘设置字段名字体为加粗并居中对齐

WithRange(Cells(1,1),Cells(1,))

.=True

.HorizontalAlignment=xlCenter

EndWith

‘复制全部数据

Range("A2").CopyFromRecordsetmyRs

‘设置工作表格式

=10

t

EndIf

‘关闭记录集及数据库连接，并释放变量

感谢下载载

精品

SetmyRs=othing

SetmyDb=othing

EndSub

·DAO创建查询记录集要使用se对象的

openrecordset方法。

语法：setrecordset=cordset(source,type,options,lockedits)

databaseo为新建立的se对象变量；

source是记录集的数据源，可以是该数据库对象对应数据库的表名，也可以是SQL

查询语句；

type:指定新建的recordset对象的类型；

options:指定新建的recordset对象的一些特性；

lockedits:控制对记录的锁定

例：setmyrs=cordset(SQL)

实例3-6将数据库数据导入工作表（QueryTable集合）

PublicSub3_6()

Dimcnn1AsString,cnn2AsString,cnn3AsString

DimSQLAsString

DimmyDataAsString,myTableAsString

‘清除工作表的全部数据

myData=&"职工管理.mdb"‘指定数据库

myTable="职工基本信息"‘指定数据表

‘设置ODBC连接字符串

cnn1="ODBC;"

cnn2="DBQ="&myData&";"

‘指定驱动程序

cnn3="Driver={MicrosoftAccessDriver(*.mdb)};"

‘设置SQL语句

SQL="select*from"&myTable&"orderby职工编号"

‘将数据库的全部记录数据导入到工作表

_

(Connection:=cnn1&cnn2&cnn3,Destination:=Range("A1"))

.CommandText=SQL

.RefreshBackgroundQuery:=False

EndWith

‘设置工作表格式

=10

感谢下载载

精品

nge

FormatLocal="yyyy-m-dh:mm"Then

FormatLocal="yyyy-m-d"

EndIf

ext

t

EndSub

•利用querytables集合对象的add方法新建一个查询表。

querytables集合对象是querytable对象的集合，每个querytable对象代表一张由外部数据

源返回的数据建立的工作表。add方法新建一个查询表：语法

(connection,destination,sql)

expression:指定一个querytables对象；

connection指定查询表的数据源，可以是一个包含OLEDB或ODB连接字符串的字符串，

一个querytable对象，一个ado或daorecordset对象，一个web查询，一个数据查程序，

或者一个文本文件；

destination指定查询表目标区域左上角单元格（放置生成的查询表的区域），目标区域必

须在包含expression指定的querytables对象的工作表上。sql为可选参数，指定在ODBC数

据源上运行的SQL查询字符串。

实例3-7数据库某些字段数据导入到Excel工作表

（ADO）

引用：ry

PublicSub3_7()

DimmydataAsString,mytableAsString,SQLAsString

tion

set

DimiAsInteger

‘清除工作表的全部数据

mydata=&"职工管理.mdb"‘指定数据库

mytable="职工基本信息"‘指定数据表

‘建立与数据库的连接

Setcnn=tion

Withcnn

.Provider=".4.0"

.Openmydata

EndWith

‘设置SQL语句

SQL="select姓名,性别,所属部门,职务,职称,年龄,进本单位时间from"_

&mytable&"orderby职工编号"

‘查询数据表

Setrs=set

感谢下载载

精品

L,cnn,adOpenKeyset,adLockOptimistic

'也可以使用下面的语句

'Setrs=e(SQL)

‘复制字段名

Fori=

Cells(1,i)=(i-1).ame

extI

‘设置字段名字体为加粗并居中对齐

WithRange(Cells(1,1),Cells(1,))

.=True

.HorizontalAlignment=xlCenter

EndWith

‘复制全部数据

Range("A2").CopyFromRecordsetrs

‘设置工作表格式

=10

t

‘关闭记录集及数据库连接，并释放变量

Setrs=othing

Setcnn=othing

EndSub

实例3-9查询前面的若干条记录

引用：ry

PublicSub3_9()

DimmydataAsString,mytableAsString,SQLAsString

tion

set

DimiAsInteger

‘清除工作表的全部数据

mydata=&"职工管理.mdb"'指定数据库

mytable="职工基本信息"'指定数据表

‘建立与数据库的连接

Setcnn=tion

Withcnn

.Provider=".4.0"

.Openmydata

感谢下载载

精品

EndWith

‘查询数据表

SQL="selecttop5*from"&mytable_

&"where年龄>40orderby年龄DESC"

Setrs=set

L,cnn,adOpenKeyset,adLockOptimistic

'也可以使用下面的语句

'Setrs=e(SQL)

‘复制字段名

Fori=

Cells(1,i)=(i-1).ame

extI

‘设置字段名字体为加粗并居中对齐

WithRange(Cells(1,1),Cells(1,))

.=True

.HorizontalAlignment=xlCenter

EndWith

‘复制全部数据

Range("A2").CopyFromRecordsetrs

‘设置工作表格式

=10

t

‘关闭记录集及数据库连接，并释放变量

Setrs=othing

Setcnn=othing

EndSub

·SQL语句的Top属性：

SQL=”selecttop*from数据表名条件表达式”‘查询满足条件的最前面的条记录

实例3-11查询不重复的字段记录（DISTICT）

引用：ry

PublicSub3_11()

DimmydataAsString,mytableAsString,SQLAsString

tion

set

DimiAsInteger

'清除工作表的全部数据

'输入标题

感谢下载载

精品

WithRange("A1:C1")

.Value=Array("部门","职称类别","职务类别")

.=True

.HorizontalAlignment=xlCenter

EndWith

'指定数据库和数据表

mydata=&"职工管理.mdb"'指定数据库

mytable="职工基本信息"'指定数据表

'建立与数据库的连接

Setcnn=tion

Withcnn

.Provider=".4.0"

.Openmydata

EndWith

'查询数据表中不重复的部门名称，并复制数据

SQL="selectdistinct所属部门from"&mytable

Setrs=set

L,cnn,adOpenKeyset,adLockOptimistic

Range("A2").CopyFromRecordsetrs

'查询数据表中不重复的职称类别名称，并复制数据

SQL="selectdistinct职称from"&mytable

Setrs=set

L,cnn,adOpenKeyset,adLockOptimistic

Range("B2").CopyFromRecordsetrs

'查询数据表中不重复的职务名称，并复制数据

SQL="selectdistinct职务from"&mytable

Setrs=set

L,cnn,adOpenKeyset,adLockOptimistic

Range("C2").CopyFromRecordsetrs

'设置工作表格式

=10

t

'关闭记录集及数据库连接,并释放变量

Setrs=othing

Setcnn=othing

EndSub

实例3-12利用Like运算符进行模糊查询

引用：ry

PublicSub3_12()

感谢下载载

精品

DimmydataAsString,mytableAsString,SQLAsString

tion

set

DimiAsInteger

'清除工作表的全部数据

'指定数据库和数据表

mydata=&"职工管理.mdb"'指定数据库

mytable="职工基本信息"'指定数据表

'建立与数据库的连接

Setcnn=tion

Withcnn

.Provider=".4.0"

.Openmydata

EndWith

'查询数据表

SQL="select*from"&mytable&"where姓名like'李%'"

Setrs=set

L,cnn,adOpenKeyset,adLockOptimistic

'也可以使用下面的语句

'Setrs=e(SQL)

'复制字段名

Fori=

Cells(1,i)=(i-1).ame

exti

'设置字段名字体为加粗并居中对齐

WithRange(Cells(1,1),Cells(1,))

.=True

.HorizontalAlignment=xlCenter

EndWith

'复制全部数据

Range("A2").CopyFromRecordsetrs

'设置工作表格式

=10

t

'关闭记录集及数据库连接,并释放变量

Setrs=othing

Setcnn=othing

EndSub

•Like运算符：语法：

[OT]LIKE匹配符

匹配符有4种：%，_，[]，^

1.%：表示任意长度任意字符；

2._：表示单个任意字符；

感谢下载载

精品

3.[]：表示在指定范围内的任意单个字符

Like‘[BC]%’：表示以B或C开头的字段数据；

Like‘[B-K]book%’：表示所有5个字母中都以B或K中任意一个字母开头的字段

数据

Like‘_[伟,玮]%’：表示名字中第2个字是伟或玮的字段数据

4.^：表示不在指定范围内的任意单个字符

Like‘[B^a]%’：表示在指定范围内的不含有大写B或小写a的字段数据

5.查询不与某字符相匹配的记录：使用OTLIKE

SQL=”select*from数据表名where商品名称notlike‘%彩电%’”

表示要查询商品名称字段中不含有“彩电”这两个字的字段数据

实例3-13查询某一区间内的记

引用：ry

PublicSub3_13()

DimmydataAsString,mytableAsString,SQLAsString

tion

set

DimiAsInteger

'清除工作表的全部数据

'指定数据库和数据表

mydata=&"职工管理.mdb"'指定数据库

mytable="职工基本信息"'指定数据表

'建立与数据库的连接

Setcnn=tion

Withcnn

.Provider=".4.0"

.Openmydata

EndWith

'查询数据表

SQL="select*from"&mytable_

&"where年龄between30and40orderby年龄DESC"

Setrs=set

L,cnn,adOpenKeyset,adLockOptimistic

'也可以使用下面的语句

'Setrs=e(SQL)

'复制字段名

Fori=

Cells(1,i)=(i-1).ame

exti

'设置字段名字体为加粗并居中对齐

感谢下载载

BETWEE）（

精品

WithRange(Cells(1,1),Cells(1,))

.=True

.HorizontalAlignment=xlCenter

EndWith

'复制全部数据

Range("A2").CopyFromRecordsetrs

'设置工作表格式

=10

t

'关闭记录集及数据库连接,并释放变量

Setrs=othing

Setcnn=othing

EndSub

·SQL语句：

Between子句查询某一区间内的记录，语法：

SQL=”select*from数据表名where字段名between值1and值2

·例：SQL=”select*from”&mytable&“where年龄between30and40orderby年龄desc

‘查询年龄字段中数据在30—40之间的所有字段数据

·例：SQL=”select*from”&mytable&“where年龄notbetween30and40orderby年龄

desc‘查询年龄字段中数据不包含在30—40之间的所有字段数据

实例3-14查询存在于某个集合里面的记录（I）

引用：ry

PublicSub3_14()

DimmydataAsString,mytableAsString,SQLAsString

tion

set

DimiAsInteger

‘清除工作表的全部数据

mydata=&"职工管理.mdb"‘指定数据库

mytable="职工基本信息"‘指定数据表

‘建立与数据库的连接

Setcnn=tion

Withcnn

.Provider=".4.0"

.Openmydata

EndWith

‘查询数据表

SQL="select*from"&mytable_

感谢下载载

精品

&"where年龄>40and职称in('工程师','经济师')orderby年龄DESC"

Setrs=e(SQL)

‘复制字段名

Fori=

Cells(1,i)=(i-1).ame

extI

‘设置字段名字体为加粗并居中对齐

WithRange(Cells(1,1),Cells(1,))

.=True

.HorizontalAlignment=xlCenter

EndWith

‘复制全部数据

Range("A2").CopyFromRecordsetrs

‘设置工作表格式

=10

t

‘关闭记录集及数据库连接，并释放变量

Setrs=othing

Setcnn=othing

EndSub

·SQL语句：

SQL=”select*from数据表名where字段名in(值1,值2,….)”

‘查询存在于某个集合里面的记录

·例：SQL="select*from"&mytable_

&"where年龄>40and职称in('工程师','经济师')orderby年龄DESC"

‘查询年龄大于40且职称为工程师或经济师的字段记录，近年龄降序排列

实例3-15将查询结果进行排序（ORDERBY）

引用：ry

PublicSub3_15()

DimmydataAsString,mytableAsString,SQLAsString

tion

set

DimiAsInteger

‘清除工作表的全部数据

mydata=&"职工管理.mdb"‘指定数据库

mytable="职工基本信息"‘指定数据表

‘建立与数据库的连接

Setcnn=tion

感谢下载载

精品

Withcnn

.Provider=".4.0"

.Openmydata

EndWith

‘查询数据表

SQL="select*from"&mytable_

&"orderby年龄DESC,工龄DESC,本单位工龄DESC"

Setrs=set

L,cnn,adOpenKeyset,adLockOptimistic

'也可以使用下面的语句

'Setrs=e(SQL)

‘复制字段名

Fori=

Cells(1,i)=(i-1).ame

extI

‘设置字段名字体为加粗并居中对齐

WithRange(Cells(1,1),Cells(1,))

.=True

.HorizontalAlignment=xlCenter

EndWith

‘复制全部数据

Range("A2").CopyFromRecordsetrs

‘设置工作表格式

=10

t

‘关闭记录集及数据库连接，并释放变量

Setrs=othing

Setcnn=othing

EndSub

·SQL语句：

SQL=”select*from数据表名orderby字段名ASC(升序)|DESC(降序)”

‘对查询结果按字段名进行排序，默认为升序

例：SQL="select*from"&mytable_

&"orderby年龄DESC,工龄DESC,本单位工龄DESC"

‘按先后顺序对年龄，工龄，本单位工龄进行降序排列

实例3-16进行复杂条件的查询（WHERE）

引用：ry

PublicSub3_16()

DimmydataAsString,mytableAsString,SQLAsString

感谢下载载

精品

tion

set

DimiAsInteger

‘清除工作表的全部数据

mydata=&"职工管理.mdb"‘指定数据库

mytable="职工基本信息"‘指定数据表

‘建立与数据库的连接

Setcnn=tion

Withcnn

.Provider=".4.0"

.Openmydata

EndWith

‘查询数据表

SQL="select*from"&mytable_

&"where职称='工程师'and(年龄between30and40)"_

&"and文化程度in('硕士','博士')orderby职工编号"

Setrs=set

L,cnn,adOpenKeyset,adLockOptimistic

'也可以使用下面的语句

'Setrs=e(SQL)

'复制字段名

Fori=

Cells(1,i)=(i-1).ame

exti

‘设置字段名字体为加粗并居中对齐

WithRange(Cells(1,1),Cells(1,))

.=True

.HorizontalAlignment=xlCenter

EndWith

‘复制全部数据

Range("A2").CopyFromRecordsetrs

‘设置工作表格式

=10

t

‘关闭记录集及数据库连接，并释放变量

Setrs=othing

Setcnn=othing

EndSub

感谢下载载

精品实例3-17利用合计函数进行查询（查询最大值和最小值）

引用：ry

PublicSub3_17()

DimmydataAsString,mytableAsString,SQLAsString

tion

set

DimiAsInteger

'清除工作表的全部数据

'指定数据库和数据表

mydata=&"职工管理.mdb"'指定数据库

mytable="职工基本信息"'指定数据表

'建立与数据库的连接

Setcnn=tion

Withcnn

.Provider=".4.0"

.Openmydata

EndWith

'查询数据表

SQL="selectmax(年龄)asAge1,min(年龄)asAge2,"_

&"max(工龄)asWorkage1,min(工龄)asWorkage2,"_

&"max(本单位工龄)asDage1,min(本单位工龄)asDage2"_

&"from"&mytable

Setrs=set

L,cnn,adOpenKeyset,adLockOptimistic

'也可以使用下面的语句

'Setrs=e(SQL)

'复制数据

Range("A1:F1")=Array("最大年龄","最小年龄","最大工龄",_

"最小工龄","最大本单位工龄","最小本单位工龄")

Range("A2:F2")=Array(rs!Age1,rs!Age2,_

rs!Workage1,rs!Workage2,rs!Dage1,rs!Dage2)

'设置工作表格式

=10

t

'关闭记录集及数据库连接,并释放变量

Setrs=othing

Setcnn=othing

EndSub