Medic
10-31-2000, 04:17 PM
I know I have got to be going about this the wrong way, but cannot see the better way. I am trying to use ADO to accessa a recordset and manipulate it. I can do this many times successfully, depending on the type of manipulation, but get stuck in two distinct areas...
In situation one, I am simply trying to change the value of one field in a recordset and update it. My code is as follows:
SQL = "select * from " & "MD" & "CH" & "active where (((MDChActive.ClaimChecked) Is Null));"
Set rsActive = New ADODB.Recordset
rsActive.Open SQL, DBConnection, adOpenDynamic, adLockOptimistic
rsActive![ClaimChecked] = "Passed All Edits!"
rsActive.Update
rsActive.Close
Set rsActive = Nothing
The error I get on the rsActive.Update statement is, and you gotta love this, Run time error -2147217887(80040e21)
Errors occurred. I have tried this both inside my project and standalone. It works standalone and sometimes works inside my project.
In addition, I have found a similarly perplexing error occurs when I try to delete an entire recordset. The code for this portion of the program is:
SQL = "Select * from " & CarrierPrefix & FormPrefix & "Active where Left([ClaimChecked],5) <> 'Claim'"
Set rsActive = New ADODB.Recordset
rsActive.CursorLocation = adUseClient
rsActive.Open SQL, DBConnection, adOpenDynamic, adLockOptimistic
Do Until rsActive.EOF
rsActive.Delete
rsActive.MoveNext
Loop
rsActive.Close
When I get to the rsActive.Delete portion, I get a run time error -2147467259(80004005) Query is too complex.
Now I could spend hours arguing to the computer that there is nothing complex about deleting a recordset, but I doubt the computer would listen. I take my cues from code that I find in the helpfiles and tutorials on the web. I have very basic skills with visual basic (class one of three completed) and even less experience. I want to take this time to express my gratitude at the level of patience and expertise demonstrated on this board by most of its' users. Having completely self taught myself in this aspect of Vb (I have six reference guides, an MSDN library, and you guys), I am reliant on experience to be my teacher. The main reason I use ADO is because there are more exapmles of code for it that DAO. I am nearing completion of my first major project, and these roadblocks are in the way. I am not opposed to using DAO (ie querydef.execute), but I feel there is more to be learned in figuring out what I did wrong here. Perhaps I can return the favor to another one day.
In situation one, I am simply trying to change the value of one field in a recordset and update it. My code is as follows:
SQL = "select * from " & "MD" & "CH" & "active where (((MDChActive.ClaimChecked) Is Null));"
Set rsActive = New ADODB.Recordset
rsActive.Open SQL, DBConnection, adOpenDynamic, adLockOptimistic
rsActive![ClaimChecked] = "Passed All Edits!"
rsActive.Update
rsActive.Close
Set rsActive = Nothing
The error I get on the rsActive.Update statement is, and you gotta love this, Run time error -2147217887(80040e21)
Errors occurred. I have tried this both inside my project and standalone. It works standalone and sometimes works inside my project.
In addition, I have found a similarly perplexing error occurs when I try to delete an entire recordset. The code for this portion of the program is:
SQL = "Select * from " & CarrierPrefix & FormPrefix & "Active where Left([ClaimChecked],5) <> 'Claim'"
Set rsActive = New ADODB.Recordset
rsActive.CursorLocation = adUseClient
rsActive.Open SQL, DBConnection, adOpenDynamic, adLockOptimistic
Do Until rsActive.EOF
rsActive.Delete
rsActive.MoveNext
Loop
rsActive.Close
When I get to the rsActive.Delete portion, I get a run time error -2147467259(80004005) Query is too complex.
Now I could spend hours arguing to the computer that there is nothing complex about deleting a recordset, but I doubt the computer would listen. I take my cues from code that I find in the helpfiles and tutorials on the web. I have very basic skills with visual basic (class one of three completed) and even less experience. I want to take this time to express my gratitude at the level of patience and expertise demonstrated on this board by most of its' users. Having completely self taught myself in this aspect of Vb (I have six reference guides, an MSDN library, and you guys), I am reliant on experience to be my teacher. The main reason I use ADO is because there are more exapmles of code for it that DAO. I am nearing completion of my first major project, and these roadblocks are in the way. I am not opposed to using DAO (ie querydef.execute), but I feel there is more to be learned in figuring out what I did wrong here. Perhaps I can return the favor to another one day.