Solved

Edit data in an ADODB recordset

Posted on 2004-08-24
10
1,423 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
  • 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 84
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
 

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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 75 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

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.

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

910 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now