Re: Returning a single record: is adExecuteRecord a mistake in doc

Stephen,
Thanks so much for your detailed reply!

I have tried the method you described (opening the Record directly using the
Open), and have tried it with various options, including setting up a
Command.

> What you do is (alternative method)
>
> recTemp.Open(connVantage, other parameters)
>
> making sure that the connVantage connection is setup and recTemp's
> connection IS NOT.

Unfortunately, I'm still not able to open a Record. I carefully used the
debugger to verify I had an open Connection and the Record itself was a new
object (having no connection info) prior to the call to its Open method. I
checked the Err object every step of the way and then also verified the
Errors collection (ADO stores multiple errors/informational messages there
and sets the main Err.Number to non-zero).
I've carefully documented all my attempts, with their associated errors.
I've about decided the Provider is not going to give. But before I let it
get the best of me, I'll drop my attempts here and see if you (or any other
interested party) has any ideas. Again, I appreciate your prompt assistance
and willingness to help! Thanks,
Suz

Here's my many attempts:

Dim sSql As String
Dim rsCustInfo As ADODB.Recordset
Dim recCustInfo As ADODB.Record
Dim comCustInfo As ADODB.Command
Dim conn as ADODB.Connection

Call vDB_openConnection(conn)

sSql = "SELECT Name, CustNum FROM PUB.Customer WHERE CustID = " &
CustomerID

' Use a Connection object to execute a SQL query - THIS WORKS!!!
Set rsCustInfo = conn.Execute(sSql, , adCmdText)

Set recCustInfo = New ADODB.Record
Set comCustInfo = New ADODB.Command
comCustInfo.ActiveConnection = conn
comCustInfo.CommandText = sSql
comCustInfo.CommandType = adCmdText

' Use a connection to execute a SQL query - Returns Err.Number=13 ->
' "Type Mismatch" (either way you specify the options)
' Error.Source = PricingPrg (my executable)
'Set recCustInfo = conn.Execute(sSql, , adCmdText)
'Set recCustInfo = conn.Execute(sSql, , adCmdText And adExecuteRecord)

'Use a connection with the adExecuteRecord option - Returns Err.Number ->
' "Arguments are of the wrong type, are out of the acceptable range,
' or are in conflict with one another" (probably because
adExecuteRecord
' causes the Connection to expect a Command instead of SQL string)
' Error.Source = ADODB.Connection
'Set recCustInfo = conn.Execute(sSql, , adExecuteRecord)

' Open the Record object with a SQL string - Returns Err.Number= 3251 ->
' "Object or provider is not capable of performing requested
operation"
' Error.Source = ADODB.Command or ADODB.Record depending on which
line below you try
'Set recCustInfo = comCustInfo.Execute(, , adExecuteRecord) ' NOTICE:
there's no "Type Mismatch" error here
'recCustInfo.Open sSql, conn, adModeRead, adCreateNonCollection,
adOpenExecuteCommand
'recCustInfo.Open comCustInfo, , adModeRead, adCreateNonCollection,
adOpenExecuteCommand


.

Relevant Pages

  • Re: Returning a single record: is adExecuteRecord a mistake in doc
    ... Thanks, Andy! ... >>> making sure that the connVantage connection is setup and recTemp's ... >> Dim rsCustInfo As ADODB.Recordset ... >> Dim conn as ADODB.Connection ...
    (microsoft.public.data.ado)
  • Re: excel to sql server (frequently)
    ... "Password=myPassword"Sub UpdateSQLDim conn As ... ADODB.ConnectionDim rst as ADODB.RecordsetDim i as Integerdb = ... So we can buid the connection string correctly. ... Dim connection As String ...
    (microsoft.public.excel.programming)
  • Problems INSERTing into MS Access from VB.NET...
    ... I would not expect this because I am creating new connection ... Dim ds As DataSet ... Dim cmd As OleDbCommand ... Dim conn As New OleDbConnection ...
    (microsoft.public.dotnet.general)
  • Re: Updating Access data using SQL / refresh time question
    ... As a test today, for one poarticular recordset, I changed from DAO to ADO to ... > I forgot to mention that the expense of repeated connection open and close ... > dim oconn as new adodb.connection ... > 'Now load the listview by looping through each RS row ...
    (microsoft.public.vb.database)
  • Re: Updating Access data using SQL / refresh time question
    ... > I forgot to mention that the expense of repeated connection open and close ... > dim oconn as new adodb.connection ... > 'Now load the listview by looping through each RS row ... > ' Set the recordset object each time we load the listview ...
    (microsoft.public.vb.database)