?
Solved

Edit data in an ADODB recordset

Posted on 2004-08-24
10
Medium Priority
?
1,444 Views
Last Modified: 2007-12-19
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
Comment
Question by:ewhitaker
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 2
10 Comments
 
LVL 36

Expert Comment

by:SidFishes
ID: 11883119
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
 
LVL 85
ID: 11883127
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
 
LVL 36

Expert Comment

by:SidFishes
ID: 11883134
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

Author Comment

by:ewhitaker
ID: 11883409
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
 

Author Comment

by:ewhitaker
ID: 11883506
This might be harder than I thought.  I'm uping the points to 75.
0
 
LVL 36

Expert Comment

by:SidFishes
ID: 11883963
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
 

Author Comment

by:ewhitaker
ID: 11884076
Yes that is set as a reference.
0
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 300 total points
ID: 11884423
>>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
 
LVL 36

Expert Comment

by:SidFishes
ID: 11884988
oops...that's right... no .edit

but the rest should work

0
 

Author Comment

by:ewhitaker
ID: 11885151
Chalk up another one for the good folks at LSM.

Thanks guys.
0

Featured Post

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question