当转换为VBA SELECT查询不起作用 - 无效的SQL语句 [英] SELECT query does not work when converted to VBA - invalid SQL statement

问题描述

我一直在努力与SQL语句时转换为VBA。基本上，我想添加一些内容在执行前（这不应该是一个问题）。然而，当我尝试更改之前运行，VBA不承认它是一个有效的SQL语句。尽管试图添加/删除括号和其他任何特殊字符，它仍然无法正常工作。请注意，它完美的作品时，作为查询运行。请参阅下面的字符串：

I have been struggling with SQL statement when converted to VBA. Basically, I wish to add something to it before executing (which shouldn't be a problem). However, when I try to run it before changes, VBA does not recognize it as a valid SQL statement. Despite trying to add/remove brackets and any other special character, it still does not work. Please note, that it works perfectly when run as a query. Please see the string below:

SQLstr = "SELECT SourceData.[Fiscal Year], SourceData.[Fiscal Quarter ID], " _
& "SourceData.[Transaction Date], SourceData.[Sales Order Number], SourceData.[Activated?], " _
& "SourceData.[Product ID], SourceData.[Bookings Quantity], SourceData.[Term Length], " _
& "SourceData.[Estimated Expiring Quarter], SourceData.[End Customer Company Name], " _
& "SourceData.[Sold To Company Name] " _
& "FROM SourceData, finalCust, finalPart " _
& "WHERE (((SourceData.[End Customer Company Name]) Like finalCust.[FinalList]) " _
& "And ((SourceData.[Sold To Company Name]) Like finalPart.[FinalList]))"

在code是纯粹的SQL进入VBA，没有任何修改，但我不想误导。

The code is 'pure' SQL into VBA, without any amendments but I don't want to mislead.

下面是一个错误信息：

Run-time error '2342':
A RunSQL action requires an argument consisting of an SQL statement.

这点我会考虑为不可读的SQL语句的VBA。

Which I would consider as unreadable SQL statement for VBA.

推荐答案

这是错误消息是误导。真正的问题是， DoCmd.RunSQL 是为行动查​​询：更新; 插入; 删除;等等。

That error message is misleading. The real problem is that DoCmd.RunSQL is intended for "action" queries: UPDATE; INSERT; DELETE; etc.

它不会接受一个普通的 SELECT 查询。例如，这个简单的 SELECT 查询给了我同样的A RunSQL操作需要由一个SQL语句的参数的从消息 DoCmd.RunSQL ：

It will not accept a plain SELECT query. For example, this simple SELECT query gives me that same "A RunSQL action requires an argument consisting of an SQL statement" message from DoCmd.RunSQL:

Dim SQLstr As String
SQLstr = "SELECT * FROM tblFoo;"
DoCmd.RunSQL SQLstr

不过， DoCmd.RunSQL 执行这个有效的更新语句没有错误：

However, DoCmd.RunSQL executes this valid UPDATE statement without error:

SQLstr = "UPDATE tblFoo SET long_text='bar' WHERE id=1;"
DoCmd.RunSQL SQLstr

您需要一个不同的方法来使用你的 SELECT 查询。和方法的选择取决于你想要做的查询返回的结果是什么。

You need a different method to use your SELECT query. And the choice of method depends on what you want to do with the results returned by the query.

这篇关于当转换为VBA SELECT查询不起作用 - 无效的SQL语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！