VBA与SQL的麻烦 [英] VBA trouble with SQL

问题描述

我正在将此代码用于Access 2010数据库，并且我的SQL语句的语法似乎有问题，但是我一辈子都无法弄清楚如何正确设置该语句的格式.预先感谢您的帮助！

I'm using this code for an Access 2010 database, and I seem to have a problem with the syntax of my SQL statement, but I can't for the life of me figure out how to correctly format the statement. Thank you in advance for any help!

qdf = db.CreateQueryDef("Company_State_Q")

Dim strSQLSearch As String


strSQLSearch = "SELECT [Company Information].Company_Name, " & _
"[Company Information].Industry" & _
"FROM Company Information" & _
"WHERE [Company Information].State ='" & stateV & "'" & _
"ORDER BY [Company Information].Company_Name;"



qdf.SQL = strSQLSearch

推荐答案

您应该使用...

Debug.Print strSQLSearch 

生成strSQLSearch字符串后，在代码中

....然后，您可以在立即窗口中查看字符串(使用 Ctrl + g 进入该字符串).

... in your code after you build the strSQLSearch string. You can then view the string in the Immediate window (go there with Ctrl+g).

在即时"窗口中，将stateV的值设置为"CA"，并像这样测试您的字符串:

From the Immediate window I set the value of stateV to "CA" and tested your string like this:

stateV = "CA" : ? "SELECT [Company Information].Company_Name, " & _
"[Company Information].Industry" & _
"FROM Company Information" & _
"WHERE [Company Information].State ='" & stateV & "'" & _
"ORDER BY [Company Information].Company_Name;"
SELECT [Company Information].Company_Name, [Company Information].IndustryFROM Company InformationWHERE [Company Information].State ='CA'ORDER BY [Company Information].Company_Name;

一旦您真正看到完整的strSQLSearch字符串，就很容易发现多个问题:

Once you actually see the completed strSQLSearch string, it's easy to spot multiple problems:

1. IndustryFROM应该是Industry FROM
2. FROM Company Information应该是FROM [Company Information]
3. InformationWHERE应该是Information WHERE
4. 'CA'ORDER应该是'CA' ORDER

1. IndustryFROM should be Industry FROM
2. FROM Company Information should be FROM [Company Information]
3. InformationWHERE should be Information WHERE
4. 'CA'ORDER should be 'CA' ORDER

考虑在SELECT语句的各部分之间使用换行符.数据库引擎对换行符而不是空格感到非常满意.而且您可能会发现，将语句读为几行而不是一行很容易.

Consider using line breaks between the sections of your SELECT statement. The db engine is perfectly happy with line breaks instead of spaces. And you may find it easier to read the statement as several short lines rather than as one long line.

Dim strSQLSearch As String
strSQLSearch = "SELECT ci.Company_Name, ci.Industry" & vbCrLf & _
    "FROM [Company Information] AS ci" & vbCrLf & _
    "WHERE ci.State ='" & stateV & "'" & vbCrLf & _
    "ORDER BY ci.Company_Name;"
Debug.Print strSQLSearch

在代码的开头，您也有...

Also at the beginning of your code you have ...

qdf = db.CreateQueryDef("Company_State_Q")

看来qdf必须是DAO.QueryDef对象，所以我认为您应该使用Set关键字来为其分配.

It seems qdf must be a DAO.QueryDef object, so I think you should use the Set keyword to assign to it.

Set qdf = db.CreateQueryDef("Company_State_Q")

这篇关于VBA与SQL的麻烦的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！