• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1453
  • Last Modified:

Edit data in an ADODB recordset

For the life of me I cannot rember how to edit data in an ADODB recordset.  The code so far is:

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cn = CurrentProject.Connection

Set rs = New ADODB.Recordset
With rs
    Set .ActiveConnection = cn
    .Source = "SELECT tblTraining.TrainingType FROM tblTraining"
    .LockType = adLockOptimistic
    .CursorType = adOpenKeyset
    .Open
End With

'Edit recordset here.

rs.Close
cn.Close
   
0
ewhitaker
Asked:
ewhitaker
  • 4
  • 4
  • 2
1 Solution
 
SidFishesCommented:
this?

Set rs = New ADODB.Recordset
With rs
    Set .ActiveConnection = cn
    .Source = "SELECT tblTraining.TrainingType FROM tblTraining"
    .LockType = adLockOptimistic
    .CursorType = adOpenKeyset
    .Open

.edit

'do stuff

.update



End With

rs.Close
cn.Close
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Set rs = New ADODB.Recordset
With rs
    Set .ActiveConnection = cn
    .Source = "SELECT tblTraining.TrainingType FROM tblTraining"
    .LockType = adLockOptimistic
    .CursorType = adOpenKeyset
    .Open
End With

rs("FIeldName") = Value
rs("OtherFieldName")= Othervalue
rs.Update

0
 
SidFishesCommented:
oh...wait...that won't work...you're not spcifying which record to edit
...need a WHERE clause

    .Source = "SELECT tblTraining.TrainingType FROM tblTraining where ID= " & me!someID
   
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
ewhitakerAuthor Commented:
SidFishes,

That is what was throwing me.  I get an "Object or Data Member not found error when I try to use the "rs.edit" command.



LSMConsulting,

I need to edit the text.  This appears to add a new item to my list.


This is being developed in Access 2000 if that makes a difference.
0
 
ewhitakerAuthor Commented:
This might be harder than I thought.  I'm uping the points to 75.
0
 
SidFishesCommented:
have you checked to make sure you've got your ado reference set...code window/tools/references

look for Microsoft ActiveX Data Objects 2.1 library
0
 
ewhitakerAuthor Commented:
Yes that is set as a reference.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
>>That is what was throwing me.  I get an "Object or Data Member not found error when I try to use the "rs.edit" >>command

ADODB.Recordsets don't expose a .Edit property or method ... DAO does, but not ADODB.

>> LSMConsulting,
>> I need to edit the text.  This appears to add a new item to my list.

No ... the code supplied above edits the current record. if you want to add a new item to an ADODB.Recordset then you issue an .AddNew before setting the recordset values:

rs.AddNew
rs("Field1") = value1
rs("Field2") = value2
etc etc
rs.update

However, as Sid pointed out, you probably want to filter your recordsets with a WHERE clause, otherwise you have no idea which record you've returned.
0
 
SidFishesCommented:
oops...that's right... no .edit

but the rest should work

0
 
ewhitakerAuthor Commented:
Chalk up another one for the good folks at LSM.

Thanks guys.
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

  • 4
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now