updating a record in access 2000 via VB

[gary125]

Dear experts,

I 'm new in MS2000 programming and have problem in updating an record
"caat_chop" in a table called "testdata"

the ms2000 reply "Compile error: method or data member not found" in the code
rstd.update listed below. what can I do to update the pgm codes? pls help

thanks
Gary




Option Compare Database
Dim cncaat As ADODB.Connection
Dim rstd As New ADODB.Recordset
Option Explicit

Sub action01()
Set cncaat = CurrentProject.Connection
rstd.Open "Select * from testdata", cncaat

rstd.MoveFirst
Do Until rstd.EOF

If Left(rstd![field1], 8) = "Record (" Then
rstd.edit
![caat_chop] = ![field1]
rstd.Update
End If

rstd.MoveNext
Loop


cncaat.Close
rstd.Close

End Sub

 

[Paolo]

Hi Gary,

You must explicit the name of the recordset you refer in the line
![caat_chop] = ![field1]
in this way
rstd![caat_chop] = rstd![field1]

HTH Paolo

 

[gary125]

Dear Paolo,

thanks for the update. After the updates to the codes you suggested, the
problem " compile error: module or data member not found still persists. pls
suggest next action to do

Thanks again.....Gary

Hi Gary,

You must explicit the name of the recordset you refer in the line
![caat_chop] = ![field1]
in this way
rstd![caat_chop] = rstd![field1]

HTH Paolo

Dear experts,

[quoted text clipped - 32 lines]

End Sub

 

[Paolo]

Hullo again,
I did little corrections to your code and now it works

Option Compare Database
Dim cncaat As ADODB.Connection
Dim rstd As New ADODB.Recordset
Option Explicit

Sub action01()
Set cncaat = CurrentProject.Connection
rstd.Open "Select * from testdata", cncaat, adOpenKeyset, adLockOptimistic

rstd.MoveFirst
Do Until rstd.EOF

If Left(rstd![field1], 8) = "Record (" Then

rstd![caat_chop] = rstd![field1]
rstd.Update
End If

rstd.MoveNext
Loop
end sub

HTH Paolo

Dear Paolo,

thanks for the update. After the updates to the codes you suggested, the
problem " compile error: module or data member not found still persists. pls
suggest next action to do

Thanks again.....Gary

Hi Gary,

You must explicit the name of the recordset you refer in the line
![caat_chop] = ![field1]
in this way
rstd![caat_chop] = rstd![field1]

HTH Paolo

Dear experts,

[quoted text clipped - 32 lines]

End Sub

 

[gary125 via AccessMonster.com]

Dear Paulo and other experts,

Thanks for the upadte last time and the code works! However, there is another
problem that only ~5% of my table being updated because the VB code makes the
MDB exceed the 2GB limit of the MS Access 2000.

Is there any other suppliementary codes needed so as to prevent the “overrunâ€
of the Ms access 2000?

Here below the basic info of my data set:
-4 data fields, namely “Id†( auto-number field created by Access as the
source data is a text file), “field1â€, “field2" , “caat_chopâ€
-the caat_chop is empty before the update, while the remaining 3 fields have
values ( all are text fields except the auto number field)
- total number of rows : ~5M rows, the access mdb file is only around ~ 330Mb
in size. therefore, I think the update of 1 more text field should not exceed
the 2GB limit of access 2000.
-

my requirement of update:
( for all of the rows in the table)
if left(field1,8) = “record (â€, update “caat_chop†= field1
else update caat_chop = (last row’s) “caat_chopâ€


my updated VB codes are as below:

Option Compare Database
Dim cncaat As ADODB.Connection
Dim rstd As New ADODB.Recordset
Dim buf As String
Option Explicit


Sub action01()

Set cncaat = CurrentProject.Connection

rstd.Open "Select * from simple_Part3", cncaat, adOpenKeyset,
adLockOptimistic

rstd.MoveFirst
Do Until rstd.EOF
If Left(rstd![field1], 8) = "Record (" Then
rstd![caat_chop] = rstd![field1]
buf = rstd![caat_chop]

rstd.Update
Else

rstd![caat_chop] = buf
rstd.Update

End If
rstd.MoveNext
Loop

End Sub


---- thanks your advice again and happy new year

Hullo again,
I did little corrections to your code and now it works

Option Compare Database
Dim cncaat As ADODB.Connection
Dim rstd As New ADODB.Recordset
Option Explicit

Sub action01()
Set cncaat = CurrentProject.Connection
rstd.Open "Select * from testdata", cncaat, adOpenKeyset, adLockOptimistic

rstd.MoveFirst
Do Until rstd.EOF

If Left(rstd![field1], 8) = "Record (" Then

rstd![caat_chop] = rstd![field1]
rstd.Update
End If

rstd.MoveNext
Loop
end sub

HTH Paolo

Dear Paolo,

[quoted text clipped - 18 lines]

 

[Alex Dybenko]

Hi,
check this thread:
http://groups.google.com/group/micr...7fc81a2aec5/63de20ad864746e0#63de20ad864746e0


--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

Dear Paulo and other experts,

Thanks for the upadte last time and the code works! However, there is
another
problem that only ~5% of my table being updated because the VB code makes
the
MDB exceed the 2GB limit of the MS Access 2000.

Is there any other suppliementary codes needed so as to prevent the
“overrunâ€
of the Ms access 2000?

Here below the basic info of my data set:
-4 data fields, namely “Id†( auto-number field created by Access as the
source data is a text file), “field1â€, “field2" , “caat_chopâ€
-the caat_chop is empty before the update, while the remaining 3 fields
have
values ( all are text fields except the auto number field)
- total number of rows : ~5M rows, the access mdb file is only around ~
330Mb
in size. therefore, I think the update of 1 more text field should not
exceed
the 2GB limit of access 2000.
-

my requirement of update:
( for all of the rows in the table)
if left(field1,8) = “record (â€, update “caat_chop†= field1
else update caat_chop = (last row’s) “caat_chopâ€


my updated VB codes are as below:

Option Compare Database
Dim cncaat As ADODB.Connection
Dim rstd As New ADODB.Recordset
Dim buf As String
Option Explicit


Sub action01()

Set cncaat = CurrentProject.Connection

rstd.Open "Select * from simple_Part3", cncaat, adOpenKeyset,
adLockOptimistic

rstd.MoveFirst
Do Until rstd.EOF
If Left(rstd![field1], 8) = "Record (" Then
rstd![caat_chop] = rstd![field1]
buf = rstd![caat_chop]

rstd.Update
Else

rstd![caat_chop] = buf
rstd.Update

End If
rstd.MoveNext
Loop

End Sub


---- thanks your advice again and happy new year

Hullo again,
I did little corrections to your code and now it works

Option Compare Database
Dim cncaat As ADODB.Connection
Dim rstd As New ADODB.Recordset
Option Explicit

Sub action01()
Set cncaat = CurrentProject.Connection
rstd.Open "Select * from testdata", cncaat, adOpenKeyset, adLockOptimistic

rstd.MoveFirst
Do Until rstd.EOF

If Left(rstd![field1], 8) = "Record (" Then

rstd![caat_chop] = rstd![field1]
rstd.Update
End If

rstd.MoveNext
Loop
end sub

HTH Paolo

Dear Paolo,

[quoted text clipped - 18 lines]

End Sub

 

[John Spencer]

First problem I see is that your record set has no defined order so the
"previous" record is not really known. If you are relying on the
autonumber field to specify an order then you must use that in an order
by clause if you really want to be sure.

To set all the field that should be set to field1 I would use

UPDATE simple_Part3
SET caat_chop = Field1
WHERE Field1 Like "record (*"

To set the other value you might try the following which will be slow
UPDATE simple_Part3
SET caat_chop = DLookup("caat_Chop","Simple_Part3","ID =" &
DMax("ID","Simple_Part3","ID<" & ID))
WHERE Field1 NOT Like "record (*"

IF the ID numbers are sequential with no gaps that can be shortened to
UPDATE simple_Part3
SET caat_chop = DLookup("caat_Chop","Simple_Part3","ID =" & ID +1)
WHERE Field1 NOT Like "record (*"

And it is even possible that you could join the table to itself for the
update.


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Dear Paulo and other experts,

Thanks for the upadte last time and the code works! However, there is another
problem that only ~5% of my table being updated because the VB code makes the
MDB exceed the 2GB limit of the MS Access 2000.

Is there any other suppliementary codes needed so as to prevent the “overrunâ€
of the Ms access 2000?

Here below the basic info of my data set:
-4 data fields, namely “Id†( auto-number field created by Access as the
source data is a text file), “field1â€, “field2" , “caat_chopâ€
-the caat_chop is empty before the update, while the remaining 3 fields have
values ( all are text fields except the auto number field)
- total number of rows : ~5M rows, the access mdb file is only around ~ 330Mb
in size. therefore, I think the update of 1 more text field should not exceed
the 2GB limit of access 2000.
-

my requirement of update:
( for all of the rows in the table)
if left(field1,8) = “record (â€, update “caat_chop†= field1
else update caat_chop = (last row’s) “caat_chopâ€


my updated VB codes are as below:

Option Compare Database
Dim cncaat As ADODB.Connection
Dim rstd As New ADODB.Recordset
Dim buf As String
Option Explicit


Sub action01()

Set cncaat = CurrentProject.Connection

rstd.Open "Select * from simple_Part3", cncaat, adOpenKeyset,
adLockOptimistic

rstd.MoveFirst
Do Until rstd.EOF
If Left(rstd![field1], 8) = "Record (" Then
rstd![caat_chop] = rstd![field1]
buf = rstd![caat_chop]

rstd.Update
Else

rstd![caat_chop] = buf
rstd.Update

End If
rstd.MoveNext
Loop

End Sub


---- thanks your advice again and happy new year

Hullo again,
I did little corrections to your code and now it works

Option Compare Database
Dim cncaat As ADODB.Connection
Dim rstd As New ADODB.Recordset
Option Explicit

Sub action01()
Set cncaat = CurrentProject.Connection
rstd.Open "Select * from testdata", cncaat, adOpenKeyset, adLockOptimistic

rstd.MoveFirst
Do Until rstd.EOF

If Left(rstd![field1], 8) = "Record (" Then

rstd![caat_chop] = rstd![field1]
rstd.Update
End If

rstd.MoveNext
Loop
end sub

HTH Paolo

Dear Paolo,

[quoted text clipped - 18 lines]

End Sub