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.
source to share
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.
source to share
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.
source to share