CAN'T OPEN RECORDSET AFTER CHANGING TO AND FROM A COMMAND OBJECT 1

[TNN]

As regards ADO
I am finding out that if I open a recordset with a command object then I am unable to use bookmarks with that recordset. Error message comes back "......not available from provider". If I open the same recordset with the Open method then bookmarks work fine. Does anyone know what's going on there ????
TNN, Tom
TNPAYROLL@AOL.COM

TNN (Programmer) Nov 19, 2000
I have to add to this thread.
Code first: strSQLF3 is a stored procedure

adoRsF3Screen.CursorType = adOpenStatic
adoRsF3Screen.LockType = adLockOptimistic
adoRsF3Screen.Open Source:=strSQLF3, ActiveConnection:=adoConnection, _
Options:=adCmdText

cmd.ActiveConnection = _
"Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=" & App.Path & "\BEMPLOYE.MDB"
cmd.CommandText = strSQLF3
cmd.CommandType = adCmdText
Set adoRsF3Screen = cmd.Execute

I opened the recordset first with the above Open statement. Then I changed it to open with a command object. I then changed back to open again with the Open statement. After changing it back to the open statement the recordset will not open. I get error "invalid operation".

I've done this with other recordsets with the same result. Once I've changed to open with a command object and then back to open with the Open statemant then the recordset will not open.

These are the same recordsets I am trying to use bookmarks with.

Can anyone tell me what's going on ??????
Thank You for any help.
TNN, Tom
TNPAYROLL@AOL.COM




TOM

http://WWW.TNPAYROLL@AOL.COM

 

[StormbringerV]

I can tell you whats going on, but not how to fix it...sorry. The problem is when using a command object.Execute, the default (that CAN'T be changed by the way) is Readonly, ForwardOnly Cursor. You will probably find that the recordcount property also returns a -1, which is a pain since its nice to know the number of records that your flexgrid needs. However, the rs.Open works fine unless you need to pass paramters to the SQL statement.

Dave

 

[TNN]

Hi Dave,
Not sure what you are saying. I have been using the following command object to pass SQL and am able to update the recordset just fine. If default for a command object was read only then I should not be able to update, right??
The SQL statement goes into the txt1.text textbox.

Set cmd = New ADODB.Command
cmd.ActiveConnection = _
"Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=C:\T&Ndevelop\ _
PayrollApp2.vbp\BEMPLOYE.MDB"
cmd.CommandText = txt1.Text
cmd.CommandType = adCmdText


And here I am passing a parameter to a SQL in a open statement. DisplayF3 is a stored procedure. Am I missing what your saying??

strSQLF3 = "EXECUTE DisplayF3 " & (strEmployee_Number)

adoRsF3Screen.CursorLocation = adUseClient
adoRsF3Screen.CursorType = adOpenStatic
adoRsF3Screen.LockType = adLockOptimistic
adoRsF3Screen.Open Source:=strSQLF3, ActiveConnection:=adoConnection, _
Options:=adCmdText

TNN, Tom
TNPAYROLL@AOL.COM



TOM

http://WWW.TNPAYROLL@AOL.COM

 

[TNN]

Hi Dave,
Think maybe I'm understanding now as regards the command object. Looks like you can update the recordset via SQL but not with the Upate method, ie. adoRs.Update. Am I understanding correctly ??

With the Open statement I am executing a stored procedure and passing parameters with it. That seems to work fine.

Thank You for your input.
TNN, Tom
TNPAYROLL@AOL.COM

TOM

http://WWW.TNPAYROLL@AOL.COM

 

[StormbringerV]

Hey Tom!

I think we're both talking about the same thing. What I've found is using the Set RS to command.execute works BUT it's readonly and forwardonly cursor. And the recordcount property is useless. But this is ONLY on the command.execute. A regular open works fine, and of course youcan set the cursor with it which is something you CAN'T do with command.execute. However, like you, I've found the stored procedure to work quite well.
It was good talking to you!
Dave