I am trying to update a field value

[vza]

I am trying to update a field value within a table using an ADO recordset Here is my code:

' Connections to Access Database
     Dim CnAX As New ADODB.Connection
     CnAX.Provider = "Microsoft.Jet.OLEDB.4.0"
     CnAX.Open ConvertForm.AccessText.Text, "admin", ""
     CnAX.CursorLocation = adUseServer
' RecordSets
     dim rsTable As New ADODB.Recordset
' Open Recordsets
RsTable.Open "Select * From Table1", CnAX, adOpenDynamic

Do Until RsFollow.EOF = True
   RsMAX.Requery
   Value = RsMAX!MAXID + 10
   If IsNull(RsFollow!Entry_ID) Then
      ***RsFollow.Update Entry_ID, Value***
      RsFollow.MoveNext
   End If
Loop
[\code]

I recieve an error when I try to update the record - 
Object or Provideris not capable of performing requested operation.....Any suggestions?  Any responses would be greatly appreciated.

Thanks
-vza

 

[mattKnight]

forgive me for being daft...


but you are opening rsTable

and looping through rsFollow....

I suggest that you use option explicit through out your project!

Take Care

Matt
If at first you don't succeed, skydiving is not for you.

 

[zemp]

Assuming that everything is decalred and opened properly, have you tried this?

If IsNull(RsFollow!Entry_ID) Then
    RsFollow!Entry_ID = Value
    RsFollow.Update 
    RsFollow.MoveNext
End If

Take Care,

zemp

"Show me someone with both feet on the ground and I will show you someone who can't put their pants on."

 

[vza]

zemp,
I had a version of this earlier and I was still getting same error...

Thanks
-vza

 

[zemp]

Then, as Matt suggested, check all your objects to make sure that they are properly declare, set and opened. Focus on the opened part, cursor type and lock type. Where and how is rsFollow opened?

Take Care,

zemp

"Show me someone with both feet on the ground and I will show you someone who can't put their pants on."

 

[vza]

zemp,
My mistake..rsFollow was changed to rsTable...I forgot to change rsFollow to rsTable when pasting code...my apologies
I am pretty sure everything is opened correctly, I get no errors besides the update error when debugging..

Here is my corrected code:

' Variables
     Dim Value As Long

' Connections to Access Database
     Dim CnAX As New ADODB.Connection
     CnAX.Provider = "Microsoft.Jet.OLEDB.4.0"
     CnAX.Open ConvertForm.AccessText.Text, "admin", ""
     CnAX.CursorLocation = adUseServer

' RecordSets
     Dim RsTable As New ADODB.Recordset
     Dim RsMAX As New ADODB.Recordset

' Open Recordsets
RsTable.Open "Select * From Table1", CnAX, adOpenDynamic
RsMAX.Open "Select MAX(Entry_ID) As MAXID From Table1", CnAX, adOpenDynamic

Do Until RsTable.EOF = True
   RsMAX.Requery
   Value = RsMAX!MAXID
   If IsNull(RsTable!Entry_ID) Then
      RsTable.Fields("Entry_ID").Value = Value + 10
      RsTable.Update
      RsFollow.MoveNext
   End If
Loop

The error now occurs on the line previous to the update...but it is still the same error.

Thanks
-vza

 

[mattKnight]

put a break point in on the line
rsFollow.update....

also check you database, as I guess the the ID field is set. I suspect your problem is that you are not updating RSmax (the maximum ID table) each time through the loop.

Thus you are trying to update a (assumed) primary key to a non - unique value.



Take Care

Matt
If at first you don't succeed, skydiving is not for you.

 

[CCLINT]

vsa, is this an AutoNumber field? If so, then you cannot just update it.