如何从VBA访问SQLite? [英] How do I access SQLite from VBA?

问题描述

我有一个Excel工作簿，其中包含一些用于查询本地访问数据库的adodb代码.我想对SQLite做同样的事情，因为我相信这将提供更好的性能.我怎么做?我可以使用adodb或odbc连接到SQLite文件吗?

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的peoople在PC上没有管理员权限，所以他们无法安装软件.

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 VBA直接访问SQLite3.最终结果是一个简单得多的解决方案，中间没有ODBC或OleDb/ADO层，并且性能反映了SQLite数据库的性能，而不是不透明包装的性能.这也很好，因为您无需在注册表中注册COM组件，只需将两个.dll与工作簿一起复制，然后将.bas模块添加到您的项目中即可.

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.

此方法的缺点是API不是标准的DAO或ADO接口，因此您可能需要进行一些包装或转换一些代码以使其起作用.这也意味着您需要熟悉SQLite API才能使用它，但是SQLite文档非常清楚.

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.

我已经在CodePlex上放置了该项目的早期版本: SQLite for Excel 提供了SQLite3 API函数的高性能路径，可以保留SQLite3库调用的语义，并允许在不重新编译的情况下访问分布式SQLite3.dll.

I have put an early version of the project on CodePlex: SQLite for Excel provides 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.

任何反馈将不胜感激.

更新:用于Excel的SQLite 项目现在位于 GitHub .

这篇关于如何从VBA访问SQLite?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！