CODEWITHSUNDEEP
sqlvbaexcel
Jimjebus
Jimjebus

Reputation: 109

VBA SQL query strings, how much is supported?

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.

Upvotes: 1

Views: 142

Answers (3)

user2140173
user2140173

Reputation: 

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

Upvotes: 2

cyboashu
cyboashu

Reputation: 10433

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"","

Upvotes: 1

Gareth
Gareth

Reputation: 5243

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.

Upvotes: 2

Related Questions