Several sql queries with the same recordset

[fbonnard]

Hi everybody

İ need to do several queries. But i don't know how to proceed wit
one recordset.

So i trıed to close and open a new one. (in the if statemen
loop)

Unfortunately this trick desn't work. Does somebody has a clue abou
that ?

Here is a part of my code


Set rs = New ADODB.Recordset

For i = 1 To 200

myAcc = Sheets("0904").Cells(i, 1).Value

mySql1 = "SELECT account from balance where (account='" & myAcc & "')"
mySql3 = "INSERT into balance (account,04 january) VALUES ('" & myAcc
"', 200)"
mySql4 = "UPDATE balance SET [04 january] = '500' WHERE account = '"
myAcc & "'"

rs.Open mySql1, cn, adOpenStatic
Range("A" & i).Value = rs.RecordCount
Range("B" & i).CopyFromRecordset rs
rs.Close

If (Range("B" & i).Value > 0) Then
rs.Open mySql4, cn, adOpenStatic
rs.Close
Else
rs.NextRecordset mySql3, cn, adOpenStatic
rs.Close
End If

Next i

:eek

 

[AA2e72E]

Can't tell whether cn is a connection object or a connection string: assuming
it is a connection string:

set Cnn=New ADODB.Connection
Cnn.Open Cn
Cnn.Execute sql ' your SQLS starting UPDATE (does not return a record set)
Cnn.Execute sql ' your SQLS starting INSERT (does not return a record set)

Then your rs.Open statement, but use Cnn instead of cn.

 

[Stephen Bullen]

Hi Fbonnard,

But i don't know how to proceed with
one recordset.

Just to (hopefully) clarify things, you only open and close recordsets
when your SQL returns values (i.e. your SELECT statement). For the
INSERT and UPDATE, you 'Execute' the SQL using the ADO Connection
object, rather than a Recordset object.

Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk