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

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(数据表)对象变量DimmyDataAsString定义数据库名称变量DimmyTableAsString定义数据表名称变量设置要创建的数据库名称（包括完整路径）myData=ThisWorkbook.Path&“学生成绩管理.mdb”设置要创建的数据表名称myTable=”期末成绩”删除已经存在的数据库文件onerrorresumenextkillmyDataonerrorgoto0创建数据库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.CloseSetmyDb=NothingSetmyTbl=Nothing弹出信息MsgBox”创建数据库成功！”&vbCrLf&“数据库文件名为：”&myData&vbCrLf&“数据表名称为：”&myTable&vbCrLf&“保存位置：”&ThisWorkbook.Path,vbInformation,”创建数据库”Endsub注：·CreateDatabase方法创建数据库setmydb=createdatabase(mydata,dblangchinesesimplified)mydb:数据库类型变量dblangchinesesimplified：表达字符串比较规则，这里为简体中文·CreateTableDef方法创建数据表SetmyTbl=mydb.Createtabledef(mytable)mytbl:表类型变量mydb:数据库名mytable:表名补充：创建带密码的Access数据库Setmydb=createdatabase(mydata,dblangchinesesimplified&“;pwd=12345”)1-2利用ADOX创建数据库和数据表：引用：microsoftADOExt.2.XforDDLandSecurity代码：publicsub1_2()dimmycatasnewadox.catalog定义ADOX的Catalog对象变量dimmytblasnewtable定义table对象变量dimmydataasstring定义数据库名称变量dimmytableasstring定义数据表名称变量设置要创建的数据库名称（包括完整路径）mydata=thisworkbook.path&“学生成绩管理.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.append“班级”,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“创建数据库成功!”&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,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对象变量Dmand定义Command对象变量dimmydataasstring定义数据库名称变量dimmytableasstring定义数据表名称变量dimSQLasstring设置要创建的数据库名称（包括完整路径）mydata=thisworkbook.path&“学生成绩管理.mdb”设置要创建的数据表名称mytable=”期末成绩”删除已经存在的数据库文件onerrorresumenextkillmydataonerrorgoto0创建数据库文件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,adcmdtextendwith释放变量setmycat=nothingsetmycmd=nothing弹出信息msgbox“创建数据库成功！”&vbcrlf&“数据库文件名为：”&mydata&vbcrlf&“数据表名称为：”&mytable&vbcrlf&“保存位置：”&thisworkbook.path,vbinformation,”创建数据库”endsub注：有两种方法来创建数据表：·利用ADODB.Command对象的commandtext属性和execute方法：mandsetmycmd.activeconnection=mycat.activeconnectionwithmycmd.commandtext=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(数据库)对象变量dimmydataasstring定义数据库名称变量dimmytableasstring定义数据表名称变量设置数据库名称（包括完整路径）mydata=thisworkbook.path&“学生成绩管理.mdb”设置要创建的数据表名称mytable=”期末成绩”打开数据库setmydb=opendatabase(mydata)删除数据库中已经存在的数据表mydb.tabledefs.deletemytable创建新的数据表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=nothingsetmytbl=nothing弹出信息msgbox”数据表<”&mytable&“>创建成功！”,vbinformation,”创建数据表”endsub·补充：opendatabase方法用来打开一个已有的数据库，返回一个数据库对象，并自动将该数据库对象加入到数据库对象集中。setdatabase=workspace.opendatabase(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在已有的数据库中创建数据表（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创建数据表，并添加字段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“总分”,adsingleendwith将创建的数据表添加到ADOX的tables集合中mycat.tables.appendmytbl释放变量setmycat=nothingsetmytbl=nothing弹出信息msgbox“数据表<”&mytable&“>创建成功！”,vbinformation,”创建数据表”endsub注：Activeconnection属性用来指示catalog所属的ADOConnection对象，表示到数据源的打开的连接。1-6在已有的数据库中创建数据表（SQL，Command对象）引用:microsoftactivexdataobjects2.xlibrary和microsoftadoext.2.xforddlandsecurity代码：publicsub1_6()dimmycatasnewadox.catalog定义adox的catalog对象变量mand定义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,adcmdtextEndwith释放变量Setmycat=nothingSetmycmd=nothing弹出信息Msgbox“数据表<”&mytable&“>创建成功！”,vbinformation,”创建数据表”Endsub1-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”.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=nothingsetcnn=nothing弹出信息msgbox“数据表<”&mytable&“>创建成功!”,vbinformation,”创建数据表”endsub1-8利用Access对象创建数据库和数据表引用：microsoftaccess9.0|10.0|11.0objectlibrary代码：PublicSub1_8()DimappAccessAsAccess.Application定义Access应用程序对象变量DimdbsAsObject定义数据库对象变量DimmyTblAsObject定义数据表对象变量DimmyFldAsVariant定义数据字段对象变量DimmyDataAsString定义数据库名称变量DimmyTableAsString定义数据表名称变量设置数据库名称(包括完整路径)myData=ThisWorkbook.Path&"学生成绩管理.mdb"myTable="期末成绩"设置要创建的数据表名称删除已存在的数据库OnErrorResumeNextKillmyDataOnErrorGoTo0创建一个新的microsoftaccess引用SetappAccess=NewAccess.Application创建一个新的Access数据库，并打开appAccess.NewCurrentDatabasemyData设置当前打开的数据库变量（即返回当前在Access窗体中打开的数据库）Setdbs=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=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.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"创建数据库成功！"&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应用程序对象变量DimdbsAsObject定义数据库对象变量DimmyTblAsObject定义数据表对象变量DimmyFldAsVariant定义数据字段对象变量DimmyDataAsString定义数据库名称变量DimmyTableAsString定义数据表名称变量设置数据库名称(包括完整路径)myData=ThisWorkbook.Path&"学生成绩管理.mdb"myTable="期末成绩"设置要创建的数据表名称删除已存在的数据库OnErrorResumeNextKillmyDataOnErrorGoTo0创建一个新的microsoftaccess引用SetappAccess=createobject(“Access.application”)创建一个新的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.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.AppendmyFldSetmyFld=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=NothingSetmyFld=Nothing弹出信息MsgBox"创建数据库成功！"&vbCrLf_&"数据库文件名为："&myData&vbCrLf_&"数据表名称为："&myTable&vbCrLf_&"保存位置："&ThisWorkbook.Path,_vbOKOnly+vbInformation,"创建数据库"EndSub1-9利用Access对象在已有的数据库中创建数据表代码：与1-8不同处加注释，其它相同PublicSub1_9()DimappAccessAsAccess.ApplicationDimdbsAsObjectDimmyTblAsObjectDimmyFldAsVariantDimmyDataAsStringDimmyTableAsStringmyData=ThisWorkbook.Path&"学生成绩管理.mdb"myTable="期末成绩"SetappAccess=NewAccess.Application打开一个现有的Access数据库appAccess.OpenCurrentDatabasemyDataSetdbs=appAccess.CurrentDb删除数据库中已经存在的同名数据表dbs.TableDefs.DeletemyTableSetmyTbl=dbs.CreateTableDef(myTable)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=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.AppendmyFldSetmyFld=myTbl.CreateField("英语",DB_SINGLE)myTbl.Fields.AppendmyFldSetmyFld=myTbl.CreateField("总分",DB_SINGLE)myTbl.Fields.AppendmyFlddbs.TableDefs.AppendmyTblappAccess.QuitSetappAccess=NothingSetdbs=NothingSetmyTbl=NothingSetmyFld=NothingMsgBox"数据表<"&myTable&">创建成功！",_vbOKOnly+vbInformation,"创建数据表"EndSub扩展：appaccess.opencurrentdatabasemydata,”12345”打开带有密码的数据表1-10利用工作表数据创建数据表（ADOX）引用：microsoftadoext.2.xforddlandsecurity代码：PublicSub1_10()DimmyCatAsNewADOX.Catalog定义catalog变量DimmyTableAsNewADOX.Table定义table变量DimmyColumnAsADOX.Column定义column变量DimmyIdxAsNewADOX.Index定义index变量DimwsAsWorksheet定义worksheet变量DimiAsLongDimmyDataAsStringmyData=ThisWorkbook.Path&"学生成绩管理.mdb"指定数据文件判断是否有保存数据表资料的工作表存在OnErrorResumeNextSetws=Worksheets("数据表设计")OnErrorGoTo0IfwsIsNothingThenMsgBox"没有数据表资料存在！",vbCritical,"警告"ExitSubEndIfws.Activate建立与数据库的连接myCat.ActiveConnection="provider=microsoft.jet.oledb.4.0;"_&"datasource="&myData删除已经存在的数据表OnErrorResumeNextmyCat.Tables.Deletews.Range("B1").ValueOnErrorGoTo0建立索引myIdx.Name="PrimaryKey"myIdx.PrimaryKey=True开始根据工作表的数据创建数据表WithmyTable.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).Value>0Then.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"数据表<"&ws.Range("B1").Value&">创建成功！",_