如何从 VBA 访问 SQLite？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1346979/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I access SQLite from VBA?
提问by user161259
I have an Excel workbook that has some adodb code that queries a local access database. I want to do the same for SQLite as I believe that will provide better performance. How do I do that? Can I connect to an SQLite file using adodb or odbc?
我有一个 Excel 工作簿，其中包含一些查询本地访问数据库的 adodb 代码。我想对 SQLite 做同样的事情，因为我相信这会提供更好的性能。我怎么做？我可以使用 adodb 或 odbc 连接到 SQLite 文件吗？
I need something simple that I can deploy so that if i can minimize unnecessary configuration and installation maybe peoople that will be using the excel won't have admin rights on the pc so they can't install software.
我需要一些简单的东西，我可以部署，这样如果我可以最大限度地减少不必要的配置和安装，那么使用 excel 的人可能不会在电脑上拥有管理员权限，因此他们无法安装软件。
回答by Govert
Facing the same question, I made a lightweight library to give direct access to SQLite3 from Excel VBA. The end result is a much simpler solution, with no intervening ODBC or OleDb/ADO layer, and the performance reflects the SQLite database performance and not that of the opaque wrapper. It's also nice because you need no registration of a COM component in the registry, you just copy two .dlls with your workbook and add a .bas module into your project.
面对同样的问题，我做了一个轻量级的库，可以从 Excel VBA 直接访问 SQLite3。最终结果是一个更简单的解决方案，没有中间的 ODBC 或 OleDb/ADO 层，性能反映了 SQLite 数据库的性能，而不是不透明包装器的性能。这也很好，因为您不需要在注册表中注册 COM 组件，您只需将两个 .dll 与您的工作簿一起复制，并将一个 .bas 模块添加到您的项目中。
A disadvantage of this approach is that the API is not the standard DAO or ADO interface, so you might need to make some wrappers, or convert some of your code to make it work. That also means you need some familiarity with the SQLite API to use it, but the SQLite documentation is very clear.
这种方法的一个缺点是 API 不是标准的 DAO 或 ADO 接口，因此您可能需要制作一些包装器，或者转换一些代码以使其工作。这也意味着您需要熟悉 SQLite API 才能使用它，但 SQLite 文档非常清楚。
I have put an early version of the project on CodePlex: SQLite for Excelprovides a high-performance path to the SQLite3 API functions, preserving the semantics of the SQLite3 library calls and allowing access to the distributed SQLite3.dll without recompilation.
我已将项目的早期版本放在 CodePlex 上：SQLite for Excel提供了一条通往 SQLite3 API 函数的高性能路径，保留了 SQLite3 库调用的语义，并允许访问分布式 SQLite3.dll 而无需重新编译。
Any feedback would be much appreciated.
任何反馈将不胜感激。
Update:The SQLite for Excelproject now lives on GitHub.
更新：在Excel的SQLite的项目现在住在GitHub上。
回答by luke
How to access sqlite from vba macro from here (original article in chinese):
Software environment:
1) Win7 (32 bit) 2) Excel2007 (with VBA function)
Steps:
1) First, http://www.zsplat.pwp.blueyonder.co.uk/programming/sqlite-3.5.7-odbc-0.65.zipdownload and install the SQLite ODBC driver.
Note: If you use Win7, then you need to Adminitrator permissions, otherwise the installation fails. The simplest step is to use the Administrator login, and then install the SQLite ODBC driver.
2) Open the Excel VBA code editor window, the menu bar [tool] -> [reference], adding Microsoft ActiveX Data Objects 2.7, the purpose is to use VBA, and database connectivity.
3) Use the following code to connect SQLite database:
软件环境：
1）Win7（32位） 2）Excel2007（带VBA功能）
脚步：
1）首先， http://www.zsplat.pwp.blueyonder.co.uk/programming/sqlite-3.5.7-odbc-0.65.zip下载并安装SQLite ODBC驱动。
注意：如果使用Win7，那么需要Adminitrator权限，否则安装失败。最简单的步骤是使用管理员登录，然后安装 SQLite ODBC 驱动程序。
2）打开Excel VBA代码编辑器窗口，菜单栏【工具】->【参考】，添加Microsoft ActiveX Data Objects 2.7，目的是使用VBA，和数据库连接。
3）使用以下代码连接SQLite数据库：
Dim conn As New ADODB.Connection
Dim dbName As String
'Define the connection string
dbName = "Driver = {SQLite3 ODBC Driver}; Database = D: \ yourdbname.db"
'Open the database
ff.Open dbName
ff.Execute "create table a (a, b, c);"
'Other operations on and VBA to connect to other DB like Access the same.
'Close connection
ff.Close
回答by Jonathan
回答by Gregg Burns
google sqliteforexcel and download it from github
google sqliteforexcel 并从github下载
govert responded to this already (the creator), the easy solution to me, unless you can't.. don't use Access. Transfer the data to sqlite db files. You can even use ADO to a server or DAO and read the rs directly in to a sqlite table
govert 已经对此做出了回应（创建者），这对我来说是一个简单的解决方案，除非你不能……不要使用 Access。将数据传输到 sqlite db 文件。您甚至可以将 ADO 用于服务器或 DAO，并将 rs 直接读入 sqlite 表
It requires no driver or install. You can have your workbook download the 2 .dll's from a shared location on file open(check if they don't already exist on their file system, on say c:\temp), then user is running sqlite. I suspect, if you're so inclined to ask this, you'd want to write a function to loop calls to the appropriate sqlite functions(explained in example docs) and read returned data in to a vba array. Then you can do whatever you want with it.
它不需要驱动程序或安装。您可以让您的工作簿从文件打开的共享位置下载 2 个 .dll（检查他们的文件系统上是否尚不存在，例如 c:\temp），然后用户正在运行 sqlite。我怀疑，如果你很想问这个，你会想要编写一个函数来循环调用适当的 sqlite 函数（在示例文档中解释）并将返回的数据读入 vba 数组。然后你可以用它做任何你想做的事。
