Excel中的VBA函数ADODB查询 [英] VBA function in Excel ADODB query
问题描述
我在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& ;扩展属性=Excel 12.0 Xml; HDR = YES; IMEX = 1;
query =select t。[Col1] from [Sheet1 $] As t
设置记录=新建ADODB.Recordset
records.Open查询，连接
表格（2）.Range（A1）。CopyFromRecordset记录
喜欢实现是在select中有另一列，如
query =select t。[Col1]，myFunc（）从[Sheet1 $] As t
其中myFunc是工作簿中定义的函数。 >
我知道在Access中有这样的可能（在查询中有自定义VBA功能）。这是否可能在Excel中？
此方案的最佳做法或解决方法是什么？
我想你在这里需要一些基本的解释，也可能是这个问题的答案：
SQL中的功能来自哪里？
如果您向支持ANSI-Standard SQL的任何数据库服务器发送查询，数据库引擎将解析并运行内联函数本机到SQL：LEFT（），SUBSTRING（），SIN（），SQR（）等。这里有一个简短的列表：
http://www.smallsql.de/doc/sql-functions/index.html
Oracle服务器将公开扩展ANSI SQL的附加功能，Microsoft SQL Server也将公开; 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-访问数据库引擎使用VBA的能力：您具有本机Jet SQL功能列表，而没有其他任何
这里列出的功能和其他几个地方： / p>
该列表现在包括 IIF（） - 内联'IF'功能 - 如果您想在SELECT子句中要一个CASE语句，那么这就是Jet SQL中的所有功能。如果您的本机Jet-SQL中的第一课是您查询中的所有VBA NZ（）函数都停止工作，那么您将发现这很有用。
这些功能中的许多功能看起来像熟悉的VBA功能：这是一个混乱的根源，因为你没有运行VBA，你正在运行SQL 。
很少有人理解，本机Jet功能列表与MS-Access可用于SQL的本地VBA功能列表不同，Microsoft不会在其文档中明确表示。
这是一个完整的PITA，因为每个人查询SQL服务器或Oracle数据库都希望服务器能够将其所有的SQL查询中的所有和任何函数都声明，导入或本地 SQL在该服务器上运行。您已经在Access mdb中声明了VBA功能，并且您希望SQL也可以看到它！
如何解决此问题：
我看到一个Sybase服务器，其中辉煌但误导的数据库所有者已经从您绝对使用的外部库导入功能，而不意识到它在每个MS-Access中数据库：vbaen32.dll，VBA函数枚举dll。这需要相当多的工作，永远不会工作：请不要尝试复制这个天才的壮举。
所以简短的答案
现在有用的答案：
记录集。 GetRows（）将作为二维VBA数组返回您的记录集，您可以在SQL引擎完成大量排序，筛选和聚合后，再运行VBA函数。
如果您在调用Recordset的Forward-Only游标上以块的顺序运行vba，您可以在大型数据集上高效地执行此操作，而不会有过多的内存占用。 GetRows（Rows：= 1024），直到你的数据结束。
虽然你可能想问：为什么我这样做？很难想到一个更好的分析和设计不会揭示更好的解决方案的过程。我，我不得不实施一个团队，他们的依赖Excel的过程运行在csv文件的增长，并增长...并增长到只能被数据库驱动程序读取的太字节大小。他们在一个环境中工作，得到一个适当的数据库服务器需要2-3年的持续管理努力。
如果你是幸运的儿子后继承了特定的过程退出后，建议在轨道上点击网站后尝试我的GetRows解决方案。
脚注：如果你找到更好的在线列表的Jet SQL功能。
同时，我会敦促任何其他Stack贡献者谁读这个添加自己的链接到一个好列出Jet SQL本机功能 - 如果可以找到一个。大多数是错误的，没有一个是全面的，很少有人明确表示本机功能和导入的VBA之间有区别。对于我所知道的，Jet Engine正在从VBAEN32.dll导入并运行一组有限的功能，但ADODB下的Jet绝对不是一个功能齐全的VBA主机应用程序，而且当您在MS-Access之外使用它。
I'm opening an ADODB connection in Excel 2007 to query one of the worksheets of the current workbook. When trying to add a custom VBA function, an error raises "Undefined function name". The connection:
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
What I would like to achieve is to have another column in the select, like
query = "select t.[Col1], myFunc() from [Sheet1$] As t"
where myFunc is a Function defined in the workbook.
I know that something like this is possible in Access (to have custom VBA functions in a query). Is this possible in Excel too?
What's the best practice or workaround for this scenario?
I think you need some basic explanations here, and maybe an answer to this question:
Where do the functions in SQL come from?
If you're sending queries to any database server that supports ANSI-Standard SQL, the database engine will parse and run 'inline' functions that are native to SQL: LEFT(), SUBSTRING(), SIN(), SQR(), etc. There's a short list here:
http://www.smallsql.de/doc/sql-functions/index.html
Oracle servers will expose additional functions that extend ANSI SQL, as will Microsoft SQL Servers; both PL-SQL and T-SQL have functions that are not available in standard SQL. Both of them allow the DB Owner to create their own functions, which reside on the server and are also available to SQL queries.
Microsoft Jet SQL, which isn't quite the same as ANSI SQL, has a rather limited set of native functions; but nobody minds when they are running Jet SQL in an MS-Access environment, because almost all of the Visual basic for Applications functions are made available to the SQL engine by MS-Access.
Furthermore, all of the globally-declared VBA functions that they've written and made visible in the local MS-Access project are made available to the SQL engine, too.
As long as you're running the query from Microsoft Access.
That's the easy part...
Once you move outside the MS-Access environment, you can't see the VBA.
You can query the data using Jet SQL (and the Microsoft.ACE.OLEDB.12.0 provider is doing exactly that) but, if you're running it from Excel, you're not going to enjoy the MS-Access database engine's ability to use VBA: you've got the native Jet SQL function list, and nothing else
The functions are listed here, and very few other places:
MS Access: Functions - Listed by Category
That list now includes IIF() - the inline 'IF' function - which is all you've got in Jet SQL if you want a CASE statement in your SELECT clause; you'll find that useful if your first lesson in native Jet-SQL is that all the VBA NZ() functions in your query have stopped working.
Many of these functions look like the familiar VBA functions: and this is a source of confusion, because you're not running VBA, you're running SQL.
Very few people understand that this list of native Jet functions is not the same as the list of native VBA functions made available to SQL by MS-Access, and Microsoft do not make this explicit in their documentation.
This is a complete PITA because everyone querying a SQL server or an Oracle DB expects that the server will run all and any functions in their SQL query that are declared, imported or 'native' to the dialect of SQL running on that server. You've declared VBA functions in the Access mdb, and you expected they would be visible to SQL too!
How not to fix this:
I have seen a Sybase server where the brilliant but misguided database owner had imported functions from an external library that you have definitely used without realising that it's there in every MS-Access database: vbaen32.dll, the VBA function enumeration dll. This required quite a lot of work, and never quite worked: please do not attempt to replicate this feat of genius.
So the short answer is 'No'.
Now for the useful answer:
Recordset.GetRows() will return your recordset as a 2-Dimensional VBA array, and you can run your VBA functions on that after the SQL engine has done the heavy lifting of sorting, filtering and aggregation.
You can do this efficiently on a large data set, without an excessive memory footprint, if you run your vba sequentially in chunks on a Forward-Only cursor, calling Recordset.GetRows(Rows:=1024) until you hit the end of the data.
Although you might want to ask: "Why am I doing this?", as it's very difficult to think of a process where better analysis and design wouldn't reveal a better solution. Me, I had to implement that hack for a team whose Excel-dependent process ran on csv files that grew, and grew... And grew to terabyte sizes that could only be read by a database driver. And they work in an environment where getting a proper database server takes 2-3 years of sustained managerial effort.
If you are the fortunate son who inherited that particular process after I quit, I recommend trying my GetRows 'solution' after nuking the site from orbit.
Footnote: Do, please, expand this answer if you find a better online listing for Jet SQL functions.
Meanwhile, I would urge any other 'Stack contributor who reads this to add their own links to a good listing of Jet SQL native functions - if you can find one. Most are wrong and none are comprehensive, and very few are explicit in stating that there's a difference between native functions and imported VBA. For all I know, the Jet Engine is importing and running a restricted set of functions from VBAEN32.dll - but Jet under ADODB definitely isn't a fully-featured VBA host application, and that limitation needs to be clearly understood when you're using it outside MS-Access.
这篇关于Excel中的VBA函数ADODB查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
