ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

excel vba ¬d¸ß Sqlite¸ê®Æ®w(³]©w»P»yªk)½d¨Ò

excel vba ¬d¸ß Sqlite¸ê®Æ®w(³]©w»P»yªk)½d¨Ò

¥»©«³Ì«á¥Ñ joey0415 ©ó 2013-7-31 20:16 ½s¿è

¦Û¤v¸Õ¤F«Ü¤[¡A²×©ó¦¨¥\¤F¡Aºô¸ô¤W¥Hvba¾Þ§@sqliteªº¤å³¹«D±`¤Ö¡A©Ò¥Hµo­Ó¤å¨Ó¬ö¿ý¤@¤U

¤£¯à¶Çªþ¥ó¡Aªþ¤Wdorpbox³sµ²¤U¸ü

sqlite odbc driver­n¸Ë
http://www.ch-werner.de/sqliteodbc/

ªþ¤W½m²ß¸ê®Æ®w»Pexcel vba ÀÉ

https://dl.dropboxusercontent.com/u/40393708/sqlite/sqlite3.zip
  1. Sub select¿é¥X¦Üsheet1¤¤()
  2.     Dim cn, rs, f
  3.    
  4.     Set cn = CreateObject("adodb.connection")
  5.     '¶}±Òsqlite«ü©w¸ê®Æ®w¡A¸ô®|»PÀɮצWºÙ­n¹ï
  6.     cn.Open ("Driver={SQLite3 ODBC Driver};database=E:\joey.sqlite")
  7.     '¼g¥XSQL¬d¸ß»yªk
  8.     Set rs = cn.Execute("SELECT  *  FROM  home ")
  9.     Sheet1.Cells.Delete
  10.     For f = 0 To rs.fields.Count - 1
  11.         Sheet1.Cells(1, f + 1).Value = rs.fields(f).Name
  12.     Next
  13.     Sheet1.Cells(2, 1).CopyFromRecordset (rs)
  14.     cn.Close
  15. End Sub
½Æ»s¥N½X
  1. Sub insert()

  2.     Dim x
  3.     Dim sql_command As String
  4.     Dim connection_string As String
  5.    
  6.         Set x = CreateObject("adodb.recordset")
  7.         sql_command = "insert into home(name, age) values('mary','50') "
  8.         connection_string = ("Driver={SQLite3 ODBC Driver};database=E:\joey.sqlite")
  9.         x.Open sql_command, connection_string
  10.         Set x = Nothing
  11.    
  12. End Sub
½Æ»s¥N½X

¦^´_ 1# joey0415

·Q½Ð°Ý joey0415 ¤j¡A¤p§Ì­è¦n¤]¦b¸Õsqltie3
¥Ø«e¤p§Ì¾ã²z¨Ï¥Îsqltie3 ¡A¾ã²z¸ê®Æ¦p¤U¹Ï¡C
¦]¬°¦Ò¼{¨ì¥¼¨Ó¦pªG­n¦hªí¾ã¦Xªº±¡ªp¡]¥Ø«e¬d¸ßÁÙ¤£¤Ó¼ô½m¡^
©Ò¥H·Q½Ð±Ð¤@¤U~~

A¡Cstock_¨C¤é§ó·s.db¤º¥á¶i 1000~9000¦UªÑ¦¨¬°ªí®æ ¡]³æ¤@ÀÉ®×·|«Ü¤j¡^
B¡C¨CªÑ¥N¸¹¦U¬°.db¡AµM«á¥Îªþ¥[¼Æ¾Úªº¤è¦¡¾É¤Jstocktest.db¡]³æ¤@Àɮפp¡A¦ý·|¤£·|¬d¸ß³Â·Ð¡H¡^

A vs B   ¥¼¨Ó¬d¸ß­þ­Ó¤ñ¸û¤è«K©O¡H
¦]¬°¥Ø«eÁÙ¦bªì´Á³Wµe¡A©Ò¥H·Q¤F¸Ñ¤@¤U joey0415 ¡A¬Ý¤p§Ì¬O§_¯à¸ò¶i~~

TOP

¦^´_ 2# koshi0413

©Ò¦³­ÓªÑªÑ»ù¸ê®Æ©ñ¶i³æ¤@ÀɮסA¨Ò¦pstock.sqlite¤¤¡A¦A³z¹Lselect ¿ï¥X§A­nªºªÑ²¼¸ê®Æ¤~¹ï¡A¦]¬°©ñ¶i¦P¤@­Ó¸ê®Æ¡A¤~¥i¥H¿ï©Ò¦³«ü©w°Ï¶¡ªºªÑ²¼»PªÑ»ùµ¥¡K
¡A³o¼Ë¤£¥Î´X¬í¡A³z¹Lµ{selectªº»yªk¡AÁÙ¥i¥H«ü©w°Ï¶¡ÂàÀɳæ¶g©Î³æ¤ë©Ò¦³ªÑ²¼¶g©Î¤ë¸ê®Æ¡A¤@©w¤£¥Î30¬í¡A§Úªº¸ê®Æ®w¤»¦~¤é¶g¤ë©Ò¦³ªÑ²¼»P¶°«O¡AÀɮפ~800mb
¨S¦³¤°»ò°ÝÃD

TOP

¦^´_ 3# joey0415

¦hÁÂ joey0415 ¤jÄ@·N»¡©ú^^
¤p§Ì´£­ÓºÃ°Ý Stock.db >> ·|¦³ mian >> ¤~¦³ Table
µM«áTable¬O¤ÀÃþ¡AÁÙ¬O¦UªÑ©O¡H

¤p§Ìªº·Qªk¬°¡G
1.Table=¨C¤é¦æ±¡
2.Table=¨C¤é¸ê¨é
3.Table=¨C¤éªk¤H«ùªÑ
4.Table=¨C¤é¨é°Ó¶i¥X
5.Table=¶°«O®w¦s
6.Table=¨C¤ëÀ禬
7.Table=¨C©u°]³ø
8.Table=¨C¦~°]³ø
¦ý Table ¤§¤U¦n¹³µLªk¥]§t­ÓªÑ¡H

©Ò¥H¦³­Ó·Qªk¡A¦pªG§ï¦¨ Stock.db >>  mian >>  Table
mian ¬°ªþ¥[¼Æ¾Ú®w
1. Stock.db ªþ¥[ ¨C¤é¦æ±¡.db >>  Table¬°1000~9000­ÓªÑ
2.Stock.db ªþ¥[ ¨C¤é¸ê¨é.db >>  Table¬°1000~9000­ÓªÑ
3.Stock.db ªþ¥[ ¨C¤éªk¤H«ùªÑ.db >>  Table¬°1000~9000­ÓªÑ
4.Stock.db ªþ¥[ ¨C¤é¨é°Ó¶i¥X.db >>  Table¬°1000~9000­ÓªÑ
5.Stock.db ªþ¥[ ¶°«O®w¦s.db >>  Table¬°1000~9000­ÓªÑ
6.Stock.db ªþ¥[ ¨C¤ëÀ禬.db >>  Table¬°1000~9000­ÓªÑ
7.Stock.db ªþ¥[ ¨C©u°]³ø.db >>  Table¬°1000~9000­ÓªÑ
8.Stock.db ªþ¥[ ¨C¦~°]³ø.db >>  Table¬°1000~9000­ÓªÑ
¤£ª¾¹D³o¼Ë·|¤£·|¦n¬d¸ß©O¡H

ÁÙ¬O±z¬°ªº«¬¦¡¬°¡G¡]³æ¤@Table§Y1000~9000¡^
1.Table=¨C¤é¦æ±¡
2.Table=¨C¤é¸ê¨é
3.Table=¨C¤éªk¤H«ùªÑ
4.Table=¨C¤é¨é°Ó¶i¥X
5.Table=¶°«O®w¦s
6.Table=¨C¤ëÀ禬
7.Table=¨C©u°]³ø
8.Table=¨C¦~°]³ø

ps: ¥Ø«eªk¤H«ùªÑ+¨C¤é¸ê¨é ¤G¦~´Á´N120mb¤F@@

TOP

¦^´_ 1# joey0415


     joey¤j¤j±z¦n,
«D±`·PÁ±zªº¤j¤O¤À¨É
·Q¦V±z½Ð±ÐSqlite´X­Ó°ÝÃD

¤@¡B»PMSsqlªº¤@¯ë¾Þ§@ªk¬Û¦P¶Ü?¨Ò¦p¿é¤J¤é´Á¡B¼Æ¦r¡B¤å¦rªº®É­Ô
¤G¡B»PAccess¤@¼Ë³£¬OÀɮפ覡ªº¸ê®Æ®w,¨ä¸ê®Æ®w¤j¤p¡B³s½u¤H¼Æ¡B³s½uªº°õ¦æ³t«×¡A¤£¾å±o¤j¤j¬O§_ª¾±x?
¤T¡BSqlite¥»¨­­n¦p¦ó³Æ¥÷?
PKKO

TOP

¦^´_ 4# koshi0413

§A¬Ý¹Ï´Nª¾¹D¤F

120mb«Ü¤Ö¡Asqlite®e¶q³Ì¤j¬O2T¡A«Ü°÷¥Î

¸ê®Æ§ì±o¨ì¡A·|¥ÎSQL  »yªk¥i¥H¸Ñªk«Ü¦h°ÝÃD¡A¥¦ªº¹Bºâ¤ñVBA°}¦Cµ¥³£ÁÙ§Ö¤W³\¦h

TOP

¦^´_ 5# PKKO

±z¦n¡I

¤@¡BÀ³¸Ó¤@¼Ë¤~¹ï¡A¸ê®Æ³£¬O¶i§ì¨Ó´N¥ý³B²z¦n(¸ê®Æ®æ¦¡)
¤G¡BACCESS³Ì¤j2G, sqlite 2T¡A³s½u¤H¼Æ§Ú¨S¸Õ¹L¡A³t«×¤@¼Ë§Ö
¤T¡B¥¦¬O³æ¤@ÀɮסAª½±µ½Æ»s¶K¤W§Y¥i¡A®t²§³Æ¥÷¦n±ø¨S¦³

TOP

¦^´_ 6# joey0415

¬Ý¤F joey0415¤jªº¹Ï´N¤F¸Ñ¤F¡A­ì¨Ó¬O¤èªk¤T¡A³o¬O¤p§Ì¤@¶}©lªº§@ªkxd
«¬¦¡¬°¡G¡]³æ¤@Table§Y1000~9000­ÓªÑ¡^
1.Table=¨C¤é¦æ±¡
2.Table=¨C¤é¸ê¨é
3.Table=¨C¤éªk¤H«ùªÑ
4.Table=¨C¤é¨é°Ó¶i¥X
5.Table=¶°«O®w¦s
6.Table=¨C¤ëÀ禬
7.Table=¨C©u°]³ø
8.Table=¨C¦~°]³ø

¤G¬P´Á«e­è±µÄ²sql¡AÁÙ¤£ª¾¹D·j´M»yªk¥i¥Hºâ³o»ò§Ö³t¡A©Ò¥H¤~§ï¬°¦U§OÀÉ®×
³Ìªñ§Q¥Î¤W¯Z®É¶¡±`±`¦b§äsql¸ê®Æ¡A­ì¨Ó¯Á¤Þ¡BÂù¬d¸ß¤]¬O«Ü­«­nªº¡]­è¶}©l¯uªº¤£À´@@¡^
µ²ªG¥»¨Ó¬d¤@µ§¸ê®Æ­n25s¡A¥[¤W¯Á¤Þ«á´NÅÜ3s¤F¡AµM«á¦b§ï¤@¤U´NÅÜ0.08s!!!
©Ò¥H³o¤G¤Ñªº»{¯u«ä¦Ò¡A«ç¼Ëªº¾ã²z¤è¦¡¤ñ¸û¦n¡A¥»¨­¨S¸I¹L³oÃþªºªF¦è¡A»yªk¤]¬Ý±o¨¯­W¡A´N¬O½d¥»¤@ª½¸Õ¤@ª½¸Õ~~
ÁÙ¦n  joey0415¤j ¥i¥HÀ°¦£¸Ñ´b¡A«D±`·PÁ±z:victory:

TOP

¦^´_ 5# PKKO

³Æ¥÷¤p§Ì¥Ø«e¬O¥Î gdrive ¨C¤Ñ¤W¶Ç¡A¥i¥H¦³³Æ¥÷°O¿ý ¤£¹L¥u¦³ 15gb

¥t¥~¦³§Oªº³nÅé¡A¥i¥H³]©w¨C¤Ñ³Æ¥÷¡A¥u¤£¹L³Æ¥÷¥X¨Óªº¬O³æ¤@ÀÉ¥÷

¦pªG­n¦P¨B³Æ¥÷¨ì§Oªº¸ê®Æ§¨orµwºÐ¡A¥i¥H¤Wºô¬d.bat¡A¼g¤@­Ó²³æªº¦P¨BÀɧY¥i¡A³]©w¶}¾÷±Ò°Ê¤§Ãþªº

TOP

¦^´_ 1# joey0415
½Ð±Ð¤@¤U¦³Ãö Sqlite ­n¦p¦ó¥¿½T¦w¸Ë¡H
§Ú¤w¸g¤U¸üÁA¤@¨Ç®M¥ó¦p¹Ï¡G

§Ú°õ¦æ¤F sqliteodbc.exe ¥H¤Î sqliteodbc_w64.exe
¦ý¬O±µ¤U¨Ó­n¦p¦óÀ³¥Î¡H
±q¥¼±µÄ²¹L¥¦ÁٽЦh¦h«ü±Ð¡I

TOP

        ÀR«ä¦Û¦b : ¬O«D·í±Ð¨|¡AÆg¬ü§@ĵ±§¡C
ªð¦^¦Cªí ¤W¤@¥DÃD