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

I am using SQL in VBA inside MS Excel to query a spreadsheet. I want to check for the existence of a record and either update or insert a record as needed. It requires:

IF EXISTS (condition) UPDATE ELSE INSERT

      

The "Test" part does not work and generates an error. In debug, I removed all Excel items 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 an SQL statement:

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

      

Resulting error:

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

I tested the same statement in MS SSMS and it worked correctly. After running out of both the Excel spreadsheet and the SQL statement as problems, it seems that the problem is a VBA quirk. How to fix it?

For what it's worth, the connection string works correctly for clean queries SELECT

in my project.

+3


source to share


2 answers


You are using the ACE OleDb provider to interact with the spreadsheet. This means that you can only use SQL functions that the provider supports for your data source. IF EXISTS

not supported in this situation.



However, an even more basic problem may arise. I don't believe it is possible to change the contents of a table with SQL executed from ACE OleDb.

+2


source


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 minor changes. As Alex-K pointed out, the Excel engine cannot evaluate operators if

for your. SQL Server can. But in Excel, you will have to limit your SQL language and evaluate if

yourself as shown above.

0


source







All Articles