Solved

Edit data in an ADODB recordset

Posted on 2004-08-24
10
1,430 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

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 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

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)

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

808 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