ADO Update Efficiency

Hi All,

I tried my luck in the Access forum and I've search the web and MSDN for an answer with little luck.

Simply, is it better to update a table via an UPDATE query or Recordset manipulation?

I have read that if you were to update 10,000 records an UPDATE query is more efficient (obviously), but does that transend down to say 1 - 10 updates?

i.e. There are six unique updates I want to make to 6 different rows. Should I code the backend VB to execute 6 different queries or seek and update a recordset?

It's a MS Access XP app with ADO 2.8.

My gut feeling on this is that making 6 update queries is more efficient, both with system resources and record-locking issues; I'd just like another opinion on the matter.

I appreciate your help!
Thanks,
WarrenHow about this...

store the keys and values in an access table and join the sql server table to it and perform the update...

UPDATE s
SET Col1 = a.Col1
FROM SQLTable s INNER JOIN AccessTable a
ON s.key = a.key|||Hey Brett,

While that would be efficient, however the data is being populated via a form. To update a "search table" then run the query would include extra transactions: 6 queries/seeks to update the search table then another query to update the main table.

I decided on this:

'con = open connection
Con.Execute "UPDATE ...", , adExecuteNoRecords

I felt this would be more efficient than

'rs open with appropriate connection; seek and index support
rs.Index = "PrimaryKey"
rs.seek "pkval1", "pkval2", adSeekFirstEQ
rs!val1 = newval1
rs!val2 = newval2
rs.update

I really can't find any documentation benchmarking the efficiency of ADO updates; it just doesn't seem to be out there.

-Warren

ADO Error codes...

Hi,

I have another question about ADO (using C++). I have searched the MSDN but I haven't found a answer to my question (maybe I'm just too stupid)... If I make a connection to my SQL Server there may occur some errors, but how to find out what went wrong ?

In terms of code I have e.g. something like that:
try
{
connection->Open(ConnectionString,Username,Password,ADODB::adConnectUnspecified);
...
}
catch (_com_error& e)
{
long numErrors = connection->Errors->Count;
for (long i=0; i<numErrors; i++)
{
ADODB::ErrorPtr pErr = connection->Errors->GetItem(i);
.....
}
}

Now I could get the error number by pErr->GetNumber().
But with which symbolic constant has this to be compared to find out which error occured ? I didn't find any...Or is there another better way to do this ?

P.S. I am using SQL Server 2005 Express

Take a look at this link for various ADO Error codes

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdconadoerrorreference.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdcsterrorvalueenum.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdconadoerrors.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adosql/adoprg03_59pr.asp

Hope this helps

|||Hi,

yes it helps a little. But I saw this error collection before, and I wondererd how to check if e.g. the authenticaton was wrong. I didn't saw any symbolic constant for this. That's the reason why I posted here...|||

Download MDAC 2.8 SDK and you should see oledberr.h which should have the symbolic constants like "DB_SEC_E_AUTH_FAILED" (authentication failed) etc;

MDAC 28 SDK link - http://www.microsoft.com/downloads/details.aspx?familyid=5067faf8-0db4-429a-b502-de4329c8c850&displaylang=en

Also take a look at this link and download Oledberr.exe - http://support.microsoft.com/default.aspx?scid=kb;EN-US;q168354&GSSNB=1

Hope this helps

|||Ah that's exactly what I've searched for. Thanks a lot.
I tested it by comparing this constants to the exceptions Error-value and it worked :)
I hope this is the right way

P.S. The second link doesn't work for me, but I think it's not that important. The first link was that what I was searching for.