使用ExcelVBA操作MySQL數(shù)據(jù)庫(kù) - (附源代碼地址)

1、原創(chuàng) ExcelVBA操作MySQL目 錄簡(jiǎn)介1教程源代碼下載1ExcelVBA操作MySQL之一（準(zhǔn)備工作）2ExcelVBA操作MySQL之二（鏈接MySQL）4ExcelVBA操作MySQL之三（有關(guān)用戶名和密碼的操作）12ExcelVBA操作MySQL之四（創(chuàng)建數(shù)據(jù)庫(kù)和表）17ExcelVBA操作MySQL之五（添加和導(dǎo)入數(shù)據(jù)）21ExcelVBA操作MySQL之六（查詢記錄）32ExcelVBA操作MySQL之七（從文本文件導(dǎo)入數(shù)據(jù)和數(shù)據(jù)導(dǎo)出至文本文件）44ExcelVBA操作MySQL之八（修改記錄）62ExcelVBA操作MySQL之九（刪除記錄）67ExcelVBA操作MySQ

2、L之十（查詢示例）71ExcelVBA操作MySQL之十一（有關(guān)表的操作）93ExcelVBA操作MySQL之十二（獲取數(shù)據(jù)庫(kù)模式信息）98ExcelVBA操作MySQL之十三（生成數(shù)據(jù)透視表）108ExcelVBA操作MySQL之十四（AppendChunk、 GetChunk存取文本和圖片二進(jìn)制數(shù)據(jù)）113ExcelVBA操作MySQL之十五（使用ADODB.Stream）127簡(jiǎn)介學(xué)習(xí)MySQL過(guò)程中,發(fā)現(xiàn)網(wǎng)上關(guān)于ExcelVBA+MySQL的文章不多?，F(xiàn)在分享ExcelVBA操作MySQL的代碼。歡迎各位網(wǎng)友指正，提供更好的解決方法。從這系列文章可以了解到：l  E

3、xcelVBA操作數(shù)據(jù)庫(kù)的方法l  ADO的用法l  MySQL的SQL語(yǔ)句的用法.教程源代碼下載（點(diǎn)此超鏈接放心下載）.后記：經(jīng)過(guò)兩個(gè)月的斷斷續(xù)續(xù)的寫作和測(cè)試，教程寫到這里已經(jīng)到達(dá)136頁(yè)了，自覺(jué)寫作的沖動(dòng)和靈感突然耗盡。作為MySQL新手和非計(jì)算機(jī)專業(yè)人士，寫教程并非易事。寫作過(guò)程中，筆者需更深入了解有關(guān)知識(shí)，結(jié)果令自己提高不少。寫作是艱苦的腦力工作。筆者白天上班，晚上寫作，時(shí)間精力非常有限；更加重要的是，筆者的技術(shù)水平實(shí)在不敢恭維，且嚴(yán)重缺乏系統(tǒng)寫作的經(jīng)驗(yàn)。教程深度、廣度和條理性欠缺，大伙湊和著看拙作吧，估計(jì)多少有所幫助。代碼均為筆者所寫，并親自試

4、驗(yàn)。希望網(wǎng)友多提意見(jiàn)，幫助筆者提高水平。同時(shí)希望有高手能對(duì)教程作補(bǔ)充修正。有任何建議或指教，可電郵858845818（視情況而定，不保證一定回復(fù)哦。）后由 zouyuejian 于 2012-5-3 22:08 編輯ExcelVBA操作MySQL之一（準(zhǔn)備工作）前段時(shí)間學(xué)習(xí)MySQL,發(fā)現(xiàn)網(wǎng)上關(guān)于VBA+MySQL的文章不多。現(xiàn)在分享ExcelVBA操作MySQL的代碼。歡迎各位網(wǎng)友指正，提供更好的解決方法。從這系列文章可以了解到：l ExcelVBA操作數(shù)據(jù)庫(kù)的方法l ADO的用法l MySQL的SQL語(yǔ)句的用法1. MySQL的簡(jiǎn)介 不多說(shuō)。看維基http:/zh.wikipedia.or

5、g/wiki/MySQL看百度2. 安裝MySQL和myODBC for MySQL。MySQL和myODBC for mysql的下載地址：內(nèi)含MySQL-5.5.12-win32 + myODBC for MySQL+MySQL 5.0中文版官方說(shuō)明。當(dāng)然,可以到官網(wǎng)下載。嫌官網(wǎng)下載麻煩的自己百度谷歌吧。關(guān)于myODBC for mysql的安裝,請(qǐng)看這里關(guān)于MySQL的安裝,請(qǐng)看這里MySQL的安裝的教程網(wǎng)絡(luò)上到處有。實(shí)在嫌麻煩的到此網(wǎng)址下載MySQL和myODBC for MySQL：3. MySQL數(shù)據(jù)庫(kù)圖形化管理工具不習(xí)慣命令行界面的,可以使用Mysql的圖形化工具,如MySQL

6、Workbench、Navicat、SQLLog、EMS.SQL.Manager和HeidiSQL等。個(gè)人習(xí)慣用Navicat,下載地址：4. 引用ADO對(duì)象。引用方法：VBE中"工具"菜單引用選擇"Microsoft Activex Data Objects 2.8 Library"和"Microsoft ActiveX Data Object recordset 2.8 Library"好了,準(zhǔn)備到此完畢?？偨Y(jié)一下本人的軟件環(huán)境：l WinXP with SP3l MySQL 5.5.12l MyODBC-3.51.11-2-wi

7、nl Microsoft Excel 2007（兼容模式）l Microsoft ActiveX Data Object 2.8 Library和Microsoft ActiveX Data Object Recordset 2.8 Library。+-+-+-+-+-+-+-+-+-ExcelVBA操作MySQL之二（鏈接MySQL）毫無(wú)疑問(wèn),操作數(shù)據(jù)庫(kù)的第一步是鏈接。鏈接字符串主要是給出Driver、Server、DataBase、UID、PWD和Option。下面是簡(jiǎn)單的鏈接例子,此處省略了DataBase,根用戶root賬號(hào)的登錄密碼是123456。Public Sub LinkMyS

8、QL() Dim Con As ADODB.Connection Set Con = New ADODB.Connection Con.ConnectionString = "Driver=MySQL ODBC 3.51 Driver;" + _ "Server=localhost;" + _ "UID=root;" + _ "PWD=123456;" + _ "OPTION=3;" Con.Open If Con.State = adStateOpen Then MsgBox "鏈接

9、狀態(tài)：" & Con.State & vbCrLf & "ADO版本：" & Con.Version, vbInformation, "" End If Con.Close: Set Con = NothingEnd Sub*如果想寫得簡(jiǎn)單點(diǎn)，也可以寫成：Public Sub LinkMySQL() Dim Con As ADODB.Connection Set Con = New ADODB.Connection Con.Open "Driver=MySQL ODBC 3.51 Driver;Serv

10、er=localhost;OPTION=3", "root", "123456" If Con.State = adStateOpen Then MsgBox "鏈接狀態(tài)：" & Con.State & vbCrLf & "ADO版本：" & Con.Version, vbInformation, "" End If Con.Close: Set Con = NothingEnd Sub*運(yùn)行的結(jié)果如圖：提示：1) 鏈接成功后,Connection的St

11、ate屬性為adStateOpen（值1）。2) ConnectionString有關(guān)參數(shù)參數(shù)默認(rèn)值注釋userODBC (on Windows)用于鏈接至MySQL的用戶名。serverlocalhostMySQL服務(wù)器的主機(jī)名。database默認(rèn)數(shù)據(jù)庫(kù)。option0指定MyODBC工作方式的選項(xiàng)。參見(jiàn)下面。port3306如果服務(wù)器不是本地主機(jī)將要使用的TCP/IP端口。stmt連接至MySQL時(shí)將要執(zhí)行的語(yǔ)句。password服務(wù)器上用戶賬戶的密碼。socket當(dāng)服務(wù)器是本地主機(jī)是將要連接的Unix套接字文件或Windows命名管道。要想選擇多個(gè)選項(xiàng)，可將它們的值加在一起。例如，將選

12、項(xiàng)設(shè)置為12（48），就能獲得調(diào)試功能，但沒(méi)有信息包限制。其中，UID,用戶名,可以寫作user ID或者user。PWD,用戶名的登錄的密碼,可以寫作PassWord。當(dāng)使用簡(jiǎn)稱時(shí)，等號(hào)后面的字符不加引號(hào)。3) Server數(shù)據(jù)庫(kù)服務(wù)器的計(jì)算機(jī)名字,或者IP。如果本機(jī)是服務(wù)器,設(shè)置為L(zhǎng)ocalhost或。4) OPTION,指定MyODBC工作方式的選項(xiàng)。以下為摘錄文字：在Windows平臺(tái)下,正常情況下,應(yīng)通過(guò)切換連接屏幕上的復(fù)選框選擇選項(xiàng),但也能在選項(xiàng)參量中選擇它們。下述選項(xiàng)是按照它們?cè)贛yODBC連接屏幕上顯示的順序排列的：值描述1客戶端無(wú)法處理,MyODBC返回列的

13、實(shí)際寬度。2客戶端無(wú)法處理,MyODBC返回受影響行的真值。如果設(shè)置了該標(biāo)志,MySQL將返回“發(fā)現(xiàn)的行”取而代之。MySQL的版本必須是3.21.14或更高版本,該功能才能生效。4在c:myodbc.log中生成調(diào)試日志。它與將MYSQL_DEBUG=d:t:O,c:myodbc.log放到AUTOEXEC.BAT中的效果相同（在Unix平臺(tái)下,該文件是/tmp/myodbc.log）。8不為結(jié)果和參數(shù)設(shè)置任何信息報(bào)限制。16即使驅(qū)動(dòng)程序可能會(huì)給出提示,對(duì)出現(xiàn)的問(wèn)題不予提示。32允許動(dòng)態(tài)光標(biāo)支持。（在MyODBC 2.50中不允許）。64在db_name.tbl_name.col_name中

14、忽略數(shù)據(jù)庫(kù)名的使用。128強(qiáng)制使用ODBC管理器光標(biāo)（實(shí)驗(yàn)性）。256禁止使用擴(kuò)展取數(shù)據(jù)（實(shí)驗(yàn)性）。512將CHAR列填充為全列寬。1024SQLDescribeCol()返回完全合格的列名。2048使用壓縮客戶端服務(wù)器協(xié)議。4096通知服務(wù)器忽略函數(shù)名之后和“(”之前的空格（PowerBuilder要求這樣）。這會(huì)使所有的函數(shù)名成為關(guān)鍵字。8192用命名管道鏈接至運(yùn)行在NT環(huán)境下的mysqld服務(wù)器。16384將LONGLONG列更改為INT列（某些應(yīng)用程序不能處理LONGLONG列）。32768從SQLTables返回作為Table_qualifier和Table_owner的用戶（實(shí)驗(yàn)性

15、）。65536從f的client和odbc組讀取參數(shù)。131072增加一些額外檢查（不應(yīng)需要之,但）。262144禁止事務(wù)。524288允許將查詢記錄到c:myodbc.sql(/tmp/myodbc.sql)文件。（僅在調(diào)試模式下才能啟用）。1048576不要驅(qū)動(dòng)中的結(jié)果進(jìn)行緩沖處理,而應(yīng)從服務(wù)器讀取“mysql_use_result()”。僅對(duì)正向光標(biāo)才能起作用。當(dāng)你不希望緩沖處理整個(gè)結(jié)果集時(shí),對(duì)于大表處理,該選項(xiàng)十分重要。2097152強(qiáng)制使用正向光標(biāo)類型。在應(yīng)用程序設(shè)置了默認(rèn)靜態(tài)動(dòng)態(tài)光標(biāo)類型的情況下,如果希望驅(qū)動(dòng)程序使用非緩沖結(jié)果集,那么該選項(xiàng)能夠保證正向光標(biāo)的行為。在下面的表各中,給

16、出了針對(duì)各種配置的推薦選項(xiàng)值：配置選項(xiàng)值Microsoft Access3Microsoft Visual Basic3具有很多行的大表2049驅(qū)動(dòng)跟蹤生成（調(diào)試模式）4查詢?nèi)罩旧桑ㄕ{(diào)試模式）524288生成驅(qū)動(dòng)跟蹤和查詢?nèi)罩荆ㄕ{(diào)試模式）524292具有非緩沖結(jié)果的大表3145731提醒：在配置Connector/ODBC時(shí),Advanced的某些選項(xiàng)等效于設(shè)置OPTION。5) 個(gè)人認(rèn)為其他比較重要的提示。以下為摘錄文字：為什么SELECT COUNT(*) FROM tbl_name返回錯(cuò)誤？這是因?yàn)镃OUNT(*)表達(dá)式返回了1個(gè)BIGINT, ADO不理解這個(gè)大值的含義。選擇“將BI

17、GINT列更改為INT”選項(xiàng)（選項(xiàng)值16384）。MyODBC支持動(dòng)態(tài)光標(biāo)類型嗎？是。MyODBC 3.51支持動(dòng)態(tài)光標(biāo)類型以及正向和靜態(tài)特性。由于性能方面的原因,在默認(rèn)情況下,驅(qū)動(dòng)程序不支持該特性。你可以啟用該特性,方法是,將連接選項(xiàng)標(biāo)志指定為“OPTION=32”,或在DSN配置中選中“啟用動(dòng)態(tài)光標(biāo)”選項(xiàng)。MyODBC的性能為什么很差,對(duì)于相對(duì)較小的查詢也會(huì)導(dǎo)致大量的磁盤動(dòng)作？MyODBC比其他ODBC驅(qū)動(dòng)程序快很多。緩慢可能是因未使用下述選項(xiàng)造成的：打開“ODBC跟蹤”選項(xiàng)。遵循這里給出的指示說(shuō)明,交叉檢查是否未啟用該選項(xiàng)。如上圖所示,“ODBC數(shù)據(jù)源管理器”“跟蹤”選項(xiàng)卡的“何時(shí)跟蹤”

18、選項(xiàng)應(yīng)始終指向“現(xiàn)在開始跟蹤”,而不是“現(xiàn)在停止跟蹤”。使用了驅(qū)動(dòng)程序的調(diào)試版本。如果你正在使用驅(qū)動(dòng)DLL的調(diào)試版本,也會(huì)使查詢處理變慢。你可以執(zhí)行交叉檢查,通過(guò)驅(qū)動(dòng)DLL屬性（在系統(tǒng)目錄下,右擊驅(qū)動(dòng)DLL并點(diǎn)擊“屬性”）的“注釋”區(qū),檢查DLL是否是調(diào)試版或發(fā)布版,如下圖所示：?jiǎn)⒂昧恕膀?qū)動(dòng)跟蹤和查詢?nèi)罩尽?。即使你打算使用?qū)動(dòng)程序的調(diào)試版（在生產(chǎn)環(huán)境下總應(yīng)使用發(fā)布版）,也應(yīng)確保禁止了“驅(qū)動(dòng)跟蹤和查詢?nèi)罩尽边x項(xiàng)（OPTION=4,524288）,如下圖所示：摘錄自：如果想連接字符串了解更多，可以到 。+-+-+-+-+-+-+-+-+-ExcelVBA操作MySQL之三（有關(guān)用戶名和密碼的操作）

19、1. 更改根用戶名和密碼。使用默認(rèn)的root作為用戶名是不太安全的，下面的代碼把root用戶名改為VBA，密碼為excelmysql。Public Sub Changeroot() Dim Con As ADODB.Connection Set Con = New ADODB.Connection Con.ConnectionString = "Driver=MySQL ODBC 3.51 Driver;" + _ "Server=localhost;" + _ "DB=mysql;" + _ "user=root;&quo

20、t; + _ "PassWord=123456;" + _ "OPTION=3;" Con.Open Con.Execute "update user set user='VBA',password=password('excelmysql') where user='root'", , adCmdText '更改mysql數(shù)據(jù)庫(kù)的user列值為root的記錄。 Con.Execute "flush privileges;" '刷新權(quán)限表。 Con.C

21、lose: Set Con = NothingEnd Sub*提示：1) DB 鏈接后進(jìn)入的數(shù)據(jù)庫(kù)名字，也可以寫作Database。2) 直接更改權(quán)限表的方法改變權(quán)限，必須運(yùn)行Flush privileges刷新后才生效。而且，設(shè)置密碼需用password函數(shù)加密。3) Excute的options參數(shù)設(shè)置為adCmdText，指示commandtext的字符串應(yīng)看作命令文本。預(yù)先指定options參數(shù)而不自動(dòng)辨別，有助于加快運(yùn)行。以下摘自ADO參考：Options    可選，長(zhǎng)整型值，指示提供者應(yīng)如何計(jì)算 CommandText 參數(shù)，可為下列值：常量說(shuō)明Ad

22、CmdText指示提供者應(yīng)按命令的文本定義計(jì)算 CommandText。AdCmdTable指示 ADO 應(yīng)生成 SQL 查詢以便從 CommandText 命名的表中返回所有行。AdCmdTableDirect指示提供者應(yīng)從 CommandText 命名的表中返回所有行。AdCmdTable指示提供者應(yīng)按表名計(jì)算 CommandText。AdCmdStoredProc指示提供者應(yīng)按存儲(chǔ)過(guò)程計(jì)算 CommandText。AdCmdUnknown指示 CommandText 參數(shù)中的命令類型未知。adAsyncExecute指示命令應(yīng)該異步執(zhí)行。adAsyncFetch指示對(duì)在 CacheSiz

23、e 屬性指定的初始數(shù)量之后的剩余行使用異步提取。2. 更改密碼。也許原來(lái)的密碼有點(diǎn)長(zhǎng)的，現(xiàn)在把密碼改為“excel“。Public Sub Resetpwd() Dim Con As ADODB.Connection Set Con = New ADODB.Connection Con.ConnectionString = "Driver=MySQL ODBC 3.51 Driver;" + _ "Server=localhost;" + _ "user=VBA;" + _ "PassWord='excelmysql

24、'" + _ "OPTION=3;" Con.Open Con.Execute "set password=password('excel');", , adCmdText Con.Close: Set Con = NothingEnd Sub*提示：1) 用set password語(yǔ)句更改密碼是不需要運(yùn)行Flush privileges的。3. 新建用戶。新建用戶比較好的方法是使用grant。此方法無(wú)需運(yùn)行Flush privileges。下面的代碼新建本地用戶monty，密碼mysql。它的權(quán)限僅僅限于select，

25、即檢索數(shù)據(jù)。Public Sub Newuser() Dim Con As ADODB.Connection Set Con = New ADODB.Connection Con.ConnectionString = "Driver=MySQL ODBC 3.51 Driver;" + _ "Server=localhost;" + _ "user=VBA;" + _ "PassWord='excel'" + _ "OPTION=3;" Con.Open Con.Execute

26、"Grant select on *.* to 'monty''' identified by 'mysql'", , adCmdText Con.Close: Set Con = NothingEnd Sub4. 為幫助理解mysql的訪問(wèn)權(quán)限處理，從MySQL入門到精通（美吉爾費(fèi)倫）摘錄：MySQL如何檢查允許訪問(wèn)的權(quán)限 當(dāng)一個(gè)用戶試圖連接時(shí)，MySQL首先檢查用戶表，以確定所列出的特定用戶、主機(jī)和密碼的組合。如果沒(méi)有，則用戶被拒絕訪問(wèn)。如果用戶試圖直接連接數(shù)據(jù)庫(kù)，即便通過(guò)了其他檢查，db表還是要被檢查的

27、。如果用戶沒(méi)有連接數(shù)據(jù)庫(kù)的權(quán)限，訪問(wèn)將被拒絕。 當(dāng)已完成連接的用戶試圖執(zhí)行管理操作(比如說(shuō)，mysqladmin shutdown)，MySQL會(huì)檢查用戶表中與操作有關(guān)的列。如果所要求的操作被授予了權(quán)限，操作就能繼續(xù)進(jìn)行;如果沒(méi)有，則操作會(huì)失敗。如果已完成連接的用戶試圖執(zhí)行與數(shù)據(jù)庫(kù)有關(guān)的操作（如select, update,等等），MySQL將從用戶表中檢查相關(guān)的字段，如果所要求的操作(select,update等)被授予了權(quán)限，操作就會(huì)被允許。如果沒(méi)有，MySQL就會(huì)進(jìn)人下一個(gè)步驟。 下一步是檢查db表。MySQL查找用戶正在其上執(zhí)行操作的的數(shù)據(jù)庫(kù)。如果這個(gè)數(shù)據(jù)庫(kù)不存在，則許可權(quán)被禁止，操作

28、失敗。如果數(shù)據(jù)庫(kù)存在，主機(jī)和用戶匹配，則與操作有關(guān)的字段會(huì)被檢查。如果所要求的操作被授予了權(quán)限，操作會(huì)成功。如果沒(méi)有被授予權(quán)限，MySQL接著進(jìn)入下一步。如果數(shù)據(jù)庫(kù)和用戶的組合存在，且主機(jī)的字段為空，MySQL就會(huì)檢查主機(jī)表，看看主機(jī)是否能執(zhí)行所要求的操作。如果在主機(jī)表中找到了主機(jī)和數(shù)據(jù)庫(kù)，則在主機(jī)和db表上都相關(guān)的字段決定操作能否成功。如果兩個(gè)表都授予了許可權(quán)，操作就會(huì)成功，如果沒(méi)有，MySQL進(jìn)人下一步。 MySQL檢查tables-priv表，考慮要執(zhí)行操作的表的情況。如果主機(jī)、用戶、db與表的組合不存在，操作就會(huì)失敗。如果存在，就會(huì)檢查相關(guān)的字段。如果權(quán)限沒(méi)有被授予，MySQL進(jìn)入下一

29、步。如果權(quán)限被授予，操作則會(huì)成功。 最后，MYSQL檢查columns_priv表，考慮操作中所用的列的情況。如果所要求的操作被授予了權(quán)限，則操作成功，否則，操作失敗。MySQL許可權(quán)表的優(yōu)先權(quán)順序參見(jiàn)圖（摘錄完）另外兩篇很不錯(cuò)的講解權(quán)限的文章+-+-+-+-+-+-+-+-+-ExcelVBA操作MySQL之四（創(chuàng)建數(shù)據(jù)庫(kù)和表）1) 創(chuàng)建數(shù)據(jù)庫(kù)。下面使用Create Database語(yǔ)句創(chuàng)建名為vbadb的數(shù)據(jù)庫(kù)。Public Sub NewDatabase() Dim Con As ADODB.Connection Set Con = New ADODB.Connection Con.Co

30、nnectionString = "Driver=MySQL ODBC 3.51 Driver;" + _ "Server=localhost;" + _ "user=VBA;" + _ "PassWord='excel'" + _ "OPTION=3;" Con.Open Con.Execute "create database if not exists vbadb;", , adCmdText '加if not exists表示如果不存在vbad

31、b數(shù)據(jù)庫(kù)時(shí)才創(chuàng)建 Con.Close: Set Con = NothingEnd Sub*2) 創(chuàng)建表。下面將創(chuàng)建的表的字段屬性如下：列名類型長(zhǎng)度是否允許空值是否主鍵IDMediumint3否。自動(dòng)遞增數(shù)列。是chnamechar10否否ennamechar30否否sexEnum否?！澳小被颉芭狈馻getinyint1是否countrychar20是否commentsvarchar400是否代碼：Public Sub NewTable() Dim Con As ADODB.Connection Set Con = New ADODB.Connection Con.ConnectionStri

32、ng = "Driver=MySQL ODBC 3.51 Driver;" + _ "Server=localhost;" + _ "DB=vbadb;" + _ "user=VBA;" + _ "PassWord='excel'" + _ "OPTION=3;" + _ "Stmt=Set Names 'GBK'" Con.Open Con.Execute "create table if not exists

33、student (" + _ "ID mediumint(3) NOT NULL AUTO_INCREMENT ," + _ "chname char(10) NOT NULL ," + _ "enname char(30) NOT NULL ," + _ "sex enum('男','女') NOT NULL ," + _ "age tinyint(1) NULL DEFAULT NULL ," + _ "country char(20) N

34、ULL DEFAULT NULL ," + _ "comments varchar(400) NULL DEFAULT NULL ," + _ "PRIMARY KEY (ID) ENGINE=InnoDB;", , adCmdText Con.Close: Set Con = NothingEnd Sub*提示：1) 注意，這次的鏈接字符串多了一句“Stmt=Set Names 'GBK'”。Stmt用來(lái)設(shè)置連接至MySQL時(shí)將要執(zhí)行的語(yǔ)句。而Set Names 'GBK'語(yǔ)句指示鏈接使用的GBK字符集。如果不

35、使用Stmt，也可以在打開鏈接后通過(guò)Excute方法運(yùn)行Set Names 'GBK'，即代碼寫為：Public Sub NewTable() Dim Con As ADODB.Connection Set Con = New ADODB.Connection Con.ConnectionString = "Driver=MySQL ODBC 3.51 Driver;" + _ "Server=localhost;" + _ "DB=vbadb;" + _ "user=VBA;" + _ "

36、;PassWord='excel'" + _ "OPTION=3;" Con.Open Con.Execute "Set Names 'GBK'"'這句和在鏈接字符串加Stmt=Set Names 'GBK'相同。 Con.Execute "create table“End sub*2) 運(yùn)行Set Names 'GBK'的作用是指示使用GBK字符集，以便正常支持中文。不首先指定字符集，則創(chuàng)建表時(shí)不能識(shí)別中文，將報(bào)錯(cuò)：“Column 'sex' h

37、as duplicated value '?' in ENUM”。3) “'”（char(32)）并非單引號(hào)。此符號(hào)由Esc鍵下方的“”輸入。作用是指示在它之間的字符不屬于關(guān)鍵字，而是表名或字段名。4) ENGINE=InnoDB，指定新建表的類型或稱作存儲(chǔ)引擎類型。Mysql5.1支持提供了包括DBD、HEAP、ISAM、MERGE、MyIAS、InnoDB以及Gemeni表類型。其中DBD、InnoDB屬于事務(wù)安全類表，而其他屬于事務(wù)非安全類表。 具體可參考手冊(cè)：+-+-+-+-+-+-+-+-+-ExcelVBA操作MySQL之五（添加和導(dǎo)入數(shù)據(jù)）之前創(chuàng)

38、建了vbadb數(shù)據(jù)庫(kù)和student表?，F(xiàn)在將添加數(shù)據(jù)到student表里。下面分幾種情形介紹。1. 從Excel工作表導(dǎo)入數(shù)據(jù)。需導(dǎo)入的數(shù)據(jù)在工作表import里，如圖：（1） 使用insert語(yǔ)句。為了方便寫insert語(yǔ)句中的指定字段值部分，先自定義函數(shù)JOINFI。此函數(shù)的參數(shù)為arr（連接成字符串的數(shù)組），delimiter（分隔符），quotes（引號(hào)符），比如：JOINFI(Array("a", "b", "c"), "/", "|")將返回字符串|a|/|b|/|c| 。JOIN

39、FI函數(shù)代碼：Public Function JOINFI(arr As Variant, delimiter As String, Optional quotes As String = "") As String Dim i As Integer, el For Each el In arr i = 1 + i If i = 1 Then JOINFI = quotes & el & quotes Else JOINFI = JOINFI & delimiter & quotes & el & quotes End If

40、Next elEnd Function*用insert語(yǔ)句插入記錄的代碼：Public Sub Import() Dim rngCur As Range, Cell As Range, i As Integer Dim sInsert As String, iRowscount As Integer Dim Con As ADODB.Connection With Worksheets("import") Set rngCur = .Range(.Range("a2"), .Range("a2").End(xlDown) '獲

41、取數(shù)據(jù)區(qū)域 End With For Each Cell In rngCur i = 1 + i If i = 1 Then sInsert = "(" + JOINFI(Cell.Offset(0, 1).Resize(1, 6).Value, ",", """") + ")" Else sInsert = sInsert + "," + "(" + JOINFI(Cell.Offset(0, 1).Resize(1, 6).Value, ",

42、", """") + ")" End If Next Cell '用for each 把數(shù)據(jù)添加到SQL命令字符串sInsert sInsert = "insert student(chname,enname,sex,age,country,comments) value" + sInsert '構(gòu)造插入記錄的SQL命令字符串sInsert Set Con = New ADODB.Connection Con.ConnectionString = "Driver=MySQL OD

43、BC 3.51 Driver;" + _ "Server=localhost;" + _ "DB=vbadb;" + _ "user=VBA;" + _ "PassWord='excel'" + _ "OPTION=3;" + _ "Stmt=Set Names 'GBK'" Con.Open Con.Execute sInsert, iRowscount, adCmdText Con.Close: Set Con = Nothing

44、 MsgBox "導(dǎo)入 " & iRowscount & " 行", vbOKOnly, ""End Sub*1) Insert語(yǔ)句的value后用多個(gè)括號(hào)和逗號(hào)分隔多條記錄，一次性插入多行的用法是MySQL特有的語(yǔ)法。這種方法比使用多條Insert語(yǔ)句逐行插入更有效率。2) ID字段設(shè)置為自動(dòng)遞增，插入記錄后自動(dòng)編號(hào)，無(wú)須指定。2. 使用AddNew方法。AddNew直接使用Insert語(yǔ)句效率較差。但代碼看起來(lái)似乎簡(jiǎn)易。用Addnew方法的代碼：Public Sub Addnew_Import() Dim rngC

45、ur As Range, Cell As Range Dim iRowscount As Integer Dim Con As ADODB.Connection Dim Rec As ADODB.Recordset Set Con = New ADODB.Connection Con.ConnectionString = "Driver=MySQL ODBC 3.51 Driver;" + _ "Server=localhost;" + _ "DB=vbadb;" + _ "user=VBA;" + _ "

46、;PassWord='excel'" + _ "OPTION=3;" + _ "Stmt=Set Names 'GBK'" Con.Open With Worksheets("import") Set rngCur = .Range(.Range("a2"), .Range("a2").End(xlDown) '獲取數(shù)據(jù)區(qū)域 End With Set Rec = New ADODB.Recordset Rec.Open "student

47、", Con, adOpenStatic, adLockOptimistic, adCmdTable For Each Cell In rngCur Rec.AddNew _ Array("chname", "enname", "sex", "age", "country", "comments"), _ WorksheetFunction.Transpose(WorksheetFunction.Transpose _ (Cell.Offset(0, 1).Resi

48、ze(1, 6).Value) '用兩次transpose把區(qū)域的內(nèi)容轉(zhuǎn)為一維數(shù)組 iRowscount = 1 + iRowscount Next Cell Rec.Close: Set Rec = Nothing Con.Close: Set Con = Nothing MsgBox "導(dǎo)入 " & iRowscount & " 行", vbOKOnly, ""End Sub*1) AddNew方法第二個(gè)參數(shù)values只接受一維數(shù)組。用兩次transpose可以非常方便地把單元格區(qū)域轉(zhuǎn)為一維數(shù)組。2) A

49、ddNew方法比使用Insert語(yǔ)句速度慢。3. 使用Load Data方法。Load Data是非常有效率的導(dǎo)入數(shù)據(jù)的方法，尤其適合于大量數(shù)據(jù)。使用Load Data 的用戶須擁有 File 權(quán)限。先把上面的數(shù)據(jù)保存到文本文件C:import.txt。運(yùn)行代碼：Sub Saveastxt() Application.DisplayAlerts = False Dim tm As Worksheet Worksheets("import").Activate ActiveWorkbook.SaveAs Filename:="C:import.txt",

50、_ FileFormat:=xlText, _ CreateBackup:=FalseActiveWorkbook.Close savechanges:=True, Filename:="C:import.txt", RouteWorkbook:=FalseEnd Sub或者，手動(dòng)在菜單操作：office按鈕另存為其他格式選擇保存類型為“文本文件（制表符分割）”，文件名為import。Import.txt文件如圖：注意，為了保證正常導(dǎo)入，數(shù)據(jù)源的文本文件編碼須為ANSI。查看文本文件編碼的菜單操作方法：文件另存為，”編碼”的當(dāng)前選項(xiàng)即是文本文件使用的編碼。如果文本文件編碼不

51、是ANSI，可以在另存文本文件時(shí)選擇編碼ANSI后另存，另存的文件將使用ANSI編碼。以后將介紹更多有關(guān)數(shù)據(jù)導(dǎo)出的內(nèi)容?，F(xiàn)在把路徑為C:import.txt的文本文件數(shù)據(jù)導(dǎo)入vbadb數(shù)據(jù)庫(kù)的student表中。代碼如下：Public Sub Load_data_Import() Dim Con As ADODB.Connection Dim iRowscount As Long, sLoaddata As String Set Con = New ADODB.Connection Con.ConnectionString = "Driver=MySQL ODBC 3.51 Driver;" + _ "Server=localhost;" + _ "DB=vbadb;" + _ "UID=VBA;" + _ "PWD=excel;" + _ "OPTION=3;" Con.Open sL