excel - Excel ADODB查询中的VBA函数

Question

我正在Excel 2007中打开ADODB连接，以查询当前工作簿的工作表之一。尝试添加自定义VBA函数时，将引发错误“未定义的函数名称”。连接：

Dim connection As String
Dim records As ADODB.Recordset
Dim query As String
Dim fileName As String

fileName = ThisWorkbook.FullName
connection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & fileName & ";Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1"";"
query = "select t.[Col1] from [Sheet1$] As t"

Set records = New ADODB.Recordset
records.Open query, connection

Sheets(2).Range("A1").CopyFromRecordset records

我想要实现的是在选择中添加另一列，例如

query = "select t.[Col1], myFunc() from [Sheet1$] As t"

其中myFunc是工作簿中定义的功能。

我知道在Access中可以进行类似的操作（在查询中具有自定义的VBA函数）。在Excel中也可以吗？

此方案的最佳实践是什么？

Answer 1

我认为您在这里需要一些基本的解释，也许是这个问题的答案：

SQL中的功能从何而来？

如果您要将查询发送到任何支持ANSI-Standard SQL的数据库服务器，则数据库引擎将解析并运行SQL固有的“内联”函数：LEFT（），SUBSTRING（），SIN（），SQR（），等等。这里有一个简短的清单：

http://www.smallsql.de/doc/sql-functions/index.html

Oracle服务器以及Microsoft SQL Server将提供扩展ANSI SQL的其他功能。 PL-SQL和T-SQL都具有标准SQL中不可用的功能。它们都允许数据库所有者创建自己的函数，这些函数驻留在服务器上，并且也可用于SQL查询。

Microsoft Jet SQL与ANSI SQL不太一样，它具有一组相当有限的本机函数。但是没有人介意在MS-Access环境中运行Jet SQL的原因，因为几乎所有的Visual Basic for Applications功能都可以由MS-Access提供给SQL引擎。

此外，它们已经编写并在本地MS-Access项目中显示的所有全局声明的VBA函数也可用于SQL引擎。

只要您从Microsoft Access运行查询。

那很容易...

一旦移出MS-Access环境，您将看不到VBA。

您可以使用Jet SQL查询数据（而Microsoft.ACE.OLEDB.12.0提供程序正是这样做的），但是，如果您从Excel中运行数据，您将无法享受MS-Access数据库引擎提供的功能。使用VBA：您已经获得了本机Jet SQL函数列表，仅此而已

这些功能在此处列出，其他地方很少：

MS Access: Functions - Listed by Category

现在，该列表包括IIF()-内联'IF'函数-如果您想在SELECT子句中使用CASE语句，这就是您在Jet SQL中获得的全部内容；如果您在本机Jet-SQL中的第一课是查询中的所有VBA NZ（）函数都已停止工作，您会发现这很有用。

其中许多功能看起来像熟悉的VBA功能：这令人困惑，因为您没有运行VBA，而是在运行SQL。

很少有人知道，本机Jet函数的列表与MS-Access提供给SQL的本机VBA函数的列表不同，并且Microsoft在其文档中没有对此进行明确说明。

这是一个完整的PITA，因为每个查询SQL Server或Oracle DB的人都希望该服务器将运行SQL查询中所有已声明，导入或在该服务器上运行的SQL方言“原生”的函数。您已经在Access mdb中声明了VBA函数，并且希望它们也对SQL可见！

如何不解决此问题：

我见过一个Sybase服务器，在这个聪明而又被误导的数据库所有者中，您从一个肯定已经使用过的外部库中导入了函数，却没有意识到它存在于每个MS-Access数据库中：vbaen32.dll，VBA函数枚举dll。这需要大量的工作，而且从来没有做过：请不要尝试复制这种天才的壮举。

因此，简短的答案是“否”。

现在获取有用的答案：

Recordset.GetRows（）将以二维VBA数组的形式返回记录集，并且在SQL引擎完成繁重的排序，过滤和聚合操作之后，您可以在该数组上运行VBA函数。

如果您在仅向前的游标上按块顺序地运行vba，则调用Recordset.GetRows（Rows：= 1024），直到达到数据结尾，您就可以在大型数据集上高效地执行此操作，而不会占用过多的内存。

尽管您可能想问：“我为什么要这样做？”，因为很难想到一个流程，在该流程中，更好的分析和设计无法揭示更好的解决方案。我，我必须为一个团队实施该hack，该团队的Excel依赖过程在不断增长的csv文件上运行……并且增长到TB级的大小，只能由数据库驱动程序读取。而且，他们在这样的环境中工作：购买适当的数据库服务器需要2-3年的持续管理工作。

如果您是幸运的儿子，在我退出后继承了该特定流程，则建议您从轨道上绕开该站点后尝试使用GetRows“解决方案”。




脚注：如果您找到更好的Jet SQL函数在线清单，请扩大此答案。

同时，我敦促任何其他阅读此书的'Stack贡献者将自己的链接添加到Jet SQL本机功能的良好列表中（如果可以找到的话）。大多数错误是错误的，没有一个是全面的，很少有明确指出本机功能和导入的VBA之间存在差异。就我所知，Jet Engine正在从VBAEN32.dll导入并运行一组受限制的功能-但是ADODB下的Jet绝对不是功能齐全的VBA主机应用程序，因此当您使用在MS-Access之外使用它。