ADODB command inserts fail without error -- help please

[Timo]

I would be very grateful for help discovering the cause of this
mysterious problem. I'm in the dark, but maybe these clues
will turn on a lightbulb for somebody?

I have an Access 2000 ADP connected to SQL Server 2000 database.
The Access forms are working correctly; individual inserts,
updates, and deletes work. However, I have a subroutine that
inserts a group of records using an ADODBCommand, and this command
fails to insert any records, yet no error is raised! It just fails
silently.

The command's connection is based on a "trusted user" --
Integrated Security SSPI in the connection string.

APPROACH #1
The command is CommandType.Text, so after cmd.Execute is invoked,
I examine my INSERT statement in SQL Trace; when I paste the
statement into Query Analyzer and run it, the expected records DO
get inserted. So my SQL insert command is valid and well-formed,
and it is getting through to the database.

APPROACH #2
Thinking to take another approach, I use two Recordsets. I select
the records I want to insert, loop through the source recordset,
and invoke the AddNew method on the target recordset, assigning
the fields individually and invoking the target recordset's Update
method with each iteration. This approach does not insert any
records either. And it too fails silently, without error!

APPROACH #3
Thinking to take yet another approach, I rewrite the insert
command as a stored procedure, and change my command to
CommandType.StoredProcedure. Here, I DO get an error. "Could not
find stored procedure <spname>". I have granted execute on the
stored procedure to PUBLIC, and have tried prefixing the owner to
the SP name. Still get the error.

What might cause Approach #1 and #2 to fail without error?
And what should I do to get around the error in #3?

Many thanks in advance.
Timo

 

[Sylvain Lafontaine]

First, maybe a exemple of your SQL could be of some utility here; for all of
your approachs #1, #2 and #3. You should also tell us if you have other
Insert statements who are working well or if this is your first try. May
help to discriminate for some common errors.

Second, make sure that the option "Error trapping" is set to "Break on all
errors"; otherwise there may be errors in the code used to trap for errors
itself; with the effect of hidden the errors code that we need. Also, the
DAO and the ADO databases engines have their own errors messages who are
sometime more helpfull than the one displayed by VBA. Here two short exemple
for both DAO and ADO:

Dim MyError As dao.Error
MsgBox Errors.Count

For Each MyError In DBEngine.Errors
With MyError
MsgBox .Number & " " & .Description & " : " & .Source
End With
Next

Dim ErrorADO As ADODB.Error

For Each ErrorADO In CurrentProject.Connection.Errors
With ErrorADO
MsgBox .Number & " " & .Description & " : " & .Source
End With
Next

Third, make sure that you have not mixed DAO and ADO in your code.

Finally, replace Access 2000 with 2002 or 2003 and to upgrade to the last
MDAC version. Access 2000 and 2002 ADP are known to be full of bugs but
A2002 has a lot less. For A2003 RTM, I don't know as I don't have bought it
yet but my previous tests with A2003 Beta was that none of the A2002 bugs
that I known have been corrected in the A2003 beta. It's not a personnal
grievance, as I am usually able to make my way beetween them, but a
statement of fact that theses bugs are a real pain in the back for newcomers
and A2000 is really, really worse than A2002 in that regard.

S. L.