Run-time error when executing SQL in VBA

Question

I am getting Run-time error '-2147217900 (80040e14) Incorrect syntax new '11999999' when trying to pull data in from a SQL server database in to a Recordset in VBA, this is my first time using VBA so forgive the lack of perfection.

My code is as follows;

Set connection = New ADODB.Connection
Dim newData As ADODB.Recordset

connection.ConnectionString = "my connection string" 'not posting this for security reasons'
connection.Open

Set newData = connectionExecute(BSMARTOpenFaults )

I have my query stored as a multiline string as I prefer it becuase it looks better with SQL statements.

Private Const BSMARTOpenFaults = "select count(*) from call" _
& "where (" _
& "   call_id between 11000000 and 11999999" _
& "or call_id between 12000000 and 12999999" _
& "or call_id between 14000000 and 14999999" _
& "or call_id between 16000000 and 19999999" _
& "or call_id between 26000000 and 26999999" _
& "or call_id between 31000000 and 31999999" _
& "or call_id between 73000000 and 73999999)" _
& "and call_status <> 2 -- all open calls" _
& "and call_type = 'FT'"

Judging by the error its falling on the SQL query (or its just a coincedence that the two numbers are the same) but I am not sure on how to fix it because when I run the query on SQL Server Management Studio the query executes and returns a result (21 to be exact) so it is confusing me as to why it won't execute and returns an invalid syntax error when attempting in VBA, is this an incorrect way of formatting SQL statements in VBA?

Panagiotis Kanavos · Accepted Answer · 2017-06-08 10:19:08Z

4

There are no newlines between the numbers and OR keywords. Your statement looks like :

and 11999999or call_id between 12000000 and 12999999or and

Add a space after the number and/or before the keywords

answered Jun 8, 2017 at 10:19

Panagiotis Kanavos

134k16 gold badges211 silver badges270 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

WhatsThePoint Over a year ago

thanks, didnt realise i had forgotten the spaces inbetween the quotes and because obviously my normal form of it for running in management studio doesnt have the qutoes i didnt realise

Robin Mackenzie · Accepted Answer · 2017-06-08 10:21:08Z

Don't skip the spaces at the end of each section of the string. At the moment your string returns:

select count(*) from callwhere (...

And callwhere is probably not a table in your schema. Same issue is there for the OR clauses. Try this:

Private Const BSMARTOpenFaults = "select count(*) from call " _
& "where (" _
& "   call_id between 11000000 and 11999999 " _
& "or call_id between 12000000 and 12999999 " _
& "or call_id between 14000000 and 14999999 " _
& "or call_id between 16000000 and 19999999 " _
& "or call_id between 26000000 and 26999999 " _
& "or call_id between 31000000 and 31999999 " _
& "or call_id between 73000000 and 73999999) " _
& "and call_status <> 2 " _
& "and call_type = 'FT'"

Vityata · Accepted Answer · 2017-06-08 10:26:01Z

2

General tip what to do, when you are debugging SQL in VBA:

declare the BSMARTOpenFaults as a public for the test.
Go to the immediate window and write ?BSMARTOpenFaults
See the output.
Examine the output.
See that callwhere is one word.
Change the code to Const BSMARTOpenFaults = "select count(*) from call " _ so the words are 2.

edited Jun 8, 2017 at 10:26

answered Jun 8, 2017 at 10:13

Vityata

43.7k10 gold badges64 silver badges110 bronze badges

Collectives™ on Stack Overflow

Run-time error when executing SQL in VBA

3 Answers 3

1 Comment

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Related