Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Several sql queries with the same recordset
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 -- fbonnar ----------------------------------------------------------------------- fbonnard's Profile: http://www.excelforum.com/member.php...fo&userid=1597 View this thread: http://www.excelforum.com/showthread.php?threadid=27446 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Several sql queries with the same recordset
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. "fbonnard" wrote: Hi everybody İ need to do several queries. But i don't know how to proceed with one recordset. So i trıed to close and open a new one. (in the if statement loop) Unfortunately this trick desn't work. Does somebody has a clue about 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 -- fbonnard ------------------------------------------------------------------------ fbonnard's Profile: http://www.excelforum.com/member.php...o&userid=15977 View this thread: http://www.excelforum.com/showthread...hreadid=274467 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Several sql queries with the same recordset
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
query a recordset | Excel Discussion (Misc queries) | |||
DAO Recordset in Excel | Excel Programming | |||
Type recordset/recordset? | Excel Programming | |||
Viewing Recordset in SQL help please | Excel Programming | |||
Recordset | Excel Programming |