使用CTE返回没有记录的Excel VBA ADODB sql查询
这是我的第一个关于Stack Overflow的问题。我从这个网站学到很多东西,但是我一直无法找到我遇到的问题的答案。使用CTE返回没有记录的Excel VBA ADODB sql查询
我有一个SQL查询可以在SQL Server 2008 R2管理工作室中使用,但在EXCEL 2013中使用vba查询时无法正常工作。查询包含一个公用表表达式,并且它不会将任何记录返回到我的记录集。
SQL查询是:
WITH cte AS
(SELECT *, ROW_NUMBER() OVER (PARTITION BY [partNumber]
ORDER BY [date] DESC) AS i FROM [myDB].[dbo].[PartOrders]
WHERE [partDescription] like '%motor%')
SELECT * FROM cte WHERE i = 1
我对Microsoft ActiveX数据对象的引用6.1库
我使用的VBA代码:
Dim conn as ADODB.Connection
Dim sql as String
Dim rst as ADODB.Recordset
Set conn = New ADODB.Connection
conn.ConnectionString = myConnectionString
conn.Open
sql = ";WITH cte AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY " & _
"[partNumber] ORDER BY [date] DESC) AS i " & _
"FROM [myDB].[dbo].[PartOrders]" & _
"WHERE [partDescription] like '%motor%') " & _
"SELECT * FROM cte WHERE i = 1 "
Set rst = New ADODB.Recordset
rst.Open sql, conn, adOpenStatic, adLockReadOnly, adCmdText
debug.print rst.recordcount
conn.Close
Set rst = Nothing
Set conn = Nothing
我的代码打印立即窗口中的“-1”
我已添加前面的“;”根据另一个问题回答的建议来回答我的问题。它没有什么区别。
我已经验证下面的查询字符串返回一个记录:
sql = "SELECT *, ROW_NUMBER() OVER (PARTITION BY " & _
"[partNumber] ORDER BY [date] DESC) AS i " & _
"FROM [myDB].[dbo].[partNumbers]" & _
"WHERE [partDescription] like '%motor%'"
我用一个CTE由于需要收集表的全部记录,但只有特定部分的描述。我不想看到一个电机订购了20次。我希望看到发动机至少与其他相关领域一起订购了一次。我正在搜索730,000条记录,其中有10000个电机的记录,但只有500个不同类型的记录。
我打开使用不同的查询,如果它将网络相同的结果,但我真的很好奇,为什么我当前的查询不产生任何记录。我希望这不是由于ADODB和VBA的兼容性。
非常感谢您收到他人提供的所有帮助,我非常感谢您能为我提供的任何帮助。
Parfait在我对原始问题的评论中为我提供了一个解决方案。将连接字符串提供程序更改为驱动程序不起作用。它造成了一个Run-time error '-2147467259 (80004005)'
而不是CTE,我在我的查询中使用了派生表。
SQL查询VBA中返回的RecordCount对我来说是:
SELECT main.*
FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY [partNumber]
ORDER BY [date] DESC) AS i FROM [myDB].[dbo].[PartOrders]
WHERE [partDescription] like '%motor%')
AS main WHERE main.i = 1
谢谢!
这必须是MSSQL版本(即2008 R2)和/或其与'OLEDB提供程序'的交互,尽管它是为什么CTE是在SQL Server 2005中引入的!如果您有权访问更高版本(2012/2014/2016),请尝试使用CTE。 – Parfait
我尝试过,但尝试过,但不能重现您的问题,虽然我使用MSSQL 2012.精确的SQL(不同的表)和相同的VBA对象和属性。 Recordcount返回正数。什么是连接字符串中的* Driver *(唯一要测试的其他变量)?无论记录数是否查询返回任何东西? – Parfait
我没有连接字符串中的驱动程序设置...我的模糊连接字符串是...“Provider = SQLOLEDB.1; Data Source = server.address; uid = user; pwd = password; Initial Catalog = myDB” 。无论记录计数如何,查询都不会返回任何内容。 – user7638417
哇!即使使用*提供者*我也无法重现您的问题。也许你应该试试Driver:'DRIVER = {SQL Server}; server = server; database = myDB; UID = user; PWD = password;'尝试使用派生表(CTE的替代):'SELECT main。* FROM(SELECT ...)主要在哪里main.i = 1'。 – Parfait