SQL to Excel table in VBA using IF EXISTS complains "Invalid SQL statement"

Question

I am using SQL in VBA inside MS Excel to query a spreadsheet. I want to check for a record's existence, and either update or insert the record as appropriate. This calls for:

IF EXISTS (condition) UPDATE ELSE INSERT

The "Test" portion doesn't work, and raises an error. In debugging, I have eliminated all Excel elements from the query, resulting in the following code:

Dim conn As New ADODB.Connection
Dim recset As New ADODB.Recordset
Dim scmd As String
scmd = "IF EXISTS (SELECT 'Test')"
scmd = scmd + "SELECT 'Found' "

conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & ";" & _
"Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"
conn.Execute (scmd)

conn.Close

This generates the SQL statement:

IF EXISTS (SELECT 'Test') SELECT 'Found'

The resulting error is:

Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.

I tested the same statement in MS SSMS, and it worked properly. Having eliminated both my Excel spreadsheet and the SQL statement as problems, it seems the problem is in a quirk of VBA. How do I fix this?

For what it's worth, the connection string works properly for pure SELECT queries in my project.

Welcome to Stackoverflow! What's your exact SQL statement? Does that Test qualify as a column name or a table name? Can you use [] with proper column and table names and try the query? — bonCodigo
– bonCodigo, Commented Jun 29, 2015 at 14:33
@bonCodigo In my simplified statement, I am explicitly selecting the string Test. — Bee Dee Ess
– Bee Dee Ess, Commented Jun 29, 2015 at 14:42
So what's the rewritten SQL your using? you cant use IF EXISTS — Alex K.
– Alex K., Commented Jun 29, 2015 at 14:49

HansUp · Accepted Answer · 2015-06-29 15:03:44Z

2

You're using the ACE OleDb provider to interact with the spreadsheet. That means you can use only SQL features that provider supports for your data source. IF EXISTS is not supported in this situation.

However, there may be an even more basic problem. I don't believe it's possible to alter the contents of a spreadsheet with SQL executed from ACE OleDb.

answered Jun 29, 2015 at 15:03

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

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

5 Comments

Bee Dee Ess Over a year ago

Ah, that would explain it. Can I use a different provider to query (and update!) the other spreadsheet?

HansUp Over a year ago

I'm not aware of any SQL (DML) method to alter a spreadsheet's content.

Bee Dee Ess Over a year ago

Bummer. :( I had been exploring SQL because updating the target spreadsheet through automation (open the XLS file, find target row, copy data, save & close, lather-rinse-repeat) had a big performance impact.

HansUp Over a year ago

I feel your pain. I'm primarily a database person. Maybe another spreadsheet person can offer you a less painful alternative.

Bee Dee Ess Over a year ago

I'm a software person forced into a spreadsheet position. Though I did come up with the idea of updating only the one row of interest, with some thanks to your answer.

Ralph · Accepted Answer · 2015-06-29 15:03:13Z

0

Dim conn As New ADODB.Connection
Dim recset As New ADODB.Recordset
Dim scmd As String

scmd = "SELECT * from [Sheet1$]"

conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source=" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & ";" & _
    "Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"
recset.ActiveConnection = conn
recset.Open scmd

If Not recset.EOF And Not recset.BOF Then
    Debug.Print "found"
End If

conn.Close

This should do the trick. Basically, this is your code with a minor change. As Alex-K noted, the Excel engine cannot evaluate if statements for your. An SQL server would be able to. But within Excel you'll have to restrict your SQL language and evaluate the if yourself as shown above.

edited Jun 29, 2015 at 15:03

answered Jun 29, 2015 at 14:37

Ralph

9,4644 gold badges35 silver badges42 bronze badges

1 Comment

Bee Dee Ess Over a year ago

And thanks for catching the missing space. My original query does have that space.

Collectives™ on Stack Overflow

SQL to Excel table in VBA using IF EXISTS complains "Invalid SQL statement"

2 Answers 2

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related