DAO to ADO

G

Guest

I have to following code in ADO (unrelated parts edited out, trouble line has "Trouble Here" directly above it):

Dim cnn as ADODB.Connection
Dim rs as New ADODB.Recordset
Dim strNewTable2 as String

strNewTable2 = "tblTest"

Set cnn = CurrentProject.Connection

rs.Open strNewTable2, cnn, adOpenDynamic, adLockOptimistic

Do Until rs.EOF
If Left(rs.Fields("TestField"), 2) = "zz" Then
'Trouble here
rs.Fields("TestField").Value = Right(rs.Fields("TestField").Value, (Len(rs.Fields("TestField").Value) - 2))
rs.Update
End If
Loop

rs.Close
cnn.Close

Set rs = Nothing
Set cnn = Nothing

Any ideas what I'm doing wrong? The error message is 3251, Object or provider is not capable of performing requested operation. The line worked perfectly in DAO. The value of rs.Fields("TestField") is "zzTest".

Thanks!

Jake
 
G

Guest

Looking deeper into this, I've found that for some reason the CursorType is actually opening as adOpenForward, and the LockType is adLockReadOnly...the table is updatable (it is an actual table) - any ideas these changes are overriding what I'm keying in? I'm not sure if it matters, but I used the "rs" variable earlier in the procedure - however even closing it and setting it to nothing (using debug) are having no effect.

Thanks!
Jake
 
G

Gary Miller

Jake,

I see one problem, that being that you left out the Edit
statement right where your Trouble Here line is...

rs.Edit
rs.Fields("TestField").Value =
Right(rs.Fields("TestField").Value,
(Len(rs.Fields("TestField").Value) - 2))
rs.Update

--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________
message
I have to following code in ADO (unrelated parts edited
Click to expand...
out, trouble line has "Trouble Here" directly above it):
Dim cnn as ADODB.Connection
Dim rs as New ADODB.Recordset
Dim strNewTable2 as String

strNewTable2 = "tblTest"

Set cnn = CurrentProject.Connection

rs.Open strNewTable2, cnn, adOpenDynamic, adLockOptimistic

Do Until rs.EOF
If Left(rs.Fields("TestField"), 2) = "zz" Then
'Trouble here
rs.Fields("TestField").Value =
Click to expand...
Right(rs.Fields("TestField").Value,
(Len(rs.Fields("TestField").Value) - 2))
rs.Update
End If
Loop

rs.Close
cnn.Close

Set rs = Nothing
Set cnn = Nothing

Any ideas what I'm doing wrong? The error message is
Click to expand...
3251, Object or provider is not capable of performing
requested operation. The line worked perfectly in DAO. The
value of rs.Fields("TestField") is "zzTest".
 
G

Guest

The way I understand it, ADO does not have an edit statement??

Jak

----- Gary Miller wrote: ----

Jake

I see one problem, that being that you left out the Edi
statement right where your Trouble Here line is..

rs.Edi
rs.Fields("TestField").Value
Right(rs.Fields("TestField").Value
(Len(rs.Fields("TestField").Value) - 2)
rs.Updat

-

Gary Mille
Gary Miller Computer Service
Sisters, O
_______________________
messag
I have to following code in ADO (unrelated parts edite
Click to expand...
out, trouble line has "Trouble Here" directly above it)
Dim rs as New ADODB.Recordse
Dim strNewTable2 as Strin
If Left(rs.Fields("TestField"), 2) = "zz" The
'Trouble her
rs.Fields("TestField").Value
Click to expand...
Right(rs.Fields("TestField").Value
(Len(rs.Fields("TestField").Value) - 2)
rs.Updat
End I
Loo
Click to expand...
3251, Object or provider is not capable of performin
requested operation. The line worked perfectly in DAO. Th
value of rs.Fields("TestField") is "zzTest"
 
M

Martin Seelhofer

Hey there
The way I understand it, ADO does not have an edit statement???
Click to expand...

Right, in ADO there's no Edit-thing anymore.
[...]
rs.Fields("TestField").Value =
Click to expand...
Right(rs.Fields("TestField").Value,
(Len(rs.Fields("TestField").Value) - 2))
[...]
Click to expand...

Have you tried saving the record's current value in a temporary
variable? Can you access it this way? [By the way, why not using
the compressed syntax rs("TestField").Value or simply rs("TestField")
to access the fields contents?]

E.g.

strOldVal = rs("TestField")
' What is the string's content here?
rs("TestField") = Right(strOldVal, len(strOldVal)-2)


However, I don't see a reason, why your code shouldn't work, hm...


Cheers,

Martin
 
G

Gary Miller

I'm a DAO hangout for it's simplicity and my lack of a need
for ADO with the projects I work on. Researched it and it
looks like you are correct. My comment may not be valid for
ADO.
--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________
message
 
D

Dirk Goldgar

Jake said:
I have to following code in ADO (unrelated parts edited out, trouble
line has "Trouble Here" directly above it):

Dim cnn as ADODB.Connection
Dim rs as New ADODB.Recordset
Dim strNewTable2 as String

strNewTable2 = "tblTest"

Set cnn = CurrentProject.Connection

rs.Open strNewTable2, cnn, adOpenDynamic, adLockOptimistic

Do Until rs.EOF
If Left(rs.Fields("TestField"), 2) = "zz" Then
'Trouble here
rs.Fields("TestField").Value =
Right(rs.Fields("TestField").Value,
(Len(rs.Fields("TestField").Value) - 2)) rs.Update End If
Loop

rs.Close
cnn.Close

Set rs = Nothing
Set cnn = Nothing

Any ideas what I'm doing wrong? The error message is 3251, Object or
provider is not capable of performing requested operation. The line
worked perfectly in DAO. The value of rs.Fields("TestField") is
"zzTest".

Thanks!

Jake
Click to expand...

I don't know much about ADO, but try

rs.Open strNewTable2, cnn, adOpenDynamic, adLockOptimistic,
adCmdTable
 
S

Scott McDaniel

Your cursor and locktype properties are right, i.e. they would provide an
updateable recordset, and since your running this off a table and not a
query there is no question about updateability ....

1) You haven't properly instantiated the cnn object. Try adding the New
keyword to your Dim statement, or, better yet, explicitily set it to a New
ADODB.Connection before use ... or just do away with it and use
CurrentProject.Connection in your recordset open statement.
2) If adOpenDynamic doesn't work, try adOpenKeyset (although this shouldn't
really make a difference)
2) Try removing the .Value property (although this really shouldn't make any
difference)

Error 3251 is sometimes associated with cursor locking issues. You didn't
say what type of database your using, but you may try setting the
CursorLocation = adUserClient (this is a connection object property).

--
Scott McDaniel
CS Computer Software
Visual Basic - Access - Sql Server - ASP
Jake said:
I have to following code in ADO (unrelated parts edited out, trouble line
Click to expand...
has "Trouble Here" directly above it):
Dim cnn as ADODB.Connection
Dim rs as New ADODB.Recordset
Dim strNewTable2 as String

strNewTable2 = "tblTest"

Set cnn = CurrentProject.Connection

rs.Open strNewTable2, cnn, adOpenDynamic, adLockOptimistic

Do Until rs.EOF
If Left(rs.Fields("TestField"), 2) = "zz" Then
'Trouble here
rs.Fields("TestField").Value =
Click to expand...
Right(rs.Fields("TestField").Value, (Len(rs.Fields("TestField").Value) - 2))
rs.Update
End If
Loop

rs.Close
cnn.Close

Set rs = Nothing
Set cnn = Nothing

Any ideas what I'm doing wrong? The error message is 3251, Object or
Click to expand...
provider is not capable of performing requested operation. The line worked
perfectly in DAO. The value of rs.Fields("TestField") is "zzTest".
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

ADO Access For Value List Error 4
collection corresponding to the requested name or ordinal 2
display the result 1
ADO Fields collection 1
VBA Excel - Select a value from one cell in Excel to update a SQL table 1
ADO Minus/Intersect? 3
Help!Error 3265, loading fr cbo box to combo box and then to txt b 1
traversing a large recordset 4

Top