Invalid SQL statement; error when using IF EXISTS() in SQL statement, OLEDB MS ACCESS

Question

I'm trying to build an SQL statement that will insert or update a row in the database depending on whether it exists in the table already. This format seems to work when I interface to an MSSQL server, but does not work when I use an OLEDB connection to MS Access.

IF EXISTS(select * from Resistors where ID = 2816)
    update Resistors set
    [Part Number]='1234'
    where ID = 2816
else
    insert into Resistors (
    [Part Number]
    )
    values(
    '1234'
    )
;

I have validated these two sql commands using the OLEDB connection to MS Access. They work properly but now with the IF EXISTS() portion of the command.

update Resistors set
[Part Number]='1234'
where ID = 2816


insert into Resistors (
[Part Number]
)
values(
'1234'
)

The IF EXISTS() statement appears to match other documentation I have seen on the internet but I must be doing something wrong.

This is the error I receive when I run the command.

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

This is my connection string

connection.ConnectionString = "Provider = Microsoft Office 12.0 Access Database Engine OLE DB Provider; Data source = " + _database_path;

Is the exists command not supported by this connection interface? Is there a version that supports the exists command?

The SQL you can send to an MS SQL Server differ quite a bit to the commands you can send to Access. They are different engines. Are these link tables inside Access? Also I'm not seeing your If Exists() code here? — JNevill
– JNevill, Commented Oct 29, 2019 at 19:26
Where can I find a list of the supported commands that are supported. I read that the OLEDB connection makes an environment that supports SQL commands and interfaces to the MS Access database. I've also read that the "EXISTS" command is a valid SQL command. So I just don't understand where the limit is for the OLEDB connection are. — Eric Minnaert
– Eric Minnaert, Commented Oct 29, 2019 at 19:48
I don't think these are link tables. They are each independent tables, each with it's own primary key. — Eric Minnaert
– Eric Minnaert, Commented Oct 29, 2019 at 20:05
There's no such thing as just a blanket "SQL" command that you can guarantee works across all RDBMS's. There is ANSI SQL and then there is each RDBMS's interpretation of that standard. Some of them deviate wildly (like MySQL, for instance) and some of them hold pretty close to the standard. You have to write SQL that your engine supports. It's own dialect. I believe you are trying to do a MERGE/UPSERT here and it's not something that is terribly simple in Access, which is a pretty stripped down engine compared to others (ms sql server for example). — JNevill
– JNevill, Commented Oct 29, 2019 at 20:31
I implemented it by doing two queries and executing code in-between them. I was hoping to avoid that. It's not a big deal though. — Eric Minnaert
– Eric Minnaert, Commented Mar 18, 2020 at 19:36

Ibrahim · Accepted Answer · 2019-10-29 23:04:28Z

2

Unfortunately, ifexists() is not supported in MS-Access sql.

you can use select count() as a work around, if returns 0 then it is not exist.

...
string cmdstr = "SELECT COUNT(*) FROM Resistors where ID = 2816";
....
int count = (int)command.ExecuteScalar();
....
if (count=0)
....

I hope this may help

edited Oct 29, 2019 at 23:04

answered Oct 29, 2019 at 22:23

Ibrahim

716 bronze badges

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

Collectives™ on Stack Overflow

Invalid SQL statement; error when using IF EXISTS() in SQL statement, OLEDB MS ACCESS

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related