By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,688 Members | 1,229 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,688 IT Pros & Developers. It's quick & easy.

ADODB.recordset does not let me edit correctly

P: n/a
Hi all,

All I am trying to do is open a table, edit it, and then close it.
But I am having the strangest error. Here is my code

Dim i as integer
Dim rs as adodb.recordset
Set rs = new ADODB.recordset
rs.open "SELECT * FROM ITEM",CurrentProject.Connection, adOpenStatic,
adLockOptimistic
rs.filter="[storageArea]='Cabinet A'"

For i = 1 To rs.RecordCount
rs("storageArea") = "Cabinet B"
rs.update
rs.MoveNext
Next

This code wokrs perfectly, if there is only one record in the
recordset. But for more than one record, it will correctly edit all
the records EXCEPT the last one.

For example, if the recordCount is 3. It will edit the first two
records, then it will tell me that it has reached EOF, and it crashes,
anyone know what I'm doing wrong??

Mark

Jun 21 '07 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Hi Mark,

Instead of using a Recordset object try using an ADODB.Command object:

Sub UpdateUsingCmd()

Dim cmd As New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection

cmd.CommandType = adCmdText
cmd.CommandText = "Update tbl1 Set fld2 = 'test' Where fld1 = 'Joe'"
cmd.Execute

End Sub

The ADODB.Command object is more reliable and efficient than a Recordset
object. Best practice is to steer away from looping when possible (it
is not always possible to get away from looping - but how you create
your loop is also another thing to look at). Loops have more parts and
create more room for error(s).
Rich

*** Sent via Developersdex http://www.developersdex.com ***
Jun 21 '07 #2

P: n/a
On Jun 21, 4:20 pm, Rich P <rpng...@aol.comwrote:
Hi Mark,

Instead of using a Recordset object try using an ADODB.Command object:

Sub UpdateUsingCmd()

Dim cmd As New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection

cmd.CommandType = adCmdText
cmd.CommandText = "Update tbl1 Set fld2 = 'test' Where fld1 = 'Joe'"
cmd.Execute

End Sub

The ADODB.Command object is more reliable and efficient than a Recordset
object. Best practice is to steer away from looping when possible (it
is not always possible to get away from looping - but how you create
your loop is also another thing to look at). Loops have more parts and
create more room for error(s).

Rich

*** Sent via Developersdexhttp://www.developersdex.com***
Thanks a lot Rich, the ADODB.Command object worked perfectly, and I've
tried to integrate it into my application whenever possible. The only
problem with it...is that it doesn't seem able to return recordset's
properly. Every time I use it to return a recordset...

Dim rs as adodb.recordset
....
Set rs = cmd.execute
....

It gives a readonly, only forward opening recordset with a recordcount
of -1!!! Is it possible to use ADODB.command to modify data??

Mark

Jun 25 '07 #3

P: n/a
On Jun 25, 9:50 am, mark_aok <mark_...@hotmail.comwrote:
On Jun 21, 4:20 pm, Rich P <rpng...@aol.comwrote:
Hi Mark,
Instead of using a Recordset object try using an ADODB.Command object:
Sub UpdateUsingCmd()
Dim cmd As New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText
cmd.CommandText = "Update tbl1 Set fld2 = 'test' Where fld1 = 'Joe'"
cmd.Execute
End Sub
The ADODB.Command object is more reliable and efficient than a Recordset
object. Best practice is to steer away from looping when possible (it
is not always possible to get away from looping - but how you create
your loop is also another thing to look at). Loops have more parts and
create more room for error(s).
Rich
*** Sent via Developersdexhttp://www.developersdex.com***

Thanks a lot Rich, the ADODB.Command object worked perfectly, and I've
tried to integrate it into my application whenever possible. The only
problem with it...is that it doesn't seem able to return recordset's
properly. Every time I use it to return a recordset...

Dim rs as adodb.recordset
...
Set rs = cmd.execute
...

It gives a readonly, only forward opening recordset with a recordcount
of -1!!! Is it possible to use ADODB.command to modify data??

Mark
1. Instead of using rs.Filter you could put the filter criteria in the
SQL itself, as in

rs.open _
"SELECT * FROM [ITEM] " & _
"WHERE [storageArea]='Cabinet A'", _
CurrentProject.Connection, _
adOpenStatic, _
adLockOptimistic
2. Instead of a For loop using rs.RecordCount you could just use

Do While Not rs.EOF
' do interesting stuff here
rs.MoveNext
Loop
3. As Rich hinted, in this case you don't need a loop at all. You just
need

CurrentProject.Connection.Execute _
"UPDATE [ITEM] SET " & _
"[storageArea]='Cabinet B' " & _
"WHERE [storageArea]='Cabinet A'"

Jun 26 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.