How can I change IMPLICIT TRANSACTIONS ON

a)
  Just change the Registry Key:
    HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\<DSN-Name>\UseProcForPrepare to "No"

b)
  Issue the SQL-Command
   "SET IMPLICIT_TRANSACTIONS ON" via SQLExecDirect Call.
 
  This means that the SQL-Server will automatically use transactions for the Commands
    fetch,delete,insert,create,alter table,open,grant,revoke
    drop,truncate table,select,update
  (see Transact SQL-Help about the SET-Statement)

Thank you again CONNEX. Your answer is quite good but I still do not know which is the relation between the IMPLICIT TRANSACTIONS ON or OFF and the "Generate Stored Procedure for Prepared Statement".

I tried to run the program with the "Generate Stored Procedure" checkbox enabled in the Control Panel and the IMPLICIT TRANSACTIONS ON (activated using CDatabase::ExecuteSQL("SET IMPLICIT_TRANSACTIONS ON") after the CDatabase::Open() call), and the program hung in the next CRecordset::Open() call. I do not know why... On the other hand, it runs ok when I disable the checkbox in the Control Panel.

Hmm works fine with ,my application ...
What exactly are you doing inbetween the calls?
Do you commit your transactions? or are you using autocommit?

Oops forgot that part .)
There is quite a large article about that in the Technical articles about ODBC. Maybe you have a look at the MS Support pages just search for "implicit and transaction and odbc and stored and procedure"
It's too huge to post it here

Generate stored procedures for prepared statement has nothing to do with implicit transactions setting.
Generate... option is used by JET to optimize repeated parameter queries. It is quicker to call prepared stored procedure with a parameter, then do "Select .. where parameter_criteria"

Implicit transactions on means, that each statement issued agains  t SQL server database is automatically commited.
Implicit transaction off means, that you have to begin and commit transaction explicitly.

Thank you CONNEX. The article you suggested was all what I needed. In fact, the two articles,
http://support.microsoft.com/support/kb/articles/Q135/5/32.asp and
http://support.microsoft.com/support/kb/articles/q151/5/36.asp
were very interesting. They explain the new configuration options for SQL Server 6.5 which are not included in the SQL Server documentation nor in the Visual C++ 5.0 help.

The only you need is to copy the odbcss.h file from the SQL Server 6.5 Service Pack 3 and then you are able to use the following instructions,
SQLSetConnectOption(hdbc, SQL_USE_PROCEDURE_FOR_PREPARE,
SQL_UP_ON_DROP)
SQLSetConnectOption(hdbc, SQL_AUTOCOMMIT, SQL_AUTOCOMMIT_ON)

It seems to work fine. The first instruction makes SQL Server to delete the temporary stored procedures it creates on each Prepare... wow! I am currently trying SQL_AUTOCOMMIT_OFF in order to compare the efficiency.

I also want to thank you SPIRIDONOV for your help, though I think that the points of this question must go to CONNEX as he gave me the clue. Hope you understand.