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

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

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

3、ithmyTbl.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).Fi

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

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

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

7、ook.path&“学生成绩管理.mdb”设置要创建的数据表名称mytable=”期末成绩”删除已经存在的数据库文件onerrorresumenextkillmydataonerrorgoto0创建新的数据库mycat.create”provider=microsoft.jet.oledb.4.0;datasource=”&mydata创建数据表，并添加字段=mytable.columns.append“学号”,advarwchar,10.columns.append“姓名”,advarwchar,6.columns.append“性别”,advarwchar,1.

8、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弹出信息msgbo

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

10、tables,users,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语句创建数据库和数据表首先引用：microsoftactiveXd

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

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

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

14、ection=mandtext=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(数据库)

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

16、d(学号,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.app

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

18、kspace.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=123

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

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

21、gle.columns.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

22、属性用来指示catalog所属的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_&“(

24、学号text(10),姓名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对象）引用：MicrosoftActiveXDataO

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

26、.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=

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

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

29、打开的数据库）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.Appendmy

30、FldSetmyFld=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)

31、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=Nothin

32、g弹出信息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应用程序对象

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

34、“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(姓

35、名,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.

36、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=Noth

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

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

39、ield(学号,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.AppendmyFld

40、SetmyFld=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)myTb

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

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

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

44、创建数据表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).Value0Then.DefinedSize=ws.Cells(i,3).Value.Attributes=adColNullableEndIfEndWith.Columns.AppendmyColumnIfws.Cell

45、s(i,4).Value=是ThenmyIdx.Columns.Appendws.Cells(i,1).ValueEndIfNextEndWith将表定义进行保存myCat.Tables.AppendmyTablemyTable.Indexes.AppendmyIdx弹出信息MsgBox数据表创建成功！,_vbOKOnly+vbInformation,创建数据表关闭连接，并释放变量Setws=NothingSetmyIdx=NothingSetmyTable=NothingSetmyCat=NothingEndSub将工作表中定义的数据类型（字符串型）转换为字段类型VBA常量，即编制一个自定义

46、函数GetConstNoFunctionGetConstNo(myStrAsString)AsIntegerSelectCasemyStrCaseadBigInt:GetConstNo=20CaseadBinary:GetConstNo=128CaseadBoolean:GetConstNo=11CaseadBSTR:GetConstNo=8CaseadChapter:GetConstNo=136CaseadChar:GetConstNo=129CaseadCurrency:GetConstNo=6CaseadDate:GetConstNo=7CaseadDBDate:GetConstNo=1

47、33CaseadDBTime:GetConstNo=134CaseadDBTimeStamp:GetConstNo=135CaseadDecimal:GetConstNo=14CaseadDouble:GetConstNo=5CaseadEmpty:GetConstNo=0CaseadError:GetConstNo=10CaseadFileTime:GetConstNo=64CaseadGUID:GetConstNo=72CaseadIDispatch:GetConstNo=9CaseadInteger:GetConstNo=3CaseadIUnknown:GetConstNo=13Case

48、adLongVarBinary:GetConstNo=205CaseadLongVarChar:GetConstNo=201CaseadLongVarWChar:GetConstNo=203CaseadNumeric:GetConstNo=131CaseadPropVariant:GetConstNo=138CaseadSingle:GetConstNo=4CaseadSmallInt:GetConstNo=2CaseadTinyInt:GetConstNo=16CaseadUnsignedBigInt:GetConstNo=21CaseadUnsignedInt:GetConstNo=19C

49、aseadUnsignedSmallInt:GetConstNo=18CaseadUnsignedTinyInt:GetConstNo=17CaseadUserDefined:GetConstNo=132CaseadVarBinary:GetConstNo=204CaseadVarChar:GetConstNo=200CaseadVariant:GetConstNo=12CaseadVarNumeric:GetConstNo=139CaseadVarWChar:GetConstNo=202CaseadWChar:GetConstNo=130CaseElse:GetConstNo=-1EndSe

50、lectEndFunction1-11利用工作表数据创建数据表（ADO+SQL）引用：microsoftactiveXdataobjects2.Xlibrary代码：PublicSub1_11()DimcnnAsNewADODB.Connection定义connection对象变量DimrsAsNewADODB.Recordset定义recordset对象变量DimwsAsWorksheet定义worksheet对象变量DimiAsLongDimmyDataAsString,SQLAsStringmyData=ThisWorkbook.Path&学生成绩管理.mdb指定数据库文件判断是否有保存

51、数据表资料的工作表存在OnErrorResumeNextSetws=Worksheets(数据表设计)OnErrorGoTo0IfwsIsNothingThenMsgBox没有数据表资料存在！,vbCritical,警告ExitSubEndIfws.Activate建立与数据库的连接Setcnn=NewADODB.ConnectionWithcnn.Provider=microsoft.jet.oledb.4.0.OpenmyDataEndWith删除已经存在的数据表OnErrorResumeNextSQL=droptable&ws.Range(B1).ValueSetrs=cnn.Execu

52、te(SQL)OnErrorGoTo0生成创建数据表的SQL语句字符串SQL=createtable&ws.Range(B1).Value&(Fori=4Tows.Range(A65536).End(xlUp).RowSQL=SQL&ws.Cells(i,1).Value&ws.Cells(i,2).ValueIfws.Cells(i,3).Value0ThenSQL=SQL&(&ws.Cells(i,3).Value&)EndIfIfws.Cells(i,4).Value=是ThenSQL=SQL&primarykeyEndIfSQL=SQL&,NextSQL=Left(SQL,Len(SQ

53、L)-1)&)创建数据表Setrs=cnn.Execute(SQL)MsgBox数据表创建成功！,_vbOKOnly+vbInformation,创建数据表关闭连接，并释放变量cnn.CloseSetws=NothingSetrs=NothingSetcnn=NothingEndSub1-12利用工作表数据创建数据表（DAO）引用：microsoftDAO3.6objectlibrary代码：PublicSub1_12()DimmyDbAsDAO.Database定义database变量DimmyTableAsDAO.TableDef定义tabledef变量DimmyIndexAsDAO.In

54、dex定义index变量DimwsAsWorksheet定义worksheet变量DimiAsLongDimmyDataAsStringmyData=ThisWorkbook.Path&学生成绩管理.mdb指定数据库文件判断是否有保存数据表资料的工作表存在OnErrorResumeNextSetws=Worksheets(数据表设计)OnErrorGoTo0IfwsIsNothingThenMsgBox没有数据表资料存在！,vbCritical,警告ExitSubEndIfws.Activate建立与数据库的连接SetmyDb=OpenDatabase(myData)删除已经存在的数据表OnE

55、rrorResumeNextmyDb.TableDefs.DeleteRange(B1).ValueOnErrorGoTo0创建数据表SetmyTable=myDb.CreateTableDef(Range(B1).Value)创建索引SetmyIndex=myTable.CreateIndex(PrimaryKey)myIndex.Primary=True开始创建数据表字段等Fori=4ToRange(A65536).End(xlUp).RowWithmyTable添加字段.Fields.Append.CreateField(Cells(i,1).Value,_GetConstNo(Cell

56、s(i,2).Value),Cells(i,3).Value)对文本型字段设置是否允许零长度字符串IfCells(i,2).Value=dbTextThenIfCells(i,4).Value=TrueThen.Fields(Cells(i,1).Value).AllowZeroLength=TrueEndIfEndIf设置是否为必填字段IfCells(i,5).Value=TrueThen.Fields(Cells(i,1).Value).Required=TrueElse.Fields(Cells(i,1).Value).Required=FalseEndIf设置索引IfCells(i,6

57、).Value=是ThenmyIndex.Fields.AppendmyIndex.CreateField(Cells(i,1).Value)EndIfEndWithNextI将数据表定义保存到数据库myTable.Indexes.AppendmyIndexmyDb.TableDefs.AppendmyTable弹出信息MsgBox数据表创建成功！,_vbOKOnly+vbInformation,创建数据表关闭数据库myDb.Close设置变量Setws=NothingSetmyIndex=NothingSetmyTable=NothingSetmyDb=NothingEndSub将工作表中定

58、义的字段类型字符串转换为字段类型VBA常量的函数FunctionGetConstNo(myStrAsString)AsIntegerSelectCasemyStrCasedbBoolean:GetConstNo=1CasedbByte:GetConstNo=2CasedbInteger:GetConstNo=3CasedbLong:GetConstNo=4CasedbCurrency:GetConstNo=5CasedbSingle:GetConstNo=6CasedbDouble:GetConstNo=7CasedbDate:GetConstNo=8CasedbBinary:GetConst

59、No=9CasedbText:GetConstNo=10CasedbLongBinary:GetConstNo=11CasedbMemo:GetConstNo=12CasedbGUID:GetConstNo=15CasedbBigInt:GetConstNo=16CasedbVarBinary:GetConstNo=17CasedbChar:GetConstNo=18CasedbNumeric:GetConstNo=19CasedbDecimal:GetConstNo=20CasedbFloat:GetConstNo=21CasedbTime:GetConstNo=22CasedbTimeSt

60、amp:GetConstNo=23CaseElse:GetConstNo=-1EndSelectEndFunction1-13利用已有的数据表创建新数据表（ADO）引用：microsoftactiveXdataobjects2.xlibraryPublicSub1_13()OnErrorGoTohhhDimcnnAsNewADODB.Connection定义connection对象变量DimrsAsNewADODB.Recordset定义recordset对象变量DimmyDataAsString定义数据库名称变量DimmyTableAsString定义新数据表名称变量DimmyOldTabl