ExcelVBA与数据库Access整合笔记1.doc

《ExcelVBA与数据库Access整合笔记1.doc》由会员分享，可在线阅读，更多相关《ExcelVBA与数据库Access整合笔记1.doc（233页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、【精品文档】如有侵权，请联系网站删除，仅供学习与交流ExcelVBA与数据库Access整合笔记1.精品文档.1-1 利用DAO创建数据库和数据表1. 首先建立对DAO对象库Microsoft DAO3.6 Object Library的引用.在VBA界面下:工具-引用,选中”Microsoft DAO3.6 Object Library”2. 代码:Public Sub1_1()Dim myDb As DAO.Database 定义DAO的Database(数据库)对象变量Dim myTbl As DAO.TableDef 定义DAO的TableDef(数据表)对象变量Dim myData

2、As String 定义数据库名称变量Dim myTable As String 定义数据表名称变量设置要创建的数据库名称（包括完整路径）myData=ThisWorkbook.Path & “学生成绩管理.mdb”设置要创建的数据表名称myTable=”期末成绩”删除已经存在的数据库文件on error resume nextkill myDataon error goto 0创建数据库Set myDb=CreateDatabase(myData,dbLangChineseSimplified)创建数据表Set myTbl=myDb.CreateTableDef(myTable)为创建的数据

3、表添加各个字段With myTbl .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

4、(“物理”,dbSingle).Fields.Append .CreateField(“化学”,dbSingle).Fields.Append .CreateField(“英语”,dbSingle).Fields.Append .CreateField(“总分”,dbSingle)End With将创建的数据表添加到数据库的TableDefs集合中myDb.TableDefs.Append myTbl关闭数据库，并释放变量myDb.CloseSet myDb=NothingSet myTbl=Nothing弹出信息MsgBox”创建数据库成功！” & vbCrLf & “数据库文件名为：” &

5、 myData & vbCrLf & “数据表名称为：” & myTable & vbCrLf & “保存位置：” & ThisWorkbook.Path,vbInformation,”创建数据库”End sub注：CreateDatabase方法创建数据库set mydb=createdatabase(mydata,dblangchinesesimplified)mydb:数据库类型变量dblangchinesesimplified：表达字符串比较规则，这里为简体中文CreateTableDef方法创建数据表Set myTbl=mydb.Createtabledef(mytable)mytb

6、l:表类型变量mydb:数据库名mytable:表名补充：创建带密码的Access数据库Set mydb=createdatabase(mydata, dblangchinesesimplified & “;pwd=12345”)1-2 利用ADOX创建数据库和数据表：引用：microsoft ADO Ext.2.X for DDL and Security代码：public sub1_2()dim mycat as new adox.catalog 定义ADOX的Catalog对象变量dim mytbl as new table 定义table对象变量dim mydata as string

7、 定义数据库名称变量dim mytable as string 定义数据表名称变量设置要创建的数据库名称（包括完整路径）mydata=thisworkbook.path & “学生成绩管理.mdb”设置要创建的数据表名称mytable=”期末成绩”删除已经存在的数据库文件on error resume nextkill mydataon error goto 0创建新的数据库mycat.create”provider=microsoft.jet.oledb.4.0;data source=” & mydata创建数据表，并添加字段with mytbl .name=mytable .column

8、s.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 “总分”,adSing

9、leEnd with将创建的数据表添加到ADOX的Tables集合中mycat.tables.append mytbl释放变量set mycat=nothingset mytbl=nothing弹出信息msgbox “创建数据库成功!” & vbcrlf & “数据库文件名为:” & mydata & vbcrlf & “数据表名称为:” & mytable & vbcrlf & “保存位置:” & thisworkbook.path,vbinformation,”创建数据库”end sub注：在VB中，常用的数据访问接口有下列三种：数据库访问对象（DAO，DataAccess object）

10、、远程数据库对象（RDO,Remote Data Object）和ActiveX数据对象（ADO,ActiveX Data Object）ADOX的常用方法：Append方法：可以创建columns,groups,indexes,keys,procedures,tables,users,views等 为数据表添加字段： mytbl.columns.append 字段名，数据类型，字段长度 将创建的数据表添加到ADOX的Tables集合中的语句是： Mycat.tables.append mytblCreate方法：创建一个新的数据库的语句： Mycat.create “provider=Mic

11、rosoft.jet.oledb.4.0;data source=” & mydataDelete方法：删除数据表： Mycat.tables.delete 数据表名Refresh方法：用于更新集合中的对象1-3 利用SQL语句创建数据库和数据表首先引用：microsoft activeX data objects 2.X library 和 microsoft ado ext.2.x for ddl and security”代码：public sub1_3()dim mycat as new adox.catalog 定义ADOX的Catalog对象变量Dim mycmd as new m

12、and 定义Command对象变量dim mydata as string 定义数据库名称变量dim mytable as string 定义数据表名称变量dim SQL as string设置要创建的数据库名称（包括完整路径）mydata=thisworkbook.path & “学生成绩管理.mdb”设置要创建的数据表名称mytable=”期末成绩”删除已经存在的数据库文件on error resume nextkill mydataon error goto 0创建数据库文件mycat.create “provider=microsoft.jet.oledb.4.0;Data sourc

13、e=” & mydata设置数据库连接set mycmd.activeconnection=mycat.activeconnection设置创建数据表的SQL语句 SQL = CREATE TABLE & myTable _ & (学号 text(10),姓名 text(6),性别 text(1),班级 text(10), _ & 数学 Single,语文 Single,物理 Single,化学 Single, _ & 英语 Single,总分 Single)利用execute方法创建数据表with mycmd .commandtext=sql .execute, , adcmdtextend

14、 with释放变量set mycat=nothingset mycmd=nothing弹出信息 msgbox “创建数据库成功！” & vbcrlf & “数据库文件名为：” & mydata & vbcrlf & “数据表名称为：” & mytable & vbcrlf & “保存位置：” & thisworkbook.path,vbinformation,”创建数据库”end sub注：有两种方法来创建数据表： 利用ADODB.Command对象的commandtext属性和execute方法：dim mycmd as new mandset mycmd.activeconnection=

15、mycat.activeconnectionwith mycmd .commandtext=SQL .execute, , adcmdtextend with 利用ADODB.Connection对象的execute方法来生成几个记录集Dim cnn as new adodb.connectiondim rs as new adodb.recordsetset cnn=mycat.activeconnectionset rs=cnn.execute(sql)1-4 在已有的数据库中创建数据表（DAO）引用DAO对象库：microsoft DAO 3.6 object library代码：pub

16、lic sub1_4()dim mydb as dao.database 定义DAO的database(数据库)对象变量dim mydata as string 定义数据库名称变量dim mytable as string 定义数据表名称变量设置数据库名称（包括完整路径）mydata=thisworkbook.path & “学生成绩管理.mdb”设置要创建的数据表名称mytable=”期末成绩”打开数据库set mydb=opendatabase(mydata)删除数据库中已经存在的数据表mydb.tabledefs.delete mytable创建新的数据表set mytbl=mydb.c

17、reatetabledef(mytable)为创建的数据表添加各个字段With mytbl .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(语文, dbsing

18、le) .fields.append .createfield(物理, dbsingle) .fields.append .createfield(化学, dbsingle) .fields.append .createfield(英语, dbsingle) .fields.append .createfield(总分, dbsingle)end with将创建的数据表添加到数据库的TableDefs集合中mydb.tabledefs.append mytbl关闭数据库mydb.close释放变量set mydb=nothingset mytbl=nothing弹出信息msgbox”数据表创建

19、成功！”,vbinformation,”创建数据表”end sub补充：opendatabase方法用来打开一个已有的数据库，返回一个数据库对象，并自动将该数据库对象加入到数据库对象集中。set database=workspace.opendatabase(databasename,options,read-only,connect)workspace:定义的Workspace类型变量，它表示所使用的工作环境，将包含新的数据库对象databasename:一个有效的Jet数据库文件或ODBC数据源options:T/F,T表示以独占方式打开数据库，而F表示以共享方式打开数据库read-onl

20、y:是否以只读方式打开数据库，为T/Fconnect:说明不同连接方式以及密码扩展：利用DAO打开有密码的Access数据库set mydb=opendatabase(mydata,true,false,”;pwd=12345”)1-5 在已有的数据库中创建数据表（ADOX） 引用：microsoft ADO Ext.2.x for ddl and security 代码：public sub1_5()Dim mycat as new adox.catalog 定义ADOX的catalog对象变量dim mytbl as new table 定义table对象变量dim mydata as s

21、tring 定义数据库名称变量dim mytable as string 定义数据表名称变量设置数据库名称（包括完整路径）mydata=thisworkbook.path & “学生成绩管理.mdb”设置要创建的数据表名称mytable=”期末成绩”建立与数据库的连接mycat.activeconnection=”provider=microsoft.jet.oledb.4.0;”_ & “data source=” & mydata删除数据库中已经存在的数据表mycat.table.delete mytable创建数据表，并添加字段with mytbl .name=mytable .colu

22、mns.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

23、.append “总分”, adsingleend with将创建的数据表添加到ADOX的tables集合中mycat.tables.append mytbl释放变量set mycat=nothingset mytbl=nothing弹出信息msgbox “数据表创建成功！”,vbinformation,”创建数据表”end sub注：Activeconnection属性用来指示catalog所属的ADO Connection对象，表示到数据源的打开的连接。1-6 在已有的数据库中创建数据表（SQL，Command对象） 引用:microsoft activex data objects2.x

24、 library和microsoft ado ext.2.x for ddl and security 代码：public sub1_6()dim mycat as new adox.catalog 定义adox的catalog对象变量dim mycmd as new mand 定义command对象变量dim mydata as string 定义数据库名称变量dim mytable as string 定义数据表名称变量dim sql as string 设置数据库名称（包括完整路径）mydata=thisworkbook.path & “学生成绩管理.mdb”设置要创建的数据表名称myt

25、able=”期末成绩”建立与数据库的连接mycat.activeconnection=”provider=microsoft.jet.oledb.4.0;”_ & “data source=” & mydata删除数据库中已经存在的数据表mycat.tables.delete mytable设置数据库连接set mycmd.activeconnection=mycat.activeconnection设置创建数据表的SQL语句SQL=”CREATE TABLE” & mytable_ & “(学号 text(10),姓名 text(6),性别 text(1),班级 text(10),” _ &

26、 “数学 single,语文 single,物理 single,化学 single, “_ & “英语 single,总分 single)”利用Execute方法创建数据表With mycmd .commandtext=sql .execute, , adcmdtextEnd with释放变量Set mycat=nothingSet mycmd=nothing弹出信息Msgbox “数据表创建成功！”,vbinformation,”创建数据表”End sub 1-7 在已有的数据库中创建数据表（SQL，Recordset对象） 引用：Microsoft ActiveX Data Objects

27、 2.X library 代码：public sub1_7()dim cnn as new adodb.connection 定义connection对象变量dim rs as new adodb.recordset 定义Recordset对象变量dim mydata as string 定义数据库名称变量dim mytable as string 定义数据表名称变量dim sql as string设置数据库名称(包括完整路径)mydata=thisworkbook.path & “学生成绩管理.mdb”设置要创建的数据表名称mytable=”期末成绩”建立与数据库的连接with cnn .

28、provider=”microsoft.jet.oledb.4.0” .open mydataend with删除数据库中已经存在的数据表SQL=”drop table” & mytableset rs=cnn.execute(sql)设置创建数据表的SQL语句sql=”create table” & mytable _ & “(学号 text(10),姓名 text(6), 性别 text(1), 班级 text(10),” _ & “数学 single, 语文 single, 物理 single, 化学 single,” _ & “英语 single, 总分 single)”利用execu

29、te方法创建数据表 set rs=cnn.execute(sql)关闭数据库 cnn.close释放变量set mycat=nothingset rs=nothingset cnn=nothing弹出信息msgbox “数据表创建成功!”, vbinformation,”创建数据表”end sub1-8 利用Access对象创建数据库和数据表 引用：microsoft access 9.0|10.0|11.0 object library 代码： Public Sub1_8() Dim appAccess As Access.Application 定义Access应用程序对象变量 Dim d

30、bs As Object 定义数据库对象变量Dim myTbl As Object 定义数据表对象变量 Dim myFld As Variant 定义数据字段对象变量 Dim myData As String 定义数据库名称变量 Dim myTable As String 定义数据表名称变量设置数据库名称(包括完整路径)myData = ThisWorkbook.Path & 学生成绩管理.mdb myTable = 期末成绩 设置要创建的数据表名称删除已存在的数据库 On Error Resume Next Kill myDataOn Error GoTo 0创建一个新的microsoft

31、access引用 Set appAccess = New Access.Application 创建一个新的Access数据库，并打开appAccess.NewCurrentDatabase myData设置当前打开的数据库变量（即返回当前在Access窗体中打开的数据库） Set dbs = appAccess.CurrentDb 创建数据表Set myTbl = dbs.CreateTableDef(myTable) 为数据表添加字段，并用append方法将这些字段添加到Fields集合里Set myFld = myTbl.CreateField(学号, DB_Text, 10) myTb

32、l.Fields.Append myFld Set myFld = myTbl.CreateField(姓名, DB_Text, 6) myTbl.Fields.Append myFld Set myFld = myTbl.CreateField(性别, DB_Text, 1) myTbl.Fields.Append myFld Set myFld = myTbl.CreateField(班级, DB_Text, 10) myTbl.Fields.Append myFld Set myFld = myTbl.CreateField(数学, DB_SINGLE) myTbl.Fields.App

33、end myFld Set myFld = myTbl.CreateField(语文, DB_SINGLE) myTbl.Fields.Append myFld Set myFld = myTbl.CreateField(物理, DB_SINGLE) myTbl.Fields.Append myFld Set myFld = myTbl.CreateField(化学, DB_SINGLE) myTbl.Fields.Append myFld Set myFld = myTbl.CreateField(英语, DB_SINGLE) myTbl.Fields.Append myFld Set my

34、Fld = myTbl.CreateField(总分, DB_SINGLE) myTbl.Fields.Append myFld 用append方法将数据表添加到tabledefs集合里dbs.TableDefs.Append myTbl关闭Access应用程序 appAccess.Quit 释放变量Set appAccess = Nothing Set dbs = Nothing Set myTbl = Nothing Set myFld = Nothing 弹出信息MsgBox 创建数据库成功！ & vbCrLf _ & 数据库文件名为： & myData & vbCrLf _ & 数据表

35、名称为： & myTable & vbCrLf _ & 保存位置： & ThisWorkbook.Path, _ vbOKOnly + vbInformation, 创建数据库End Sub1-8-1 不引用Access对象库而使用Access的有关对象、属性和方法（先引用access对象库就是前绑定） 引用：microsoft access 9.0|10.0|11.0 object library 代码：与1-8不同处用颜色标出 Public Sub1_8_1() Dim appAccess As object 定义Access应用程序对象变量 Dim dbs As Object 定义数据库

36、对象变量Dim myTbl As Object 定义数据表对象变量 Dim myFld As Variant 定义数据字段对象变量 Dim myData As String 定义数据库名称变量 Dim myTable As String 定义数据表名称变量设置数据库名称(包括完整路径)myData = ThisWorkbook.Path & 学生成绩管理.mdb myTable = 期末成绩 设置要创建的数据表名称删除已存在的数据库 On Error Resume Next Kill myDataOn Error GoTo 0创建一个新的microsoft access引用 Set appAc

37、cess = createobject(“Access.application”) 创建一个新的Access数据库，并打开appAccess.NewCurrentDatabase myData设置当前打开的数据库变量（即返回当前在Access窗体中打开的数据库） Set dbs = appAccess.CurrentDb 创建数据表Set myTbl = dbs.CreateTableDef(myTable) 为数据表添加字段，并用append方法将这些字段添加到Fields集合里Set myFld = myTbl.CreateField(学号, 10, 10) myTbl.Fields.Ap

38、pend myFld Set myFld = myTbl.CreateField(姓名, 10, 6) myTbl.Fields.Append myFld Set myFld = myTbl.CreateField(性别, 10, 1) myTbl.Fields.Append myFld Set myFld = myTbl.CreateField(班级, 10, 10) myTbl.Fields.Append myFld Set myFld = myTbl.CreateField(数学, 6) myTbl.Fields.Append myFld Set myFld = myTbl.Create

39、Field(语文, 6) myTbl.Fields.Append myFld Set myFld = myTbl.CreateField(物理, 6) myTbl.Fields.Append myFld Set myFld = myTbl.CreateField(化学, 6) myTbl.Fields.Append myFld Set myFld = myTbl.CreateField(英语, 6) myTbl.Fields.Append myFld Set myFld = myTbl.CreateField(总分, 6) myTbl.Fields.Append myFld 用append方法

40、将数据表添加到tabledefs集合里dbs.TableDefs.Append myTbl关闭Access应用程序 appAccess.Quit 释放变量Set appAccess = Nothing Set dbs = Nothing Set myTbl = Nothing Set myFld = Nothing 弹出信息MsgBox 创建数据库成功！ & vbCrLf _ & 数据库文件名为： & myData & vbCrLf _ & 数据表名称为： & myTable & vbCrLf _ & 保存位置： & ThisWorkbook.Path, _ vbOKOnly + vbInfo

41、rmation, 创建数据库End Sub1-9 利用Access对象在已有的数据库中创建数据表代码：与1-8不同处加注释，其它相同Public Sub1_9() Dim appAccess As Access.Application Dim dbs As Object Dim myTbl As Object Dim myFld As Variant Dim myData As String Dim myTable As String myData = ThisWorkbook.Path & 学生成绩管理.mdb myTable = 期末成绩 Set appAccess = New Acces

42、s.Application 打开一个现有的Access数据库appAccess.OpenCurrentDatabase myData Set dbs = appAccess.CurrentDb 删除数据库中已经存在的同名数据表 dbs.TableDefs.Delete myTable Set myTbl = dbs.CreateTableDef(myTable) Set myFld = myTbl.CreateField(学号, DB_Text, 10) myTbl.Fields.Append myFld Set myFld = myTbl.CreateField(姓名, DB_Text, 6

43、) myTbl.Fields.Append myFld Set myFld = myTbl.CreateField(性别, DB_Text, 1) myTbl.Fields.Append myFld Set myFld = myTbl.CreateField(班级, DB_Text, 10) myTbl.Fields.Append myFld Set myFld = myTbl.CreateField(数学, DB_SINGLE) myTbl.Fields.Append myFld Set myFld = myTbl.CreateField(语文, DB_SINGLE) myTbl.Fields.Append myFld Set myFld = myTbl.CreateField(物理, DB_SINGLE) my