ExcelVBA與數(shù)據(jù)庫(kù)Access整合筆記1上課講義

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

2、數(shù)據(jù)庫(kù)名稱(chēng)變量DimmyTableAsString定義數(shù)據(jù)表名稱(chēng)變量設(shè)置要?jiǎng)?chuàng)建的數(shù)據(jù)庫(kù)名稱(chēng)（包括完整路徑）myData=ThisWorkbook.Path&“HYPERLINK學(xué)生成績(jī)管理.mdb學(xué)生成績(jī)管理.mdb”設(shè)置要?jiǎng)?chuàng)建的數(shù)據(jù)表名稱(chēng)myTable=”期末成績(jī)”刪除已經(jīng)存在的數(shù)據(jù)庫(kù)文件onerrorresumenextkillmyDataonerrorgoto0創(chuàng)建數(shù)據(jù)庫(kù)SetmyDb=CreateDatabase(myData,dbLangChineseSimplified)創(chuàng)建數(shù)據(jù)表SetmyTbl=myDb.CreateTableDef(myTable)為創(chuàng)建的數(shù)據(jù)表添加各個(gè)字段W

3、ithmyTbl.Fields.Append.CreateField(“學(xué)號(hào)”,dbText,10).Fields.Append.CreateField(“姓名”,dbText,6).Fields.Append.CreateField(“性別”,dbText,1).Fields.Append.CreateField(“班級(jí)”,dbText,10).Fields.Append.CreateField(“數(shù)學(xué)”,dbSingle).Fields.Append.CreateField(“語(yǔ)文”,dbSingle).Fields.Append.CreateField(“物理”,dbSingle).Fi

4、elds.Append.CreateField(“化學(xué)”,dbSingle).Fields.Append.CreateField(“英語(yǔ)”,dbSingle).Fields.Append.CreateField(“總分”,dbSingle)EndWith將創(chuàng)建的數(shù)據(jù)表添加到數(shù)據(jù)庫(kù)的TableDefs集合中myDb.TableDefs.AppendmyTbl關(guān)閉數(shù)據(jù)庫(kù)，并釋放變量myDb.CloseSetmyDb=NothingSetmyTbl=Nothing彈出信息MsgBox”創(chuàng)建數(shù)據(jù)庫(kù)成功！”&vbCrLf&“數(shù)據(jù)庫(kù)文件名為：”&myData&vbCrLf&“數(shù)據(jù)表名稱(chēng)為：”&myTabl

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

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

7、ook.path&“學(xué)生成績(jī)管理.mdb”設(shè)置要?jiǎng)?chuàng)建的數(shù)據(jù)表名稱(chēng)mytable=”期末成績(jī)”刪除已經(jīng)存在的數(shù)據(jù)庫(kù)文件onerrorresumenextkillmydataonerrorgoto0創(chuàng)建新的數(shù)據(jù)庫(kù)mycat.create”provider=microsoft.jet.oledb.4.0;datasource=”&mydata創(chuàng)建數(shù)據(jù)表，并添加字段=mytable.columns.append“學(xué)號(hào)”,advarwchar,10.columns.append“姓名”,advarwchar,6.columns.append“性別”,advarwchar,1.

8、columns.append“班級(jí)”,advarwchar,10.columns.append“數(shù)學(xué)”,adSingle.columns.append“語(yǔ)文”,adSingle.columns.append“物理”,adSingle.columns.append“化學(xué)”,adSingle.columns.append“英語(yǔ)”,adSingle.columns.append“總分”,adSingleEndwith將創(chuàng)建的數(shù)據(jù)表添加到ADOX的Tables集合中mycat.tables.appendmytbl釋放變量setmycat=nothingsetmytbl=nothing彈出信息msgbo

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

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

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

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

13、)利用execute方法創(chuàng)建數(shù)據(jù)表mandtext=sql.execute,adcmdtextendwith釋放變量setmycat=nothingsetmycmd=nothing彈出信息msgbox“創(chuàng)建數(shù)據(jù)庫(kù)成功！”&vbcrlf&“數(shù)據(jù)庫(kù)文件名為：”&mydata&vbcrlf&“數(shù)據(jù)表名稱(chēng)為：”&mytable&vbcrlf&“保存位置：”&thisworkbook.path,vbinformation,”創(chuàng)建數(shù)據(jù)庫(kù)”endsub注：有兩種方法來(lái)創(chuàng)建數(shù)據(jù)表：利用ADODB.Command對(duì)象的commandtext屬性和execute方法：mandsetmycmd.activeconn

14、ection=mandtext=SQL.execute,adcmdtextendwith利用ADODB.Connection對(duì)象的execute方法來(lái)生成幾個(gè)記錄集Dimcnnasnewadodb.connectiondimrsasnewadodb.recordsetsetcnn=mycat.activeconnectionsetrs=cnn.execute(sql)1-4在已有的數(shù)據(jù)庫(kù)中創(chuàng)建數(shù)據(jù)表（DAO）引用DAO對(duì)象庫(kù)：microsoftDAO3.6objectlibrary代碼：publicsub1_4()dimmydbasdao.database定義DAO的database(數(shù)據(jù)庫(kù))

15、對(duì)象變量dimmydataasstring定義數(shù)據(jù)庫(kù)名稱(chēng)變量dimmytableasstring定義數(shù)據(jù)表名稱(chēng)變量設(shè)置數(shù)據(jù)庫(kù)名稱(chēng)（包括完整路徑）mydata=thisworkbook.path&“學(xué)生成績(jī)管理.mdb”設(shè)置要?jiǎng)?chuàng)建的數(shù)據(jù)表名稱(chēng)mytable=”期末成績(jī)”打開(kāi)數(shù)據(jù)庫(kù)setmydb=opendatabase(mydata)刪除數(shù)據(jù)庫(kù)中已經(jīng)存在的數(shù)據(jù)表mydb.tabledefs.deletemytable創(chuàng)建新的數(shù)據(jù)表setmytbl=mydb.createtabledef(mytable)為創(chuàng)建的數(shù)據(jù)表添加各個(gè)字段Withmytbl.fields.append.createfiel

16、d(學(xué)號(hào),dbtext,10).fields.append.createfield(姓名,dbtext,6).fields.append.createfield(性別,dbtext,1).fields.append.createfield(班級(jí),dbtext,10).fields.append.createfield(數(shù)學(xué),dbsingle).fields.append.createfield(語(yǔ)文,dbsingle).fields.append.createfield(物理,dbsingle).fields.append.createfield(化學(xué),dbsingle).fields.app

17、end.createfield(英語(yǔ),dbsingle).fields.append.createfield(總分,dbsingle)endwith將創(chuàng)建的數(shù)據(jù)表添加到數(shù)據(jù)庫(kù)的TableDefs集合中mydb.tabledefs.appendmytbl關(guān)閉數(shù)據(jù)庫(kù)mydb.close釋放變量setmydb=nothingsetmytbl=nothing彈出信息msgbox”數(shù)據(jù)表創(chuàng)建成功！”,vbinformation,”創(chuàng)建數(shù)據(jù)表”endsub補(bǔ)充：opendatabase方法用來(lái)打開(kāi)一個(gè)已有的數(shù)據(jù)庫(kù)，返回一個(gè)數(shù)據(jù)庫(kù)對(duì)象，并自動(dòng)將該數(shù)據(jù)庫(kù)對(duì)象加入到數(shù)據(jù)庫(kù)對(duì)象集中。setdatabase=wor

18、kspace.opendatabase(databasename,options,read-only,connect)workspace:定義的Workspace類(lèi)型變量，它表示所使用的工作環(huán)境，將包含新的數(shù)據(jù)庫(kù)對(duì)象databasename:一個(gè)有效的Jet數(shù)據(jù)庫(kù)文件或ODBC數(shù)據(jù)源options:T/F,T表示以獨(dú)占方式打開(kāi)數(shù)據(jù)庫(kù)，而F表示以共享方式打開(kāi)數(shù)據(jù)庫(kù)read-only:是否以只讀方式打開(kāi)數(shù)據(jù)庫(kù)，為T(mén)/Fconnect:說(shuō)明不同連接方式以及密碼擴(kuò)展：利用DAO打開(kāi)有密碼的Access數(shù)據(jù)庫(kù)setmydb=opendatabase(mydata,true,false,”;pwd=123

19、45”)1-5在已有的數(shù)據(jù)庫(kù)中創(chuàng)建數(shù)據(jù)表（ADOX）引用：microsoftADOExt.2.xforddlandsecurity代碼：publicsub1_5()Dimmycatasnewadox.catalog定義ADOX的catalog對(duì)象變量dimmytblasnewtable定義table對(duì)象變量dimmydataasstring定義數(shù)據(jù)庫(kù)名稱(chēng)變量dimmytableasstring定義數(shù)據(jù)表名稱(chēng)變量設(shè)置數(shù)據(jù)庫(kù)名稱(chēng)（包括完整路徑）mydata=thisworkbook.path&“學(xué)生成績(jī)管理.mdb”設(shè)置要?jiǎng)?chuàng)建的數(shù)據(jù)表名稱(chēng)mytable=”期末成績(jī)”建立與數(shù)據(jù)庫(kù)的連接mycat.a

20、ctiveconnection=”provider=microsoft.jet.oledb.4.0;”_&“datasource=”&mydata刪除數(shù)據(jù)庫(kù)中已經(jīng)存在的數(shù)據(jù)表mycat.table.deletemytable創(chuàng)建數(shù)據(jù)表，并添加字段=mytable.columns.append“學(xué)號(hào)”,advarwchar,10.columns.append“姓名”,advarwchar,6.columns.append“性別”,advarwchar,1.columns.append“班級(jí)”,advarwchar,10.columns.append“數(shù)學(xué)”,adsin

21、gle.columns.append“語(yǔ)文”,adsingle.columns.append“物理”,adsingle.columns.append“化學(xué)”,adsingle.columns.append“英語(yǔ)”,adsingle.columns.append“總分”,adsingleendwith將創(chuàng)建的數(shù)據(jù)表添加到ADOX的tables集合中mycat.tables.appendmytbl釋放變量setmycat=nothingsetmytbl=nothing彈出信息msgbox“數(shù)據(jù)表創(chuàng)建成功！”,vbinformation,”創(chuàng)建數(shù)據(jù)表”endsub注：Activeconnection

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

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

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

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

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

27、nothingsetrs=nothingsetcnn=nothing彈出信息msgbox“數(shù)據(jù)表創(chuàng)建成功!”,vbinformation,”創(chuàng)建數(shù)據(jù)表”endsub1-8利用Access對(duì)象創(chuàng)建數(shù)據(jù)庫(kù)和數(shù)據(jù)表引用：microsoftaccess9.0|10.0|11.0objectlibrary代碼：PublicSub1_8()DimappAccessAsAccess.Application定義Access應(yīng)用程序?qū)ο笞兞緿imdbsAsObject定義數(shù)據(jù)庫(kù)對(duì)象變量DimmyTblAsObject定義數(shù)據(jù)表對(duì)象變量DimmyFldAsVariant定義數(shù)據(jù)字段對(duì)象變量DimmyDataAsS

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

29、打開(kāi)的數(shù)據(jù)庫(kù)）Setdbs=appAccess.CurrentDb創(chuàng)建數(shù)據(jù)表SetmyTbl=dbs.CreateTableDef(myTable)為數(shù)據(jù)表添加字段，并用append方法將這些字段添加到Fields集合里SetmyFld=myTbl.CreateField(學(xué)號(hào),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(班級(jí),DB_Text,10)myTbl.Fields.AppendmyFldSetmyFld=myTbl.CreateField(數(shù)學(xué),DB_SINGLE)myTbl.Fields.AppendmyFldSetmyFld=myTbl.CreateField(語(yǔ)文,DB_SINGLE)myTbl.Fields.AppendmyFldSetmyFld=myTbl.CreateField(物理,DB_SINGLE)myTbl.Fields.AppendmyFldSetmyFld=myTbl.CreateField(化學(xué),DB_SINGLE)

31、myTbl.Fields.AppendmyFldSetmyFld=myTbl.CreateField(英語(yǔ),DB_SINGLE)myTbl.Fields.AppendmyFldSetmyFld=myTbl.CreateField(總分,DB_SINGLE)myTbl.Fields.AppendmyFld用append方法將數(shù)據(jù)表添加到tabledefs集合里dbs.TableDefs.AppendmyTbl關(guān)閉Access應(yīng)用程序appAccess.Quit釋放變量SetappAccess=NothingSetdbs=NothingSetmyTbl=NothingSetmyFld=Nothin

32、g彈出信息MsgBox創(chuàng)建數(shù)據(jù)庫(kù)成功！&vbCrLf_&數(shù)據(jù)庫(kù)文件名為：&myData&vbCrLf_&數(shù)據(jù)表名稱(chēng)為：&myTable&vbCrLf_&保存位置：&ThisWorkbook.Path,_vbOKOnly+vbInformation,創(chuàng)建數(shù)據(jù)庫(kù)EndSub1-8-1不引用Access對(duì)象庫(kù)而使用Access的有關(guān)對(duì)象、屬性和方法（先引用access對(duì)象庫(kù)就是前綁定）引用：microsoftaccess9.0|10.0|11.0objectlibrary代碼：與1-8不同處用顏色標(biāo)出PublicSub1_8_1()DimappAccessAsobject定義Access應(yīng)用程序?qū)ο?/p>

33、變量DimdbsAsObject定義數(shù)據(jù)庫(kù)對(duì)象變量DimmyTblAsObject定義數(shù)據(jù)表對(duì)象變量DimmyFldAsVariant定義數(shù)據(jù)字段對(duì)象變量DimmyDataAsString定義數(shù)據(jù)庫(kù)名稱(chēng)變量DimmyTableAsString定義數(shù)據(jù)表名稱(chēng)變量設(shè)置數(shù)據(jù)庫(kù)名稱(chēng)(包括完整路徑)myData=ThisWorkbook.Path&學(xué)生成績(jī)管理.mdbmyTable=期末成績(jī)?cè)O(shè)置要?jiǎng)?chuàng)建的數(shù)據(jù)表名稱(chēng)刪除已存在的數(shù)據(jù)庫(kù)OnErrorResumeNextKillmyDataOnErrorGoTo0創(chuàng)建一個(gè)新的microsoftaccess引用SetappAccess=createobject(

34、“Access.application”)創(chuàng)建一個(gè)新的Access數(shù)據(jù)庫(kù)，并打開(kāi)appAccess.NewCurrentDatabasemyData設(shè)置當(dāng)前打開(kāi)的數(shù)據(jù)庫(kù)變量（即返回當(dāng)前在Access窗體中打開(kāi)的數(shù)據(jù)庫(kù)）Setdbs=appAccess.CurrentDb創(chuàng)建數(shù)據(jù)表SetmyTbl=dbs.CreateTableDef(myTable)為數(shù)據(jù)表添加字段，并用append方法將這些字段添加到Fields集合里SetmyFld=myTbl.CreateField(學(xué)號(hào),10,10)myTbl.Fields.AppendmyFldSetmyFld=myTbl.CreateField(姓

35、名,10,6)myTbl.Fields.AppendmyFldSetmyFld=myTbl.CreateField(性別,10,1)myTbl.Fields.AppendmyFldSetmyFld=myTbl.CreateField(班級(jí),10,10)myTbl.Fields.AppendmyFldSetmyFld=myTbl.CreateField(數(shù)學(xué),6)myTbl.Fields.AppendmyFldSetmyFld=myTbl.CreateField(語(yǔ)文,6)myTbl.Fields.AppendmyFldSetmyFld=myTbl.CreateField(物理,6)myTbl.

36、Fields.AppendmyFldSetmyFld=myTbl.CreateField(化學(xué),6)myTbl.Fields.AppendmyFldSetmyFld=myTbl.CreateField(英語(yǔ),6)myTbl.Fields.AppendmyFldSetmyFld=myTbl.CreateField(總分,6)myTbl.Fields.AppendmyFld用append方法將數(shù)據(jù)表添加到tabledefs集合里dbs.TableDefs.AppendmyTbl關(guān)閉Access應(yīng)用程序appAccess.Quit釋放變量SetappAccess=NothingSetdbs=Noth

37、ingSetmyTbl=NothingSetmyFld=Nothing彈出信息MsgBox創(chuàng)建數(shù)據(jù)庫(kù)成功！&vbCrLf_&數(shù)據(jù)庫(kù)文件名為：&myData&vbCrLf_&數(shù)據(jù)表名稱(chēng)為：&myTable&vbCrLf_&保存位置：&ThisWorkbook.Path,_vbOKOnly+vbInformation,創(chuàng)建數(shù)據(jù)庫(kù)EndSub1-9利用Access對(duì)象在已有的數(shù)據(jù)庫(kù)中創(chuàng)建數(shù)據(jù)表代碼：與1-8不同處加注釋?zhuān)渌嗤琍ublicSub1_9()DimappAccessAsAccess.ApplicationDimdbsAsObjectDimmyTblAsObjectDimmyFldAsV

38、ariantDimmyDataAsStringDimmyTableAsStringmyData=ThisWorkbook.Path&學(xué)生成績(jī)管理.mdbmyTable=期末成績(jī)SetappAccess=NewAccess.Application打開(kāi)一個(gè)現(xiàn)有的Access數(shù)據(jù)庫(kù)appAccess.OpenCurrentDatabasemyDataSetdbs=appAccess.CurrentDb刪除數(shù)據(jù)庫(kù)中已經(jīng)存在的同名數(shù)據(jù)表dbs.TableDefs.DeletemyTableSetmyTbl=dbs.CreateTableDef(myTable)SetmyFld=myTbl.CreateF

39、ield(學(xué)號(hào),DB_Text,10)myTbl.Fields.AppendmyFldSetmyFld=myTbl.CreateField(姓名,DB_Text,6)myTbl.Fields.AppendmyFldSetmyFld=myTbl.CreateField(性別,DB_Text,1)myTbl.Fields.AppendmyFldSetmyFld=myTbl.CreateField(班級(jí),DB_Text,10)myTbl.Fields.AppendmyFldSetmyFld=myTbl.CreateField(數(shù)學(xué),DB_SINGLE)myTbl.Fields.AppendmyFld

40、SetmyFld=myTbl.CreateField(語(yǔ)文,DB_SINGLE)myTbl.Fields.AppendmyFldSetmyFld=myTbl.CreateField(物理,DB_SINGLE)myTbl.Fields.AppendmyFldSetmyFld=myTbl.CreateField(化學(xué),DB_SINGLE)myTbl.Fields.AppendmyFldSetmyFld=myTbl.CreateField(英語(yǔ),DB_SINGLE)myTbl.Fields.AppendmyFldSetmyFld=myTbl.CreateField(總分,DB_SINGLE)myTb

41、l.Fields.AppendmyFlddbs.TableDefs.AppendmyTblappAccess.QuitSetappAccess=NothingSetdbs=NothingSetmyTbl=NothingSetmyFld=NothingMsgBox數(shù)據(jù)表創(chuàng)建成功！,_vbOKOnly+vbInformation,創(chuàng)建數(shù)據(jù)表EndSub擴(kuò)展：appaccess.opencurrentdatabasemydata,”12345”打開(kāi)帶有密碼的數(shù)據(jù)表1-10利用工作表數(shù)據(jù)創(chuàng)建數(shù)據(jù)表（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&學(xué)生成績(jī)管理.mdb指定數(shù)據(jù)文件判斷是否有保存數(shù)據(jù)表資料的工作表存在OnErrorResumeNextSetws=Worksheets(數(shù)據(jù)表設(shè)計(jì)

43、)OnErrorGoTo0IfwsIsNothingThenMsgBox沒(méi)有數(shù)據(jù)表資料存在！,vbCritical,警告ExitSubEndIfws.Activate建立與數(shù)據(jù)庫(kù)的連接myCat.ActiveConnection=provider=microsoft.jet.oledb.4.0;_&datasource=&myData刪除已經(jīng)存在的數(shù)據(jù)表OnErrorResumeNextmyCat.Tables.Deletews.Range(B1).ValueOnErrorGoTo0建立索引myIdx.Name=PrimaryKeymyIdx.PrimaryKey=True開(kāi)始根據(jù)工作表的數(shù)據(jù)

44、創(chuàng)建數(shù)據(jù)表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將表定義進(jìn)行保存myCat.Tables.AppendmyTablemyTable.Indexes.AppendmyIdx彈出信息MsgBox數(shù)據(jù)表創(chuàng)建成功！,_vbOKOnly+vbInformation,創(chuàng)建數(shù)據(jù)表關(guān)閉連接，并釋放變量Setws=NothingSetmyIdx=NothingSetmyTable=NothingSetmyCat=NothingEndSub將工作表中定義的數(shù)據(jù)類(lèi)型（字符串型）轉(zhuǎn)換為字段類(lèi)型VBA常量，即編制一個(gè)自定義

46、函數(shù)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利用工作表數(shù)據(jù)創(chuàng)建數(shù)據(jù)表（ADO+SQL）引用：microsoftactiveXdataobjects2.Xlibrary代碼：PublicSub1_11()DimcnnAsNewADODB.Connection定義connection對(duì)象變量DimrsAsNewADODB.Recordset定義recordset對(duì)象變量DimwsAsWorksheet定義worksheet對(duì)象變量DimiAsLongDimmyDataAsString,SQLAsStringmyData=ThisWorkbook.Path&學(xué)生成績(jī)管理.mdb指定數(shù)據(jù)庫(kù)文件判斷是否有保存

51、數(shù)據(jù)表資料的工作表存在OnErrorResumeNextSetws=Worksheets(數(shù)據(jù)表設(shè)計(jì))OnErrorGoTo0IfwsIsNothingThenMsgBox沒(méi)有數(shù)據(jù)表資料存在！,vbCritical,警告ExitSubEndIfws.Activate建立與數(shù)據(jù)庫(kù)的連接Setcnn=NewADODB.ConnectionWithcnn.Provider=microsoft.jet.oledb.4.0.OpenmyDataEndWith刪除已經(jīng)存在的數(shù)據(jù)表OnErrorResumeNextSQL=droptable&ws.Range(B1).ValueSetrs=cnn.Execu

52、te(SQL)OnErrorGoTo0生成創(chuàng)建數(shù)據(jù)表的SQL語(yǔ)句字符串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)建數(shù)據(jù)表Setrs=cnn.Execute(SQL)MsgBox數(shù)據(jù)表創(chuàng)建成功！,_vbOKOnly+vbInformation,創(chuàng)建數(shù)據(jù)表關(guān)閉連接，并釋放變量cnn.CloseSetws=NothingSetrs=NothingSetcnn=NothingEndSub1-12利用工作表數(shù)據(jù)創(chuàng)建數(shù)據(jù)表（DAO）引用：microsoftDAO3.6objectlibrary代碼：PublicSub1_12()DimmyDbAsDAO.Database定義database變量DimmyTableAsDAO.TableDef定義tabledef變量DimmyIndexAsDAO.In

54、dex定義index變量DimwsAsWorksheet定義worksheet變量DimiAsLongDimmyDataAsStringmyData=ThisWorkbook.Path&學(xué)生成績(jī)管理.mdb指定數(shù)據(jù)庫(kù)文件判斷是否有保存數(shù)據(jù)表資料的工作表存在OnErrorResumeNextSetws=Worksheets(數(shù)據(jù)表設(shè)計(jì))OnErrorGoTo0IfwsIsNothingThenMsgBox沒(méi)有數(shù)據(jù)表資料存在！,vbCritical,警告ExitSubEndIfws.Activate建立與數(shù)據(jù)庫(kù)的連接SetmyDb=OpenDatabase(myData)刪除已經(jīng)存在的數(shù)據(jù)表OnE

55、rrorResumeNextmyDb.TableDefs.DeleteRange(B1).ValueOnErrorGoTo0創(chuàng)建數(shù)據(jù)表SetmyTable=myDb.CreateTableDef(Range(B1).Value)創(chuàng)建索引SetmyIndex=myTable.CreateIndex(PrimaryKey)myIndex.Primary=True開(kāi)始創(chuàng)建數(shù)據(jù)表字段等Fori=4ToRange(A65536).End(xlUp).RowWithmyTable添加字段.Fields.Append.CreateField(Cells(i,1).Value,_GetConstNo(Cell

56、s(i,2).Value),Cells(i,3).Value)對(duì)文本型字段設(shè)置是否允許零長(zhǎng)度字符串IfCells(i,2).Value=dbTextThenIfCells(i,4).Value=TrueThen.Fields(Cells(i,1).Value).AllowZeroLength=TrueEndIfEndIf設(shè)置是否為必填字段IfCells(i,5).Value=TrueThen.Fields(Cells(i,1).Value).Required=TrueElse.Fields(Cells(i,1).Value).Required=FalseEndIf設(shè)置索引IfCells(i,6

57、).Value=是ThenmyIndex.Fields.AppendmyIndex.CreateField(Cells(i,1).Value)EndIfEndWithNextI將數(shù)據(jù)表定義保存到數(shù)據(jù)庫(kù)myTable.Indexes.AppendmyIndexmyDb.TableDefs.AppendmyTable彈出信息MsgBox數(shù)據(jù)表創(chuàng)建成功！,_vbOKOnly+vbInformation,創(chuàng)建數(shù)據(jù)表關(guān)閉數(shù)據(jù)庫(kù)myDb.Close設(shè)置變量Setws=NothingSetmyIndex=NothingSetmyTable=NothingSetmyDb=NothingEndSub將工作表中定

58、義的字段類(lèi)型字符串轉(zhuǎn)換為字段類(lèi)型VBA常量的函數(shù)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利用已有的數(shù)據(jù)表創(chuàng)建新數(shù)據(jù)表（ADO）引用：microsoftactiveXdataobjects2.xlibraryPublicSub1_13()OnErrorGoTohhhDimcnnAsNewADODB.Connection定義connection對(duì)象變量DimrsAsNewADODB.Recordset定義recordset對(duì)象變量DimmyDataAsString定義數(shù)據(jù)庫(kù)名稱(chēng)變量DimmyTableAsString定義新數(shù)據(jù)表名稱(chēng)變量DimmyOldTabl