ADODB.Command object problems

[Paul Richardson]

I have split a database following the directions found in another thread here
(the back end only has the tables, the rest is in the front end, including
queries). In my VB code, I instantiate a command object and set CommandText
property to the name of a query, the CommandType property to adCmdStoredProc,
and the ActiveConnection property to an ADODB.Connection object. In
addition, I create parameter objects (using the names as shown in the SQL
View of the Query) and add them to the Parameters collection. I set the
parameters and attempt to load a recordset object with the command's Execute
method. This fails with an error that says something about a "Multi-Step
OLEDB operation generated errors" if I have defined the Connection String
with a "Provider=Microsoft.Jet.OLEDB.4.0;etc." and with an error that says
"[Microsoft][ODBC Microsoft Access Driver]Optional feature not implemented."
if the ConnectionString is defined "DSN=SilentWar" (with SilentWar set
through the ODBC Administration). I have found a KB article referring to the
first error that had a registry mod. That is not the problem here.

If I take the text of the same query and create a string variable (with the
parameter values inserted where appropriate), set the CommandText property to
the string, and the CommandType property to adCmdText, I can load the
recordset object with no problem using the Execute method. Note that I don't
instantiate/add parameter objects in this situation and I am using the same
Connection object.

What have I missed? or what am I doing wrong? Do the queries need to
reside on the Back End, as well as the tables?

TIA,
Paul

 

[Douglas J. Steele]

How about pasting the code that's failing here? Someone should be able to
identify the problem.

 

[Paul Richardson]

Here ya go:

Dim strSQL As String
Dim cmdSP As ADODB.Command
Dim theParm As ADODB.Parameter
Dim rs As ADODB.Recordset

strSQL = "SELECT AreaActivity.AAct_Value, AreaActivity.AAct_TFOnly FROM
AreaActivity "
strSQL = strSQL & "WHERE (((AreaActivity.AAct_OpAreaID)=" &
CStr(Me.cboOpArea.ItemData(Me.cboOpArea.ListIndex))
strSQL = strSQL & ") AND ((AreaActivity.AAct_DieRoll)=" & CStr(DieRoll)
strSQL = strSQL & ") AND ((AreaActivity.AAct_WarPeriod)=" &
CStr(Me.fraWarPeriod.Value) & "));"

Set cmdSP = New ADODB.Command
cmdSP.ActiveConnection = g_conn
' cmdSP.CommandText = "GetContactDensity"
' cmdSP.CommandType = adCmdStoredProc
' Set theParm = cmdSP.CreateParameter("Return", adInteger,
adParamReturnValue)
' cmdSP.Parameters.Append theParm
' Set theParm = cmdSP.CreateParameter("OpAreaID", adInteger,
adParamInput, , _
' Me.cboOpArea.ItemData(Me.cboOpArea.ListIndex))
' cmdSP.Parameters.Append theParm
' Set theParm = cmdSP.CreateParameter("DieRoll", adInteger, adParamInput,
, DieRoll)
' cmdSP.Parameters.Append theParm
' Set theParm = cmdSP.CreateParameter("WarPeriod", adInteger,
adParamInput, , Me.fraWarPeriod.Value)
' cmdSP.Parameters.Append theParm
cmdSP.CommandText = strSQL
cmdSP.CommandType = adCmdText
Set rs = cmdSP.Execute

The commented code is what is failing. The uncommented code returns the
recordset with the correct record. The string in strSQL is lifted directly
from the query named "GetContactDensity".

Here is the code to establish the connection object, although I don't see
that as the problem, since the straight text query works:

Set g_conn = New ADODB.Connection
If g_conn.State <> adStateOpen Then
If g_conn.State <> adStateClosed Then g_conn.Close
g_conn.CursorLocation = adUseClient
g_conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Documents and
Settings\Paul\My Documents\SilentWar.mdb;" & _
"User ID=Admin;" 'No password spec uses a
blank string.
' g_conn.ConnectionString = "DSN=SilentWar"
g_conn.Open
End If


Thanks for your time on this. I really should be able to chase this down,
but I think I am too close to the problem to see what is going on.

Paul

How about pasting the code that's failing here? Someone should be able to
identify the problem.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I have split a database following the directions found in another thread
here
(the back end only has the tables, the rest is in the front end, including
queries). In my VB code, I instantiate a command object and set
CommandText
property to the name of a query, the CommandType property to
adCmdStoredProc,
and the ActiveConnection property to an ADODB.Connection object. In
addition, I create parameter objects (using the names as shown in the SQL
View of the Query) and add them to the Parameters collection. I set the
parameters and attempt to load a recordset object with the command's
Execute
method. This fails with an error that says something about a "Multi-Step
OLEDB operation generated errors" if I have defined the Connection String
with a "Provider=Microsoft.Jet.OLEDB.4.0;etc." and with an error that says
"[Microsoft][ODBC Microsoft Access Driver]Optional feature not
implemented."
if the ConnectionString is defined "DSN=SilentWar" (with SilentWar set
through the ODBC Administration). I have found a KB article referring to
the
first error that had a registry mod. That is not the problem here.

If I take the text of the same query and create a string variable (with
the
parameter values inserted where appropriate), set the CommandText property
to
the string, and the CommandType property to adCmdText, I can load the
recordset object with no problem using the Execute method. Note that I
don't
instantiate/add parameter objects in this situation and I am using the
same
Connection object.

What have I missed? or what am I doing wrong? Do the queries need to
reside on the Back End, as well as the tables?

TIA,
Paul