473,323 Members | 1,560 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,323 software developers and data experts.

ADODB.recordset does not let me edit correctly

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
3 6058
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Tom Petersen | last post by:
I have been copying and pasting code and this has been working, but now I get this error: Error Type: ADODB.Recordset (0x800A0E7D) Operation is not allowed on an object referencing a closed or...
0
by: elcc1958 | last post by:
I need to support a VB6 application that will be receiving disconnected ADODB.Recordset from out DotNet solution. Our dotnet solution deals with System.Data.DataTable. I need to populate a...
5
by: Simone | last post by:
Hello I hope you guys can help me. I am very new to ADO... I am creating a ADODB connection in a module and trying to access it from a command button in a form. Function fxEIDAssgn(plngEID As...
0
by: CFW | last post by:
I thought this was going to be easy but I'm missing something . . . I need to open an ADODB recordset using the recordset source for a list box on my for. When my form opens, the list box ADODB...
2
by: barret bonden | last post by:
(closest newsgroup I could find) Error Type: ADODB.Recordset (0x800A0CB3) Current Recordset does not support updating. This may be a limitation of the provider, or of the selected locktype....
5
by: Hexman | last post by:
I've come up with an error which the solution eludes me. I get the error: >An unhandled exception of type 'System.Runtime.InteropServices.COMException' occurred in HRTest.exe > >Additional...
0
by: PCroser | last post by:
I have encountered a problem when querying a table. The query passed data into a recordset which should have resulted in many records but has returned EOF. After debugging the code the only...
6
by: Oko | last post by:
I'm currently developing an MS Access Data Project (.adp) in MS Access 2002. One of the reports within the DB uses data that is Dynamic and cannot be stored on the SQL Server. To resolve this, I...
6
by: avcitamer | last post by:
We upgraded our system and problem below occured, pleas help me... Windows 2003 server SP1 When I set a decimal DB field value to "123,32" (using ADODB.recordset ) updated value was ok (123.32)...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.