2021-01-21

透過SQLite ODBC Driver讓OpenOffice / LibreOffice 存取 SQLite的資料

  • 先確認已經有OpenOffice / LibreOffice 的工作環境
  • 是否已經有管理SQLite資料檔的工具?
  • 下載取得SQLite ODBC Driver
    • http://ch-werner.de/sqliteodbc/ 
    • driver提供的網頁Christian's Homepage這樣說:

      The SQLite Database Engine provides a lightweight C library to access database files using a large subset of SQL92 without the overhead of RDBMS server processes. In order to use that functionality as a desktop database I wrapped the SQLite library into an ODBC driver.

      So far it has been tested with SQLite 2.8.17 and SQLite 3.32.3 on Windows (>= NT/2000) in MS Excel 97 (MSQUERY) with SQLite database files created on Linux. Support for Win64 exists for both SQLite 2.x and SQLite 3.x versions of the driver. It also runs on Linux with unixODBC 2.[0-2].x and with libiodbc 3.[05].x.

      For Win32 operating systems a binary package is available as an NSIS installer in sqliteodbc.exe. It was made with SQLite 2.8.17/3.32.3 and a MinGW cross compiler, and contains the driver DLLs and programs for installation and uninstallation of the ODBC driver.

      For Win64 operating systems a binary package is available as an NSIS installer in sqliteodbc_w64.exe. It was made with SQLite 3.32.3 and a MinGW cross compiler, and contains the driver DLLs and programs for installation and uninstallation of the ODBC driver. Note that this is a 64 bit only driver. If you're using 32 bit software on Win64 you should install the Win32 driver, too. 

  • 取得 sqliteodbc.exe (32位元) /或/ sqliteodbc_w64.exe (64位元),並安裝:
    • OpenOffice / LibreOffice 的安裝程式沒有區分 32/64位元(那就是32位元),如果沒有OpenOffice / LibreOffice 以外的ODBC連接需求,可以只安裝sqliteodbc 32位元版本,如果有32/64位元的需求,就兩個版本都安裝。。

    • sqlodbc.exe / sqliteodbc_w64_dl.exe 的安裝幾乎一樣,以下用64位元版本說明。
      (EXPERIMENTAL: variant which needs System.Data.SQLite.dll or sqlite3.dll in the Windows system folder)




  • 使用Win 10的搜尋功能,搜尋ODBC(或:更精準搜尋ODBC資料來源),準備建立SQLite資料檔的ODBC連線設定,透過DSN(Data Source Name)ODBC連線設定,提供給相關程式存取時使用,如:OpenOffice / LibreOffice ...


    • ODBC資料來源管理員(32位元)
      分頁:使用者資料來源名稱→新增(D)→建立新資料來源→選:SQLite3 ODBC  Driver 

    • 設定DSN:
      假設要連結的SQLite資料庫檔案在桌面的chinook/chinook.db
      Data Source Name : 可依據需求設定,這裡設為chinook
      Database Name : 用Browse選取chinookdb檔案。
    • 完成設定


  • 使用Base,建立資料庫連線
    • 連線到現有資料庫:選取ODBC
    • 設定ODBC連線:選取設定OK的DSN。例:Chinook


    • 設定使用者認證:不需輸入


    • 完成設定:
      註冊資料庫:註冊後,在Calc, Writer, ... 等,也可以使用。







    • 在Calc查看、使用SQLite檔案的資料
      檢視(V)→資料來源(D)   .... (F4)