Updating ADODB.Recordset

[mansii]

Hi All!

I am connecting to an Access Database using DSNLess connection string. Then I tried to update the recordset (or should I say table?) through these codes:

Dim oConn As ADODB.Connection
dim localKB as ADODB.Recordset

Set oConn = New ADODB.Connection
oConn.Open "Driver={Microsoft Access Driver (*.mdb)};" & _
           "Dbq=" & App.Path & "\myData.mdb;" & _
           "Uid=admin;" & _
           "Pwd="

Set localKB = New ADODB.Recordset

With localKB
    .ActiveConnection = oConn
    .Source = "Select * from S_KB"
    .LockType = adLockOptimistic
    .CursorLocation = adUseClient
    .Open
End With

Dim rsTemp as ADODB.Recordset
Set rsTemp = localKB.Clone

For j = 0 To localKB.RecordCount - 1
    FlagKB = " "
    xDocID = localKB!docID
            
    theFilter = "KB_dokid ='" & xDocID & "'"
    rsTemp.Filter = theFilter

    If rsTemp.RecordCount > 1 Then
        FlagKB = "4"
	rsTemp.Filter = adFilterNone
    Else
	FlagKB = " "
    End If
    
    localKB!Flag = FlagKB

    If Not localKB.EOF Then
       	[b]localKB.MoveNext[/b]
    Else
	Exit For
    End If
Next j

then an error message appears:

[Microsoft][ODBC Microsoft Access Driver] Query is too complex.

highlighting the bold line in the debug window.
What did I do wrong here?

TIA
mansii

 

[Dashley]

If you just tryiong to update the access table try this.
hope it helps

dim constr as string
dim oconn as adodb.connection

set conn = new adodb.connection
connstr="Connection string"

conn.open connstr

Sqlin ='Whatever your trying to put in"

conn.begintrans
conn.execute SQLIN
conn.committrans

 

[mansii]

Hi mate,
Thank's for replying. I didn't think that I made my question clear. What I'm trying to do is updating the recordset then continue looping through it.

Thank's

 

[MarkSweetland]

I think what Dashley was getting at is maybe to try and modify the table using an update statement rather than looping through the recordset.

Example (not tested, forgive me if it doesn't work..it's late)

update S_KB
set flag = '4'
where exists (
select KB_dokid, count(*) from S_KB tbl2
group by KB_dokid
where S_KB.KB_dokid = tbl2.dokid
having count(*) > 1
)


Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[mansii]

Mark,
Since it's not tested, let me work around your codes first. I'll get back to you with the result.

Thank's
mansii