Excel VBA与数据库(Access)整合笔记

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对象变量Dimmycmdasnewadodb.command‘定义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方法：dimmycmdasnewadodb.commandsetmycmd.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‘关闭