VBA SQL查询字符串，支持多少？

问题描述：

仍然在为一个项目查询工作，我的合作伙伴已经设法提出一个漂亮的SQL语句什么时候运行奇迹，但似乎在VBA中工作，它让我质疑有多少SQL语句在VBA中受支持VBA SQL查询字符串，支持多少？

这是我的工作伙伴刮起了原来的查询和SQL

SELECT 
    crm_clients.`id`, 
    crm_clients.`national_insurance`, 
    crm_clients.`total_hmrc`, 
    (SELECT 
    crm_crmuseractions.title 
    FROM 
    dev_pfands.`crm_crmuseractions` 
    WHERE crm_crmuseractions.`id` = crm_clients.`status`) AS `status` 
FROM 
    dev_pfands.`crm_clients` 
    INNER JOIN crm_client_cheques 
    ON crm_clients.id = crm_client_cheques.`client_id` 
    INNER JOIN dev_pfands.`crm_payments` 
    ON crm_clients.id = crm_payments.`client_id` 
    INNER JOIN dev_pfands.`crm_self_assesments` 
    ON crm_clients.id = crm_self_assesments.`client_id` 
WHERE crm_clients.`status` = 9 
    OR crm_clients.`status` = 8 
    OR crm_clients.`status` = 7 
    OR crm_clients.`national_insurance` != '' 
    OR crm_clients.`id` != ''

运行查询时，它的伟大工程，我知道VBA喜欢的SQL结构略有不同，所以我就适应了这个，这也许是错误的，如果它是随意烧我，因为我需要学习。

sql = "SELECT crm_clients.id, crm_clients.national_insurance, crm_clients.total_hmrc _ 
    (SELECT _ 
    crm_crmuseractions.title _ 
    FROM _ 
    crm_crmuseractions _ 
    WHERE crm_crmuseractions.id = crm_clients.status) AS 'status _ 
    FROM _ 
    crm_clients _ 
    INNER JOIN crm_client_cheques _ 
    ON crm_clients.id = crm_client_cheques.client_id _ 
    INNER JOIN crm_payments _ 
    ON crm_clients.id = crm_payments.client_id _ 
    INNER JOIN crm_self_assesments.client_id _ 
    WHERE crm_clients.status = 9 _ 
    OR crm_clients.status = 8 _ 
    OR crm_clients.status = 7 _ 
    OR crm_clients.national_insurance != '' _ 
    OR crm_clients.id != '' "

道歉提前如果一些香港专业教育学院错过了，但不是简单的选择或插入/删除和其他任何更新一些SQL的其他功能，如连接等不似乎对我在VBA

如果工作任何人都知道我出错的地方，那样会很好，如果我做得很好，它的不支持功能甚至可以解释为什么会很好，所以我可以把它和我工作的朋友相联系。

感谢先进的家伙。

可能是所有的反引号（'）字符需要在任何应用程序的执行SQL的被使用 - 我以前见过类似的东西。 – citizenkong 2014-09-01 14:52:46

''''蜱是什么意思？列名称 – 2014-09-01 14:53:49

即使反引号不是必需的，您也有不匹配的单引号。 ''status''只有一个。 – 2014-09-01 14:56:44

答

Dim query As String 

query = "SELECT " & _ 
     "crm_clients.id, " & _ 
     "crm_clients.national_insurance, " & _ 
     "crm_clients.total_hmrc, " & _ 
     "(SELECT " & _ 
     " crm_crmuseractions.Title " & _ 
     "FROM " & _ 
     " dev_pfands.crm_crmuseractions " & _ 
     "WHERE crm_crmuseractions.`id` = crm_clients.status) AS 'status' " & _ 
     "FROM " & _ 
     "dev_pfands.crm_clients " & _ 
     "INNER JOIN crm_client_cheques " & _ 
     " ON crm_clients.id = crm_client_cheques.client_id " & _ 
     "INNER JOIN dev_pfands.crm_payments " & _ 
     " ON crm_clients.id = crm_payments.client_id " & _ 
     "INNER JOIN dev_pfands.crm_self_assesments " & _ 
     " ON crm_clients.id = crm_self_assesments.client_id " & _ 
     "WHERE crm_clients.status = 9 " & _ 
     "OR crm_clients.status = 8 " & _ 
     "OR crm_clients.status = 7 " & _ 
     "OR crm_clients.national_insurance != '' " & _ 
     "OR crm_clients.id != ''"

答

您需要连接的数据串横跨多行，像这样：

strText = "This is the first line " & _ 
      "This is the second line"

鉴于你有大量的文字，你可能会碰到一个错误Too many line continuations（如最高金额续行是25）。在这种情况下，您可以在连接字符串，像这样：

strText = "This is the first line" 
strText = strText & "This is the second line"

注意当你正在编写SQL语句，你需要确保您在正确的位置空间，即你最有可能需要离开每行末尾的空格。

感谢Man的建议，明天当我返回工作时我会尝试这个 – Jimjebus 2014-09-01 15:05:02

答

写它没有单一的蜱和避免单引号，在VBA中总是使用双引号。

像这样

Sql = "SELECT" & _ 
    " crm_clients.""id""," & _ 
    " crm_clients.""national_insurance"","

VBA SQL查询字符串，支持多少？

相关推荐