VBA SQL query strings, how much is supported?

Question

still working on a query for a project and my partner has managed to come up with a nifty SQL statement what works wonders when run but wont seem to work in VBA and it has got me questioning how much of SQL statements are supported in VBA

This is the original query which my work partner whipped up and it works great when running the query in 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` != ''

I know VBA likes the SQL structured a little different so i adapted it to this, which maybe wrong so if it is feel free to burn me on it because i need to learn.

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

Apologies in advance if its something ive missed but anything other than simple selects or inserts/deletes and updates some of the other features of SQL like joins etc dont seem to work for me in VBA

If anyone knows where i've gone wrong then that'll be great and if ive done it fine and its non supported features even an explanation of why would be great so i can related it back to my work friends who believes it works.

Thanks in advanced guys.

Could be that all the backtick ( ` ) characters are required in whatever application's implementation of SQL is being used - I've seen something similar before. — citizenkong
– citizenkong, Commented Sep 1, 2014 at 14:52
Even if backticks are not required, you have mismatched single apostrophes. There's only one at 'status _. — Andrew Leach
– Andrew Leach, Commented Sep 1, 2014 at 14:56
Just a helpful suggestion for if you still cant get this working;; if you have access to it, you could run this query in PHP and then have VBA request the data from PHP using MSXML2. I had a similar problem like this, and PHP was my workaround :) — user3205578
– user3205578, Commented Sep 1, 2014 at 15:17

score 2 · Accepted Answer · 2014-09-03 07:47:00Z

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

Gareth · Accepted Answer · 2014-09-01 14:56:53Z

2

You need to concatenate the strings of data across the multiple lines like so:

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

Given the large amount of text you have, you may run into an error Too many line continuations (as the maximum amount of line continuations is 25). In which case you can concatenate the strings like so:

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

Note As you are writing a SQL statement, you need to make sure that you include spaces in the correct places i.e. you most likely need to leave a space at the end of each line.

answered Sep 1, 2014 at 14:56

Gareth

5,2636 gold badges47 silver badges73 bronze badges

Comments

cyboashu · Accepted Answer · 2014-09-01 15:01:12Z

1

write it without single ticks and avoid single quotes, in VBA use double quotes, always.

something like this

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

answered Sep 1, 2014 at 15:01

cyboashu

10.5k2 gold badges32 silver badges48 bronze badges

Collectives™ on Stack Overflow

VBA SQL query strings, how much is supported?

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related