I am using ADODB for connecting ms-access database from a vc++ application.I want to insert record in access database;but if the record already exists in the access table then update query should run instead of insert query.
the above task can easily be done with stored procedure, but since ms-access does not provide facility of stored procedure I have to find some solution in vc++ code.
One solution could be to run "select query" before "insert/update query" to check existence of the record being inserted/updated. But this will take lot of time for code to execute for every record.
does anybody have any other elegant solution?please help...

Thanks in advance
pdwivedi

I haven't tried it in c++ but can't you do it something like you would in a stored procedure?

if exists(select * from table where name = "John Doe") update table ... else insert table ...

I know the above works with Sybase database stored procedures. Since each databases has its own flavor of SQL you will have to test that to see of MS-Accesses recognises that exists keyword.

I haven't tried it in c++ but can't you do it something like you would in a stored procedure?

if exists(select * from table where name = "John Doe") update table ... else insert table ...

I know the above works with Sybase database stored procedures. Since each databases has its own flavor of SQL you will have to test that to see of MS-Accesses recognises that exists keyword.

Thanks Ancient Dragon for the solution.Sorry for replying you late.the problem with above solution is that,I insert thousands of records in the table and there are only few records which need to be updated,and to run select query for every record being inserted will make the process cumbersome. any other suggestion..?

You could just simply do an insert then if it fails do an update.

You could just simply do an insert then if it fails do an update.

that's what i did.I tried to catch the sql error .I am able to catch the com component error but don't know how to extract sql error code for insert failure something like this..

try
{
 //insert
}
catch(exception)
{
if(exception_code==sql_errorcode)
{
//update
}
else
return
}
}
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.