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

Question

仍然在对一个项目进行查询，我的合作伙伴成功地想出了一个漂亮的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中不起作用。

如果有人知道我哪里出了问题，那就太好了，如果我做得很好，并且它的不受支持的特性，甚至解释一下为什么会很棒，这样我就可以把它反馈给我的工作朋友，他们相信它是有效的。

感谢先进的人。

Answer 1

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 != ''"

Answer 2

您需要在多行之间连接数据字符串，如下所示：

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"

在编写注释语句时，需要确保在正确的位置包括空格，即最有可能需要在每一行末尾留出一个空格。

Answer 3

写它没有单引号和避免单引号，在VBA使用双引号，始终。

像这样的东西

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