Delving now into ADO I am looking at using the Command object to execute SQL
queries.  I have trouble getting them to work.

The following code, using the Open method of a recordset, works fine,
returning the
correct number of records (in objRs.RecordCount) from the database table in
the recordset:

       Dim objConn As ADODB.Connection
       Dim objRs As ADODB.Recordset
       Dim sqlStr As String

       Set objConn = New ADODB.Connection
       Set objRs = New ADODB.Recordset

       objConn.Provider = "Microsoft.Jet.OLEDB.4.0"
       objConn.Open "C:\Inetpub\Databases\Timecard\Tdata2.mdb"

       sqlStr = "SELECT * FROM tdata2 WHERE BADGENUM = 'R21010'"

' this open works fine, returns RecordCount with correct number ...

       objRs.Open sqlStr, objConn, adOpenStatic, adLockOptimistic

       objRs.Close
       objConn.Close
       Set objRs = Nothing
       Set objConn = Nothing

However, the following code, doing the same except using the Command Object,
returns
a record count of -1 (obviously an error indication):

       Dim objConn As ADODB.Connection
       Dim objCmd As ADODB.Command
       Dim sqlStr As String

       Set objConn = New ADODB.Connection
       Set objCmd = New ADODB.Command

       objConn.Provider = "Microsoft.Jet.OLEDB.4.0"
       objConn.Open "C:\Inetpub\Databases\Timecard\Tdata2.mdb"

       sqlStr = "SELECT * FROM tdata2 WHERE BADGENUM = 'R21010'"

       objCmd.CommandType = adCmdText
       objCmd.CommandText = sqlStr
       objCmd.ActiveConnection = objConn

' this call returns objRs with RecordCount set to -1 ...

       Set objRs = objCmd.Execute

       objRs.Close
       objConn.Close
       Set objRs = Nothing
       Set objCmd = Nothing
       Set objConn = Nothing

Can anyone tell me why this seemling simple use of the ADO Command object
does
not work as expected?  Are there some other attributes of the Command object
I need
to set up first, or what?

TIA - Regards,
Carl

On Mon, 7 Jan 2002 16:23:57 -0700, "Carl Husic"

Take a look at the "Execute Method" topic in online help. You'll see
that it returns a forward-only, read-only cursor (the default). This
type of cursor does not support the record-count property.

You should ALWAYS use the recordset.open method to open a recordset.
This gives you control over the cursor type that is returned. Reserve
Execute for action queries (queries that don't return records, such as
Insert, Delete and Update queries).

Just pass the Command object as the source argument of the Open
statement, like this:
objRs.Open  objCmd, ,adOpenStatic, adLockOptimistic

Incidently, you should tell ADO what type of command is being sent in
your Open statement. You set the CommandType of the Command object, so
you are covered there. However in your original Open statement, you
should have specified the same command type, like this:

objRs.Open sqlStr, objConn, adOpenStatic, adLockOptimistic,adCmdText

HTH,
Bob Barrows
Please reply to the newsgroup. My reply-to address is my "spam trap" and I don't check it very often.

Hi Carl,

The problem is them if you are using Execute method of
Connection or Command object to open recordset, then, by
default, it will be opened as read-only, forwar-only
cursor on server side even if you specify other settings
for youe recordset. That type of cursor does not return
actual record number. To get actual record number you need
to open keyset or static cursor. Usually all client
cursors return actual record number. To open cursor on
client side you can use Open method of recordset to use
recordset's settings or to use Execute method of
connection, but in that case you will need to specify
CursorLocation of Connection object, not recordset's

Val

object to execute SQL
objConn.Open "C:\Inetpub\Databases\Timecard\Tdata2.mdb"

objConn.Open "C:\Inetpub\Databases\Timecard\Tdata2.mdb"