Disconnected Recordset using Command AddNew

I want to make ADODB disconnected recordset using Command object(Set rS = Command.Execute) and then apply changes, add new records to recordset (Add New) and most important update Batch these changes.
I get "Provider doesn't support ..." when setting LockType to LockBatchOptimistic or calling Rs.AddNew
I set Cursor location to Use Client and
else but still doesn' work ...
Who is Participating?
gwambConnect With a Mentor Commented:
Ok. So I'm locking the question.
When the LockType is LocaBatchOptimistic,the CursorType is adOpenStatic,so you can not change any record.

You should use

DamnAuthor Commented:
Sorry vaqq I dont quite get it.
Problem is not disconnected recordset, it can update database, I want to get it using command object because I want to use command type property , parameters etc...
Do you know how can I get diconnected recordset using command _
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Pls see the following example:

Set a = New Connection
Set b = New Command

With a
  .Provider = "sqloledb"
  .ConnectionString = "user id=sa;password=;data source=test;initial catalog=student"
End With


On Error GoTo LocalErr
'not you can use command
   b.Execute "select * from namelist"


DamnAuthor Commented:
OK vaqq, I'm sorry but I don't see any recordset in your example and I need data and not only to pass SQL to server.
The point here is disconnected recordset.
(One you get with Set Rs.ActiveConnection = Nothing)
I will have to reject you now, so I could unlock question, but if you think you now understand question feel free to try again or post comment ....

For example u can create an Recordset connected to SQL server.
And with metod Recordset.Save u can save this recordset to disk.
With metod Recordset.Open, u can load this Recordset from disk.

Another example:
Dim rcTemp As New ADODB.Recordset

rcTemp.Fields.Append "Name", adVarChar, 100
rcTemp.Fields.Append "Age", adInteger


rcTemp.Fields![Name] = "Michal"
rcTemp.Fields![Age] = 25

rcTemp.Fields![Name] = "Orlando"
rcTemp.Fields![Age] = 28

rcTemp.Fields![Name] = "Henry"
rcTemp.Fields![Age] = 22

rcTemp.Save "c:\temp\rcTemp.xml", adPersistXML

Set rcTemp = Nothing

DamnAuthor Commented:
qwamb, you propose that I get recordset with command, then save it to disk and later open it ?
Or manually fill new recordset from recordset obtained through command ?
I'm aware of this but it's
I guess it could work but isn't there a way to disconnect recordset obtained through command ?
If you are shure that there isn't and you can prove it, I'm ready to grade your answer and give you points...
Yes, there are two ways how to create unbound recordset with command.
1. Save to disk and open it
2. Manually fill new recordset.

I'm sorry, I don't know about another solution.

U can't use Recordset.Clone method!

U can't set connection to NOTHING of an opened recordset.

DamnAuthor Commented:
I will reject your answer for now.
If nobody gives another solution I will give you points.
Let someone else try but if nobody gets anything better untill tommorow, please lock question again and i'll give you points.
Set rS = Command.Execute

This is the problem.

You create a default recordset.  any time when you create a default recordset the cursor is set to adOpenforwardOnly and the locktype is set to adLockReadOnly.

You cannot change those settings.

So you have to find ways around it if you insist using command.execute.  The way to do this is to creat a new rich recordset and copy the data from the default recordset.

qwamb provides a bypass method which gives you a rich recordset, that is why you can do all the updates, etc.  I would use the command.execute if all you need is read only data which is 8 times faster than the rich recordset that you create using new.  If you want updates, you should to use the rich recordset.

Hope this clear things up.
DamnAuthor Commented:
this is same as qwamb-s .
I am going ti give him points if he locks question
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.