excel vba 查詢 Sqlite資料庫(設定與語法)範例

joey0415

自己試了很久，終於成功了，網路上以vba操作sqlite的文章非常少，所以發個文來紀錄一下

不能傳附件，附上dorpbox連結下載

sqlite odbc driver要裝
http://www.ch-werner.de/sqliteodbc/

附上練習資料庫與excel vba 檔

https://dl.dropboxusercontent.com/u/40393708/sqlite/sqlite3.zip

1. Sub select輸出至sheet1中()
   
2.     Dim cn, rs, f
   
3.    
   
4.     Set cn = CreateObject("adodb.connection")
   
5.     '開啟sqlite指定資料庫，路徑與檔案名稱要對
   
6.     cn.Open ("Driver={SQLite3 ODBC Driver};database=E:\joey.sqlite")
   
7.     '寫出SQL查詢語法
   
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

複製代碼

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

複製代碼

koshi0413

回復 1# joey0415

想請問 joey0415 大，小弟剛好也在試sqltie3
目前小弟整理使用sqltie3 ，整理資料如下圖。
因為考慮到未來如果要多表整合的情況（目前查詢還不太熟練）
所以想請教一下~~

A。stock_每日更新.db內丟進 1000~9000各股成為表格 （單一檔案會很大）
B。每股代號各為.db，然後用附加數據的方式導入stocktest.db（單一檔案小，但會不會查詢麻煩？）

A vs B   未來查詢哪個比較方便呢？
因為目前還在初期規畫，所以想了解一下 joey0415 ，看小弟是否能跟進~~

下載 (44.03 KB)
第一張

2016-9-26 19:26

下載 (145.6 KB)
第二張

2016-9-26 19:26

joey0415

回復 2# koshi0413

所有個股股價資料放進單一檔案，例如stock.sqlite中，再透過select 選出你要的股票資料才對，因為放進同一個資料，才可以選所有指定區間的股票與股價等…
，這樣不用幾秒，透過程select的語法，還可以指定區間轉檔單週或單月所有股票週或月資料，一定不用30秒，我的資料庫六年日週月所有股票與集保，檔案才800mb
沒有什麼問題

koshi0413

回復 3# joey0415

多謝 joey0415 大願意說明^^
小弟提個疑問 Stock.db >> 會有 mian >> 才有 Table
然後Table是分類，還是各股呢？

小弟的想法為：
1.Table=每日行情
2.Table=每日資券
3.Table=每日法人持股
4.Table=每日券商進出
5.Table=集保庫存
6.Table=每月營收
7.Table=每季財報
8.Table=每年財報
但 Table 之下好像無法包含個股？

所以有個想法，如果改成 Stock.db >>  mian >>  Table
mian 為附加數據庫
1. Stock.db 附加 每日行情.db >>  Table為1000~9000個股
2.Stock.db 附加 每日資券.db >>  Table為1000~9000個股
3.Stock.db 附加 每日法人持股.db >>  Table為1000~9000個股
4.Stock.db 附加 每日券商進出.db >>  Table為1000~9000個股
5.Stock.db 附加 集保庫存.db >>  Table為1000~9000個股
6.Stock.db 附加 每月營收.db >>  Table為1000~9000個股
7.Stock.db 附加 每季財報.db >>  Table為1000~9000個股
8.Stock.db 附加 每年財報.db >>  Table為1000~9000個股
不知道這樣會不會好查詢呢？

還是您為的型式為：（單一Table即1000~9000）
1.Table=每日行情
2.Table=每日資券
3.Table=每日法人持股
4.Table=每日券商進出
5.Table=集保庫存
6.Table=每月營收
7.Table=每季財報
8.Table=每年財報

ps: 目前法人持股+每日資券 二年期就120mb了@@

PKKO

回復 1# joey0415


     joey大大您好,
非常感謝您的大力分享
想向您請教Sqlite幾個問題

一、與MSsql的一般操作法相同嗎?例如輸入日期、數字、文字的時候
二、與Access一樣都是檔案方式的資料庫,其資料庫大小、連線人數、連線的執行速度，不曉得大大是否知悉?
三、Sqlite本身要如何備份?

joey0415

回復 4# koshi0413

你看圖就知道了

120mb很少，sqlite容量最大是2T，很夠用

資料抓得到，會用SQL  語法可以解法很多問題，它的運算比VBA陣列等都還快上許多

下載 (131.76 KB)

2016-9-26 21:41

joey0415

回復 5# PKKO

您好！

一、應該一樣才對，資料都是進抓來就先處理好(資料格式)
二、ACCESS最大2G, sqlite 2T，連線人數我沒試過，速度一樣快
三、它是單一檔案，直接複製貼上即可，差異備份好條沒有

koshi0413

回復 6# joey0415

看了 joey0415大的圖就了解了，原來是方法三，這是小弟一開始的作法xd
型式為：（單一Table即1000~9000個股）
1.Table=每日行情
2.Table=每日資券
3.Table=每日法人持股
4.Table=每日券商進出
5.Table=集保庫存
6.Table=每月營收
7.Table=每季財報
8.Table=每年財報

二星期前剛接觸sql，還不知道搜尋語法可以算這麼快速，所以才改為各別檔案
最近利用上班時間常常在找sql資料，原來索引、雙查詢也是很重要的（剛開始真的不懂@@）
結果本來查一筆資料要25s，加上索引後就變3s了，然後在改一下就變0.08s!!!
所以這二天的認真思考，怎樣的整理方式比較好，本身沒碰過這類的東西，語法也看得辛苦，就是範本一直試一直試~~
還好  joey0415大 可以幫忙解惑，非常感謝您:victory:

koshi0413

回復 5# PKKO

備份小弟目前是用 gdrive 每天上傳，可以有備份記錄 不過只有 15gb

另外有別的軟體，可以設定每天備份，只不過備份出來的是單一檔份

如果要同步備份到別的資料夾or硬碟，可以上網查.bat，寫一個簡單的同步檔即可，設定開機啟動之類的

c_c_lai

回復 1# joey0415
請教一下有關 Sqlite 要如何正確安裝？
我已經下載瞭一些套件如圖：

下載 (45.55 KB)

2016-9-27 07:54

我執行了 sqliteodbc.exe 以及 sqliteodbc_w64.exe
但是接下來要如何應用？
從未接觸過它還請多多指教！