ExcelVBA與數據庫Access整合筆記1上課講義

1、Good is good, but better carries it.精益求精，善益求善。ExcelVBA與數據庫Access整合筆記11-1利用DAO創(chuàng)建數據庫和數據表首先建立對DAO對象庫MicrosoftDAO3.6ObjectLibrary的引用.在VBA界面下:工具-引用,選中”MicrosoftDAO3.6ObjectLibrary”代碼:PublicSub1_1()DimmyDbAsDAO.Database定義DAO的Database(數據庫)對象變量DimmyTblAsDAO.TableDef定義DAO的TableDef(數據表)對象變量DimmyDataAsString定義

2、數據庫名稱變量DimmyTableAsString定義數據表名稱變量設置要創(chuàng)建的數據庫名稱（包括完整路徑）myData=ThisWorkbook.Path&“HYPERLINK學生成績管理.mdb學生成績管理.mdb”設置要創(chuàng)建的數據表名稱myTable=”期末成績”刪除已經存在的數據庫文件onerrorresumenextkillmyDataonerrorgoto0創(chuàng)建數據庫SetmyDb=CreateDatabase(myData,dbLangChineseSimplified)創(chuàng)建數據表SetmyTbl=myDb.CreateTableDef(myTable)為創(chuàng)建的數據表添加各個字段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將創(chuàng)建的數據表添加到數據庫的TableDefs集合中myDb.TableDefs.AppendmyTbl關閉數據庫，并釋放變量myDb.CloseSetmyDb=NothingSetmyTbl=Nothing彈出信息MsgBox”創(chuàng)建數據庫成功！”&vbCrLf&“數據庫文件名為：”&myData&vbCrLf&“數據表名稱為：”&myTabl

5、e&vbCrLf&“保存位置：”&ThisWorkbook.Path,vbInformation,”創(chuàng)建數據庫”Endsub注：CreateDatabase方法創(chuàng)建數據庫setmydb=createdatabase(mydata,dblangchinesesimplified)mydb:數據庫類型變量dblangchinesesimplified：表達字符串比較規(guī)則，這里為簡體中文CreateTableDef方法創(chuàng)建數據表SetmyTbl=mydb.Createtabledef(mytable)mytbl:表類型變量mydb:數據庫名mytable:表名補充：創(chuàng)建帶密碼的Access數據庫Se

6、tmydb=createdatabase(mydata,dblangchinesesimplified&“;pwd=12345”)1-2利用ADOX創(chuàng)建數據庫和數據表：引用：microsoftADOExt.2.XforDDLandSecurity代碼：publicsub1_2()dimmycatasnewadox.catalog定義ADOX的Catalog對象變量dimmytblasnewtable定義table對象變量dimmydataasstring定義數據庫名稱變量dimmytableasstring定義數據表名稱變量設置要創(chuàng)建的數據庫名稱（包括完整路徑）mydata=thisworkb

7、ook.path&“學生成績管理.mdb”設置要創(chuàng)建的數據表名稱mytable=”期末成績”刪除已經存在的數據庫文件onerrorresumenextkillmydataonerrorgoto0創(chuàng)建新的數據庫mycat.create”provider=microsoft.jet.oledb.4.0;datasource=”&mydata創(chuàng)建數據表，并添加字段=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將創(chuàng)建的數據表添加到ADOX的Tables集合中mycat.tables.appendmytbl釋放變量setmycat=nothingsetmytbl=nothing彈出信息msgbo

9、x“創(chuàng)建數據庫成功!”&vbcrlf&“數據庫文件名為:”&mydata&vbcrlf&“數據表名稱為:”&mytable&vbcrlf&“保存位置:”&thisworkbook.path,vbinformation,”創(chuàng)建數據庫”endsub注：在VB中，常用的數據訪問接口有下列三種：數據庫訪問對象（DAO，DataAccessobject）、遠程數據庫對象（RDO,RemoteDataObject）和ActiveX數據對象（ADO,ActiveXDataObject）ADOX的常用方法：Append方法：可以創(chuàng)建columns,groups,indexes,keys,procedures,

10、tables,users,views等為數據表添加字段：mytbl.columns.append字段名，數據類型，字段長度將創(chuàng)建的數據表添加到ADOX的Tables集合中的語句是：Mycat.tables.appendmytblCreate方法：創(chuàng)建一個新的數據庫的語句：Mycat.create“provider=Microsoft.jet.oledb.4.0;datasource=”&mydataDelete方法：刪除數據表：Mycat.tables.delete數據表名Refresh方法：用于更新集合中的對象1-3利用SQL語句創(chuàng)建數據庫和數據表首先引用：microsoftactiveXd

11、ataobjects2.Xlibrary和microsoftadoext.2.xforddlandsecurity”代碼：publicsub1_3()dimmycatasnewadox.catalog定義ADOX的Catalog對象變量Dmand定義Command對象變量dimmydataasstring定義數據庫名稱變量dimmytableasstring定義數據表名稱變量dimSQLasstring設置要創(chuàng)建的數據庫名稱（包括完整路徑）mydata=thisworkbook.path&“學生成績管理.mdb”設置要創(chuàng)建的數據表名稱mytable=”期末成績”刪除已經存在的數據庫文件oner

12、rorresumenextkillmydataonerrorgoto0創(chuàng)建數據庫文件mycat.create“provider=microsoft.jet.oledb.4.0;Datasource=”&mydata設置數據庫連接setmycmd.activeconnection=mycat.activeconnection設置創(chuàng)建數據表的SQL語句SQL=CREATETABLE&myTable_&(學號text(10),姓名text(6),性別text(1),班級text(10),_&數學Single,語文Single,物理Single,化學Single,_&英語Single,總分Single

13、)利用execute方法創(chuàng)建數據表mandtext=sql.execute,adcmdtextendwith釋放變量setmycat=nothingsetmycmd=nothing彈出信息msgbox“創(chuàng)建數據庫成功！”&vbcrlf&“數據庫文件名為：”&mydata&vbcrlf&“數據表名稱為：”&mytable&vbcrlf&“保存位置：”&thisworkbook.path,vbinformation,”創(chuàng)建數據庫”endsub注：有兩種方法來創(chuàng)建數據表：利用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在已有的數據庫中創(chuàng)建數據表（DAO）引用DAO對象庫：microsoftDAO3.6objectlibrary代碼：publicsub1_4()dimmydbasdao.database定義DAO的database(數據庫)

15、對象變量dimmydataasstring定義數據庫名稱變量dimmytableasstring定義數據表名稱變量設置數據庫名稱（包括完整路徑）mydata=thisworkbook.path&“學生成績管理.mdb”設置要創(chuàng)建的數據表名稱mytable=”期末成績”打開數據庫setmydb=opendatabase(mydata)刪除數據庫中已經存在的數據表mydb.tabledefs.deletemytable創(chuàng)建新的數據表setmytbl=mydb.createtabledef(mytable)為創(chuàng)建的數據表添加各個字段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將創(chuàng)建的數據表添加到數據庫的TableDefs集合中mydb.tabledefs.appendmytbl關閉數據庫mydb.close釋放變量setmydb=nothingsetmytbl=nothing彈出信息msgbox”數據表創(chuàng)建成功！”,vbinformation,”創(chuàng)建數據表”endsub補充：opendatabase方法用來打開一個已有的數據庫，返回一個數據庫對象，并自動將該數據庫對象加入到數據庫對象集中。setdatabase=wor

18、kspace.opendatabase(databasename,options,read-only,connect)workspace:定義的Workspace類型變量，它表示所使用的工作環(huán)境，將包含新的數據庫對象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在已有的數據庫中創(chuàng)建數據表（ADOX）引用：microsoftADOExt.2.xforddlandsecurity代碼：publicsub1_5()Dimmycatasnewadox.catalog定義ADOX的catalog對象變量dimmytblasnewtable定義table對象變量dimmydataasstring定義數據庫名稱變量dimmytableasstring定義數據表名稱變量設置數據庫名稱（包括完整路徑）mydata=thisworkbook.path&“學生成績管理.mdb”設置要創(chuàng)建的數據表名稱mytable=”期末成績”建立與數據庫的連接mycat.a

20、ctiveconnection=”provider=microsoft.jet.oledb.4.0;”_&“datasource=”&mydata刪除數據庫中已經存在的數據表mycat.table.deletemytable創(chuàng)建數據表，并添加字段=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將創(chuàng)建的數據表添加到ADOX的tables集合中mycat.tables.appendmytbl釋放變量setmycat=nothingsetmytbl=nothing彈出信息msgbox“數據表創(chuàng)建成功！”,vbinformation,”創(chuàng)建數據表”endsub注：Activeconnection

22、屬性用來指示catalog所屬的ADOConnection對象，表示到數據源的打開的連接。1-6在已有的數據庫中創(chuàng)建數據表（SQL，Command對象）引用:microsoftactivexdataobjects2.xlibrary和microsoftadoext.2.xforddlandsecurity代碼：publicsub1_6()dimmycatasnewadox.catalog定義adox的catalog對象變量mand定義command對象變量dimmydataasstring定義數據庫名稱變量dimmytableasstring定義數據表名稱變量dimsqlasstring設置數

23、據庫名稱（包括完整路徑）mydata=thisworkbook.path&“學生成績管理.mdb”設置要創(chuàng)建的數據表名稱mytable=”期末成績”建立與數據庫的連接mycat.activeconnection=”provider=microsoft.jet.oledb.4.0;”_&“datasource=”&mydata刪除數據庫中已經存在的數據表mycat.tables.deletemytable設置數據庫連接setmycmd.activeconnection=mycat.activeconnection設置創(chuàng)建數據表的SQL語句SQL=”CREATETABLE”&mytable_&“(

24、學號text(10),姓名text(6),性別text(1),班級text(10),”_&“數學single,語文single,物理single,化學single,“_&“英語single,總分single)”利用Execute方法創(chuàng)建數據表Wmandtext=sql.execute,adcmdtextEndwith釋放變量Setmycat=nothingSetmycmd=nothing彈出信息Msgbox“數據表創(chuàng)建成功！”,vbinformation,”創(chuàng)建數據表”Endsub1-7在已有的數據庫中創(chuàng)建數據表（SQL，Recordset對象）引用：MicrosoftActiveXDataO

25、bjects2.Xlibrary代碼：publicsub1_7()dimcnnasnewadodb.connection定義connection對象變量dimrsasnewadodb.recordset定義Recordset對象變量dimmydataasstring定義數據庫名稱變量dimmytableasstring定義數據表名稱變量dimsqlasstring設置數據庫名稱(包括完整路徑)mydata=thisworkbook.path&“學生成績管理.mdb”設置要創(chuàng)建的數據表名稱mytable=”期末成績”建立與數據庫的連接vider=”microsoft.jet

26、.oledb.4.0”.openmydataendwith刪除數據庫中已經存在的數據表SQL=”droptable”&mytablesetrs=cnn.execute(sql)設置創(chuàng)建數據表的SQL語句sql=”createtable”&mytable_&“(學號text(10),姓名text(6),性別text(1),班級text(10),”_&“數學single,語文single,物理single,化學single,”_&“英語single,總分single)”利用execute方法創(chuàng)建數據表setrs=cnn.execute(sql)關閉數據庫cnn.close釋放變量setmycat=

27、nothingsetrs=nothingsetcnn=nothing彈出信息msgbox“數據表創(chuàng)建成功!”,vbinformation,”創(chuàng)建數據表”endsub1-8利用Access對象創(chuàng)建數據庫和數據表引用：microsoftaccess9.0|10.0|11.0objectlibrary代碼：PublicSub1_8()DimappAccessAsAccess.Application定義Access應用程序對象變量DimdbsAsObject定義數據庫對象變量DimmyTblAsObject定義數據表對象變量DimmyFldAsVariant定義數據字段對象變量DimmyDataAsS

28、tring定義數據庫名稱變量DimmyTableAsString定義數據表名稱變量設置數據庫名稱(包括完整路徑)myData=ThisWorkbook.Path&學生成績管理.mdbmyTable=期末成績設置要創(chuàng)建的數據表名稱刪除已存在的數據庫OnErrorResumeNextKillmyDataOnErrorGoTo0創(chuàng)建一個新的microsoftaccess引用SetappAccess=NewAccess.Application創(chuàng)建一個新的Access數據庫，并打開appAccess.NewCurrentDatabasemyData設置當前打開的數據庫變量（即返回當前在Access窗體中

29、打開的數據庫）Setdbs=appAccess.CurrentDb創(chuàng)建數據表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創(chuàng)建數據庫成功！&vbCrLf_&數據庫文件名為：&myData&vbCrLf_&數據表名稱為：&myTable&vbCrLf_&保存位置：&ThisWorkbook.Path,_vbOKOnly+vbInformation,創(chuàng)建數據庫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=期末成績設置要創(chuàng)建的數據表名稱刪除已存在的數據庫OnErrorResumeNextKillmyDataOnErrorGoTo0創(chuàng)建一個新的microsoftaccess引用SetappAccess=createobject(

34、“Access.application”)創(chuàng)建一個新的Access數據庫，并打開appAccess.NewCurrentDatabasemyData設置當前打開的數據庫變量（即返回當前在Access窗體中打開的數據庫）Setdbs=appAccess.CurrentDb創(chuàng)建數據表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創(chuàng)建數據庫成功！&vbCrLf_&數據庫文件名為：&myData&vbCrLf_&數據表名稱為：&myTable&vbCrLf_&保存位置：&ThisWorkbook.Path,_vbOKOnly+vbInformation,創(chuàng)建數據庫EndSub1-9利用Access對象在已有的數據庫中創(chuàng)建數據表代碼：與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數據表創(chuàng)建成功！,_vbOKOnly+vbInformation,創(chuàng)建數據表EndSub擴展：appaccess.opencurrentdatabasemydata,”12345”打開帶有密碼的數據表1-10利用工作表數據創(chuàng)建數據表（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、創(chuàng)建數據表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數據表創(chuàng)建成功！,_vbOKOnly+vbInformation,創(chuàng)建數據表關閉連接，并釋放變量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利用工作表數據創(chuàng)建數據表（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生成創(chuàng)建數據表的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)&)創(chuàng)建數據表Setrs=cnn.Execute(SQL)MsgBox數據表創(chuàng)建成功！,_vbOKOnly+vbInformation,創(chuàng)建數據表關閉連接，并釋放變量cnn.CloseSetws=NothingSetrs=NothingSetcnn=NothingEndSub1-12利用工作表數據創(chuàng)建數據表（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創(chuàng)建數據表SetmyTable=myDb.CreateTableDef(Range(B1).Value)創(chuàng)建索引SetmyIndex=myTable.CreateIndex(PrimaryKey)myIndex.Primary=True開始創(chuàng)建數據表字段等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數據表創(chuàng)建成功！,_vbOKOnly+vbInformation,創(chuàng)建數據表關閉數據庫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利用已有的數據表創(chuàng)建新數據表（ADO）引用：microsoftactiveXdataobjects2.xlibraryPublicSub1_13()OnErrorGoTohhhDimcnnAsNewADODB.Connection定義connection對象變量DimrsAsNewADODB.Recordset定義recordset對象變量DimmyDataAsString定義數據庫名稱變量DimmyTableAsString定義新數據表名稱變量DimmyOldTabl