Solved

Edit data in an ADODB recordset

Posted on 2004-08-24
10
1,418 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
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…

746 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

12 Experts available now in Live!

Get 1:1 Help Now