ExcelVBA与数据库Access整合笔记1上课讲义.doc

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

1、Good is good, but better carries it.精益求精，善益求善。ExcelVBA与数据库Access整合笔记1-1-1利用DAO创建数据库和数据表1. 首先建立对DAO对象库MicrosoftDAO3.6ObjectLibrary的引用.在VBA界面下:工具-引用,选中”MicrosoftDAO3.6ObjectLibrary”2. 代码:PublicSub1_1()DimmyDbAsDAO.Database定义DAO的Database(数据库)对象变量DimmyTblAsDAO.TableDef定义DAO的TableDef(数据表)对象变量DimmyDataAsS

2、tring定义数据库名称变量DimmyTableAsString定义数据表名称变量设置要创建的数据库名称（包括完整路径）myData=ThisWorkbook.Path&“学生成绩管理.mdb”设置要创建的数据表名称myTable=”期末成绩”删除已经存在的数据库文件onerrorresumenextkillmyDataonerrorgoto0创建数据库SetmyDb=CreateDatabase(myData,dbLangChineseSimplified)创建数据表SetmyTbl=myDb.CreateTableDef(myTable)为创建的数据表添加各个字段WithmyTbl.Fie

3、lds.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.

4、CreateField(“化学”,dbSingle).Fields.Append.CreateField(“英语”,dbSingle).Fields.Append.CreateField(“总分”,dbSingle)EndWith将创建的数据表添加到数据库的TableDefs集合中myDb.TableDefs.AppendmyTbl关闭数据库，并释放变量myDb.CloseSetmyDb=NothingSetmyTbl=Nothing弹出信息MsgBox”创建数据库成功！”&vbCrLf&“数据库文件名为：”&myData&vbCrLf&“数据表名称为：”&myTable&vbCrLf&“保存

5、位置：”&ThisWorkbook.Path,vbInformation,”创建数据库”Endsub注：CreateDatabase方法创建数据库setmydb=createdatabase(mydata,dblangchinesesimplified)mydb:数据库类型变量dblangchinesesimplified：表达字符串比较规则，这里为简体中文CreateTableDef方法创建数据表SetmyTbl=mydb.Createtabledef(mytable)mytbl:表类型变量mydb:数据库名mytable:表名补充：创建带密码的Access数据库Setmydb=create

6、database(mydata,dblangchinesesimplified&“;pwd=12345”)1-2利用ADOX创建数据库和数据表：引用：microsoftADOExt.2.XforDDLandSecurity代码：publicsub1_2()dimmycatasnewadox.catalog定义ADOX的Catalog对象变量dimmytblasnewtable定义table对象变量dimmydataasstring定义数据库名称变量dimmytableasstring定义数据表名称变量设置要创建的数据库名称（包括完整路径）mydata=thisworkbook.path&“学生

7、成绩管理.mdb”设置要创建的数据表名称mytable=”期末成绩”删除已经存在的数据库文件onerrorresumenextkillmydataonerrorgoto0创建新的数据库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.appe

8、nd“班级”,advarwchar,10.columns.append“数学”,adSingle.columns.append“语文”,adSingle.columns.append“物理”,adSingle.columns.append“化学”,adSingle.columns.append“英语”,adSingle.columns.append“总分”,adSingleEndwith将创建的数据表添加到ADOX的Tables集合中mycat.tables.appendmytbl释放变量setmycat=nothingsetmytbl=nothing弹出信息msgbox“创建数据库成功!”&

9、vbcrlf&“数据库文件名为:”&mydata&vbcrlf&“数据表名称为:”&mytable&vbcrlf&“保存位置:”&thisworkbook.path,vbinformation,”创建数据库”endsub注：在VB中，常用的数据访问接口有下列三种：数据库访问对象（DAO，DataAccessobject）、远程数据库对象（RDO,RemoteDataObject）和ActiveX数据对象（ADO,ActiveXDataObject）ADOX的常用方法：Append方法：可以创建columns,groups,indexes,keys,procedures,tables,users

10、,views等为数据表添加字段：mytbl.columns.append字段名，数据类型，字段长度将创建的数据表添加到ADOX的Tables集合中的语句是：Mycat.tables.appendmytblCreate方法：创建一个新的数据库的语句：Mycat.create“provider=Microsoft.jet.oledb.4.0;datasource=”&mydataDelete方法：删除数据表：Mycat.tables.delete数据表名Refresh方法：用于更新集合中的对象1-3利用SQL语句创建数据库和数据表首先引用：microsoftactiveXdataobjects2.

11、Xlibrary和microsoftadoext.2.xforddlandsecurity”代码：publicsub1_3()dimmycatasnewadox.catalog定义ADOX的Catalog对象变量Dmand定义Command对象变量dimmydataasstring定义数据库名称变量dimmytableasstring定义数据表名称变量dimSQLasstring设置要创建的数据库名称（包括完整路径）mydata=thisworkbook.path&“学生成绩管理.mdb”设置要创建的数据表名称mytable=”期末成绩”删除已经存在的数据库文件onerrorresumenex

12、tkillmydataonerrorgoto0创建数据库文件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方法

13、创建数据表mandtext=sql.execute,adcmdtextendwith释放变量setmycat=nothingsetmycmd=nothing弹出信息msgbox“创建数据库成功！”&vbcrlf&“数据库文件名为：”&mydata&vbcrlf&“数据表名称为：”&mytable&vbcrlf&“保存位置：”&thisworkbook.path,vbinformation,”创建数据库”endsub注：有两种方法来创建数据表：利用ADODB.Command对象的commandtext属性和execute方法：mandsetmycmd.activeconnection=mandt

14、ext=SQL.execute,adcmdtextendwith利用ADODB.Connection对象的execute方法来生成几个记录集Dimcnnasnewadodb.connectiondimrsasnewadodb.recordsetsetcnn=mycat.activeconnectionsetrs=cnn.execute(sql)1-4在已有的数据库中创建数据表（DAO）引用DAO对象库：microsoftDAO3.6objectlibrary代码：publicsub1_4()dimmydbasdao.database定义DAO的database(数据库)对象变量dimmydat

15、aasstring定义数据库名称变量dimmytableasstring定义数据表名称变量设置数据库名称（包括完整路径）mydata=thisworkbook.path&“学生成绩管理.mdb”设置要创建的数据表名称mytable=”期末成绩”打开数据库setmydb=opendatabase(mydata)删除数据库中已经存在的数据表mydb.tabledefs.deletemytable创建新的数据表setmytbl=mydb.createtabledef(mytable)为创建的数据表添加各个字段Withmytbl.fields.append.createfield(学号,dbtext,

16、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.createfi

17、eld(英语,dbsingle).fields.append.createfield(总分,dbsingle)endwith将创建的数据表添加到数据库的TableDefs集合中mydb.tabledefs.appendmytbl关闭数据库mydb.close释放变量setmydb=nothingsetmytbl=nothing弹出信息msgbox”数据表创建成功！”,vbinformation,”创建数据表”endsub补充：opendatabase方法用来打开一个已有的数据库，返回一个数据库对象，并自动将该数据库对象加入到数据库对象集中。setdatabase=workspace.opend

18、atabase(databasename,options,read-only,connect)workspace:定义的Workspace类型变量，它表示所使用的工作环境，将包含新的数据库对象databasename:一个有效的Jet数据库文件或ODBC数据源options:T/F,T表示以独占方式打开数据库，而F表示以共享方式打开数据库read-only:是否以只读方式打开数据库，为T/Fconnect:说明不同连接方式以及密码扩展：利用DAO打开有密码的Access数据库setmydb=opendatabase(mydata,true,false,”;pwd=12345”)1-5在已有的数

19、据库中创建数据表（ADOX）引用：microsoftADOExt.2.xforddlandsecurity代码：publicsub1_5()Dimmycatasnewadox.catalog定义ADOX的catalog对象变量dimmytblasnewtable定义table对象变量dimmydataasstring定义数据库名称变量dimmytableasstring定义数据表名称变量设置数据库名称（包括完整路径）mydata=thisworkbook.path&“学生成绩管理.mdb”设置要创建的数据表名称mytable=”期末成绩”建立与数据库的连接mycat.activeconnect

20、ion=”provider=microsoft.jet.oledb.4.0;”_&“datasource=”&mydata删除数据库中已经存在的数据表mycat.table.deletemytable创建数据表，并添加字段withmytbl.name=mytable.columns.append“学号”,advarwchar,10.columns.append“姓名”,advarwchar,6.columns.append“性别”,advarwchar,1.columns.append“班级”,advarwchar,10.columns.append“数学”,adsingle.columns.

21、append“语文”,adsingle.columns.append“物理”,adsingle.columns.append“化学”,adsingle.columns.append“英语”,adsingle.columns.append“总分”,adsingleendwith将创建的数据表添加到ADOX的tables集合中mycat.tables.appendmytbl释放变量setmycat=nothingsetmytbl=nothing弹出信息msgbox“数据表创建成功！”,vbinformation,”创建数据表”endsub注：Activeconnection属性用来指示catalo

22、g所属的ADOConnection对象，表示到数据源的打开的连接。1-6在已有的数据库中创建数据表（SQL，Command对象）引用:microsoftactivexdataobjects2.xlibrary和microsoftadoext.2.xforddlandsecurity代码：publicsub1_6()dimmycatasnewadox.catalog定义adox的catalog对象变量mand定义command对象变量dimmydataasstring定义数据库名称变量dimmytableasstring定义数据表名称变量dimsqlasstring设置数据库名称（包括完整路径）

23、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),姓

24、名text(6),性别text(1),班级text(10),”_&“数学single,语文single,物理single,化学single,“_&“英语single,总分single)”利用Execute方法创建数据表Wmandtext=sql.execute,adcmdtextEndwith释放变量Setmycat=nothingSetmycmd=nothing弹出信息Msgbox“数据表创建成功！”,vbinformation,”创建数据表”Endsub1-7在已有的数据库中创建数据表（SQL，Recordset对象）引用：MicrosoftActiveXDataObjects2.Xlib

25、rary代码：publicsub1_7()dimcnnasnewadodb.connection定义connection对象变量dimrsasnewadodb.recordset定义Recordset对象变量dimmydataasstring定义数据库名称变量dimmytableasstring定义数据表名称变量dimsqlasstring设置数据库名称(包括完整路径)mydata=thisworkbook.path&“学生成绩管理.mdb”设置要创建的数据表名称mytable=”期末成绩”建立与数据库的连接withcnn.provider=”microsoft.jet.oledb.4.0”.

26、openmydataendwith删除数据库中已经存在的数据表SQL=”droptable”&mytablesetrs=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=nothingsetrs

27、=nothingsetcnn=nothing弹出信息msgbox“数据表创建成功!”,vbinformation,”创建数据表”endsub1-8利用Access对象创建数据库和数据表引用：microsoftaccess9.0|10.0|11.0objectlibrary代码：PublicSub1_8()DimappAccessAsAccess.Application定义Access应用程序对象变量DimdbsAsObject定义数据库对象变量DimmyTblAsObject定义数据表对象变量DimmyFldAsVariant定义数据字段对象变量DimmyDataAsString定义数据库名称

28、变量DimmyTableAsString定义数据表名称变量设置数据库名称(包括完整路径)myData=ThisWorkbook.Path&学生成绩管理.mdbmyTable=期末成绩设置要创建的数据表名称删除已存在的数据库OnErrorResumeNextKillmyDataOnErrorGoTo0创建一个新的microsoftaccess引用SetappAccess=NewAccess.Application创建一个新的Access数据库，并打开appAccess.NewCurrentDatabasemyData设置当前打开的数据库变量（即返回当前在Access窗体中打开的数据库）Setdb

29、s=appAccess.CurrentDb创建数据表SetmyTbl=dbs.CreateTableDef(myTable)为数据表添加字段，并用append方法将这些字段添加到Fields集合里SetmyFld=myTbl.CreateField(学号,DB_Text,10)myTbl.Fields.AppendmyFldSetmyFld=myTbl.CreateField(姓名,DB_Text,6)myTbl.Fields.AppendmyFldSetmyFld=myTbl.CreateField(性别,DB_Text,1)myTbl.Fields.AppendmyFldSetmyFld=

30、myTbl.CreateField(班级,DB_Text,10)myTbl.Fields.AppendmyFldSetmyFld=myTbl.CreateField(数学,DB_SINGLE)myTbl.Fields.AppendmyFldSetmyFld=myTbl.CreateField(语文,DB_SINGLE)myTbl.Fields.AppendmyFldSetmyFld=myTbl.CreateField(物理,DB_SINGLE)myTbl.Fields.AppendmyFldSetmyFld=myTbl.CreateField(化学,DB_SINGLE)myTbl.Fields

31、.AppendmyFldSetmyFld=myTbl.CreateField(英语,DB_SINGLE)myTbl.Fields.AppendmyFldSetmyFld=myTbl.CreateField(总分,DB_SINGLE)myTbl.Fields.AppendmyFld用append方法将数据表添加到tabledefs集合里dbs.TableDefs.AppendmyTbl关闭Access应用程序appAccess.Quit释放变量SetappAccess=NothingSetdbs=NothingSetmyTbl=NothingSetmyFld=Nothing弹出信息MsgBox创

32、建数据库成功！&vbCrLf_&数据库文件名为：&myData&vbCrLf_&数据表名称为：&myTable&vbCrLf_&保存位置：&ThisWorkbook.Path,_vbOKOnly+vbInformation,创建数据库EndSub1-8-1不引用Access对象库而使用Access的有关对象、属性和方法（先引用access对象库就是前绑定）引用：microsoftaccess9.0|10.0|11.0objectlibrary代码：与1-8不同处用颜色标出PublicSub1_8_1()DimappAccessAsobject定义Access应用程序对象变量DimdbsAsOb

33、ject定义数据库对象变量DimmyTblAsObject定义数据表对象变量DimmyFldAsVariant定义数据字段对象变量DimmyDataAsString定义数据库名称变量DimmyTableAsString定义数据表名称变量设置数据库名称(包括完整路径)myData=ThisWorkbook.Path&学生成绩管理.mdbmyTable=期末成绩设置要创建的数据表名称删除已存在的数据库OnErrorResumeNextKillmyDataOnErrorGoTo0创建一个新的microsoftaccess引用SetappAccess=createobject(“Access.appl

34、ication”)创建一个新的Access数据库，并打开appAccess.NewCurrentDatabasemyData设置当前打开的数据库变量（即返回当前在Access窗体中打开的数据库）Setdbs=appAccess.CurrentDb创建数据表SetmyTbl=dbs.CreateTableDef(myTable)为数据表添加字段，并用append方法将这些字段添加到Fields集合里SetmyFld=myTbl.CreateField(学号,10,10)myTbl.Fields.AppendmyFldSetmyFld=myTbl.CreateField(姓名,10,6)myTbl

35、.Fields.AppendmyFldSetmyFld=myTbl.CreateField(性别,10,1)myTbl.Fields.AppendmyFldSetmyFld=myTbl.CreateField(班级,10,10)myTbl.Fields.AppendmyFldSetmyFld=myTbl.CreateField(数学,6)myTbl.Fields.AppendmyFldSetmyFld=myTbl.CreateField(语文,6)myTbl.Fields.AppendmyFldSetmyFld=myTbl.CreateField(物理,6)myTbl.Fields.Appen

36、dmyFldSetmyFld=myTbl.CreateField(化学,6)myTbl.Fields.AppendmyFldSetmyFld=myTbl.CreateField(英语,6)myTbl.Fields.AppendmyFldSetmyFld=myTbl.CreateField(总分,6)myTbl.Fields.AppendmyFld用append方法将数据表添加到tabledefs集合里dbs.TableDefs.AppendmyTbl关闭Access应用程序appAccess.Quit释放变量SetappAccess=NothingSetdbs=NothingSetmyTbl=

37、NothingSetmyFld=Nothing弹出信息MsgBox创建数据库成功！&vbCrLf_&数据库文件名为：&myData&vbCrLf_&数据表名称为：&myTable&vbCrLf_&保存位置：&ThisWorkbook.Path,_vbOKOnly+vbInformation,创建数据库EndSub1-9利用Access对象在已有的数据库中创建数据表代码：与1-8不同处加注释，其它相同PublicSub1_9()DimappAccessAsAccess.ApplicationDimdbsAsObjectDimmyTblAsObjectDimmyFldAsVariantDimmyD

38、ataAsStringDimmyTableAsStringmyData=ThisWorkbook.Path&学生成绩管理.mdbmyTable=期末成绩SetappAccess=NewAccess.Application打开一个现有的Access数据库appAccess.OpenCurrentDatabasemyDataSetdbs=appAccess.CurrentDb删除数据库中已经存在的同名数据表dbs.TableDefs.DeletemyTableSetmyTbl=dbs.CreateTableDef(myTable)SetmyFld=myTbl.CreateField(学号,DB_T

39、ext,10)myTbl.Fields.AppendmyFldSetmyFld=myTbl.CreateField(姓名,DB_Text,6)myTbl.Fields.AppendmyFldSetmyFld=myTbl.CreateField(性别,DB_Text,1)myTbl.Fields.AppendmyFldSetmyFld=myTbl.CreateField(班级,DB_Text,10)myTbl.Fields.AppendmyFldSetmyFld=myTbl.CreateField(数学,DB_SINGLE)myTbl.Fields.AppendmyFldSetmyFld=myT

40、bl.CreateField(语文,DB_SINGLE)myTbl.Fields.AppendmyFldSetmyFld=myTbl.CreateField(物理,DB_SINGLE)myTbl.Fields.AppendmyFldSetmyFld=myTbl.CreateField(化学,DB_SINGLE)myTbl.Fields.AppendmyFldSetmyFld=myTbl.CreateField(英语,DB_SINGLE)myTbl.Fields.AppendmyFldSetmyFld=myTbl.CreateField(总分,DB_SINGLE)myTbl.Fields.App

41、endmyFlddbs.TableDefs.AppendmyTblappAccess.QuitSetappAccess=NothingSetdbs=NothingSetmyTbl=NothingSetmyFld=NothingMsgBox数据表创建成功！,_vbOKOnly+vbInformation,创建数据表EndSub扩展：appaccess.opencurrentdatabasemydata,”12345”打开带有密码的数据表1-10利用工作表数据创建数据表（ADOX）引用：microsoftadoext.2.xforddlandsecurity代码：PublicSub1_10()Di

42、mmyCatAsNewADOX.Catalog定义catalog变量DimmyTableAsNewADOX.Table定义table变量DimmyColumnAsADOX.Column定义column变量DimmyIdxAsNewADOX.Index定义index变量DimwsAsWorksheet定义worksheet变量DimiAsLongDimmyDataAsStringmyData=ThisWorkbook.Path&学生成绩管理.mdb指定数据文件判断是否有保存数据表资料的工作表存在OnErrorResumeNextSetws=Worksheets(数据表设计)OnErrorGoTo

43、0IfwsIsNothingThenMsgBox没有数据表资料存在！,vbCritical,警告ExitSubEndIfws.Activate建立与数据库的连接myCat.ActiveConnection=provider=microsoft.jet.oledb.4.0;_&datasource=&myData删除已经存在的数据表OnErrorResumeNextmyCat.Tables.Deletews.Range(B1).ValueOnErrorGoTo0建立索引myIdx.Name=PrimaryKeymyIdx.PrimaryKey=True开始根据工作表的数据创建数据表WithmyT

44、able.Name=ws.Range(B1).ValueFori=4Tows.Range(A65536).End(xlUp).RowSetmyColumn=NewColumnWithmyColumn.Name=ws.Cells(i,1).Value.Type=GetConstNo(ws.Cells(i,2).Value)Ifws.Cells(i,3).Value0Then.DefinedSize=ws.Cells(i,3).Value.Attributes=adColNullableEndIfEndWith.Columns.AppendmyColumnIfws.Cells(i,4).Value=是ThenmyIdx.Columns.Appendws.Cells(i,1).ValueEndIfNextEndWith将表定义进行保存myCat.Tables.AppendmyTablemyTable.Indexes.AppendmyIdx弹出信息MsgBox数据表创建成功！,_