Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Updating A Record using ADO in VB6

Posted on 2000-03-17
Medium Priority
Last Modified: 2011-10-03
I have this little VB executable that runs on the server that uses ADO to query a recordset and then loop through to see if there is any data in the Comments field.  If there is data it needs to update the record.  The database it is updating is in Access and the field is set in Access to accept zero length strings.  I keep getting "The operation requested is not supported by the provider" every time I try to update the comment field of a record.  Please let me know why I get this error everytime I try to update it?!?!?!  

Here is my code...

Public Sub SendMail()
Dim cnLube As Connection
Dim rstEmail As Recordset
Dim strConnect As String
Dim objMail As CDONTS.NewMail
Dim strBody As String

On Error GoTo Proc_Err

        If Day(Date) = 17 Then
                Set cnLube = New ADODB.Connection
                Set rstEmail = New ADODB.Recordset
                cnLube.Provider = "Microsoft.Jet.OLEDB.3.51"
                cnLube.ConnectionString = "Data Source=C:\inetpub\wwwroot\lubeoil\MyDB_be.mdb;Jet OLEDB:database password=xxxxxxx"
                cnLube.CursorLocation = adUseServer
                rstEmail.Source = "Select * FROM tblEmailList"
                Set rstEmail.ActiveConnection = cnLube
                rstEmail.CursorType = adOpenKeyset
        End If
        If Day(Date) = 17 Then
            With rstEmail
                    Do Until .EOF
                        If Not !Comments = "" Then

                            !Comments = ""
                        End If
            End With
        End If
Set rstEmail = Nothing
Set cnLube = Nothing

Debug.Print Err.Description

End Sub

Question by:kenadelglass
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Accepted Solution

mpcapps earned 200 total points
ID: 2629840
put your .edit in before you modify and update :>)
LVL 70

Expert Comment

by:Éric Moreau
ID: 2630188

in ADO, we don't put the .Edit mtehod anymore!!!


Do this table have a primary index?

Why don't you simply use a SQL Update query? See this sample:

If Day(Date) = 17 Then
   Set cnLube = New ADODB.Connection
   cnLube.Provider = "Microsoft.Jet.OLEDB.3.51"
   cnLube.ConnectionString = "Data Source=C:\inetpub\wwwroot\lubeoil\MyDB_be.mdb;Jet OLEDB:database password=xxxxxxx"
   cnLube.CursorLocation = adUseServer

   cnLube.Execute "Update tblEmailList Set Comments = ''"                
End If

Expert Comment

ID: 2631007
The standard open method for ADO recordsets, is read only mode.
try adding rstEmail.LockType=adLockOptimistic.
Also emoreau is right, it is much quicker to use the Update SQL statement and it looks cleaner.

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 2631353

Yes the table I am updating has a primary key.  I was going to try the SQL statement you suggested but I wanted to try some ADO stuff - primarily for my own learning.

Jetforce - what could I use besides the standard rst.Open?  What does the optimistic locking have to do with whether I can update a record - Nobody is in the database when I am testing so I don't think there is an editing conflict?

LVL 70

Expert Comment

by:Éric Moreau
ID: 2631389
If the recordset is open for "Read only" (the default value) as JetForce mentionned, you can't update.
LVL 14

Expert Comment

ID: 2635150
Back to basics, in your access database is the Comments field Allow Zero Length Fields property set to Yes?

Emoreau & Jetforce: (From Above)
rstEmail.CursorType = adOpenKeyset
He's covered.


Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

721 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