ADO Recordset and Connection Issues in VB6
in [Visual Basic]
|
Prev: MSFlexgrid Bug
Next: Mapi email problem (and Apology)
From: Jamal Samedov on 7 Apr 2010 04:33 Hi albertleng, just small question: where do you open connection object? -- www.coin-masters.com "albertleng" <albertleng(a)gmail.com> wrote in message news:810f7297-f95d-4108-8ee4-1f35c05b1b1e(a)r18g2000yqd.googlegroups.com... > Hi. I'm having difficulty in using ADO Recordset and Connection. > I'm using a single Connection and multiple Recordsets to process data > of different tables in a database. > > My program runs into either "Error 91 Object variable or With block > variable not set", "Error 3704 or 3705 The operation requested by the > application is not allowed if the object is closed/opened". > > I'm a bit confused whether the same Recordset can be reused for the > same connection, when to close, set to nothing and multiple > Recordsets. > > My program is quite large and i apologise for my messy code. Hence, > i'll simplify it as below. Below are the excerpts of my codes. Please > help to point out what i have done wrong and what i can do to improve > this code. > > Dim cnn As Connection > Dim Record As Recordset > Dim Record1 As Recordset > Dim Record2 As Recordset > Dim Record3 As Recordset > Dim Record4 As Recordset > > Set Record = New ADODB.Recordset > Record.CursorLocation = adUseClient > Record.CursorType = adOpenDynamic > Record.LockType = adLockOptimistic > Record.Open "Table1", cnn > > If Not (Record.EOF) And Not (Record.BOF) Then > Do Until Record.EOF > data1 = Record("var1") > ... > cnn.Execute "INSERT INTO TABLE2 (...,...,...) VALUES ('" & > data1 & "'... > Record.MoveNext > Loop > end if > Record.Close > Set Record = Nothing > > Set Record = cnn.Execute("SELECT SOMEDATA FROM TABLE2") > LOOP THROUGH THE DATA AND UPDATE TABLE2 WITH something like > cnn.Execute "UPDATE TABLE2 SET ..." > Record.Close > Set Record = Nothing > > Set Record = New ADODB.Recordset > Record.CursorLocation = adUseClient > Record.CursorType = adOpenDynamic > Record.LockType = adLockOptimistic > Record.Open "TABLE2", cnn > LOOP THROUGH THE DATA AND INSERT INTO TABLE2A and TABLE2B based on > if conditions, i.e. cnn.Execute "INSERT INTO TABLE2A(...) VALUES > (...)" > Record.Close > Set Record = Nothing > > cnn.Execute "DELETE FROM TABLE2A WHERE..." > cnn.Execute "UPDATE TABLE2A SET VAR1 =..." > cnn.Execute "UPDATE TABLE2A SET VAR2 =..." > cnn.Execute "DELETE FROM TABLE2B WHERE..." > > Set Record = New ADODB.Recordset > Record.CursorLocation = adUseClient > Record.CursorType = adOpenDynamic > Record.LockType = adLockOptimistic > Record.Open "TABLE2A", cnn > LOOP THROUGH THE DATA AND INSERT INTO TABLE3A based on > if conditions, i.e. cnn.Execute "INSERT INTO TABLE3A(...) VALUES > (...)" > Record.Close > Set Record = Nothing > > Set Record = cnn.Execute("SELECT SOMEDATA FROM TABLE2B") > LOOP THROUGH THE DATA AND UPDATE TABLE2B WITH something like > Record(TIME") = someTime > Record.Update > > Record.Close > Set Record = Nothing > > Set Record1 = New ADODB.Recordset > Set Record1 = csvSQL.Execute("SELECT SOMEDATA FROM TABLE2B WHERE > CONDITION1") > Loop the data and inside the loop, there's another loop based on > Record2, > i.e. > Set Record2 = New ADODB.Recordset > Set Record2 = csvSQL.Execute("SELECT SOMEDATA FROM TABLE2B WHERE > CONDITION2") > Inside the inner loop, an update is done, i.e. cnn.Execute("UPDATE > TABLE2B SET SOMEDATA = ...) > Record1.Close > Set Record1 = Nothing > Record2.Close > Set Record2 = Nothing > > ... > > > > > > > > > > > > > >
From: albertleng on 7 Apr 2010 08:16
Hi. My connection object is a public variable and i open it in form_load. For other questions all of you asked, i can't answer at the moment because i can't do the testing at the moment. Thanks a lot to all. :D On Apr 7, 4:33 pm, "Jamal Samedov" <jsame...(a)iface.nl> wrote: > Hi albertleng, > just small question: > where do you open connection object? > > -- > > www.coin-masters.com"albertleng" <albertl...(a)gmail.com> wrote in message > > news:810f7297-f95d-4108-8ee4-1f35c05b1b1e(a)r18g2000yqd.googlegroups.com... > > > Hi. I'm having difficulty in using ADO Recordset and Connection. > > I'm using a single Connection and multiple Recordsets to process data > > of different tables in a database. > > > My program runs into either "Error 91 Object variable or With block > > variable not set", "Error 3704 or 3705 The operation requested by the > > application is not allowed if the object is closed/opened". > > > I'm a bit confused whether the same Recordset can be reused for the > > same connection, when to close, set to nothing and multiple > > Recordsets. > > > My program is quite large and i apologise for my messy code. Hence, > > i'll simplify it as below. Below are the excerpts of my codes. Please > > help to point out what i have done wrong and what i can do to improve > > this code. > > > Dim cnn As Connection > > Dim Record As Recordset > > Dim Record1 As Recordset > > Dim Record2 As Recordset > > Dim Record3 As Recordset > > Dim Record4 As Recordset > > > Set Record = New ADODB.Recordset > > Record.CursorLocation = adUseClient > > Record.CursorType = adOpenDynamic > > Record.LockType = adLockOptimistic > > Record.Open "Table1", cnn > > > If Not (Record.EOF) And Not (Record.BOF) Then > > Do Until Record.EOF > > data1 = Record("var1") > > ... > > cnn.Execute "INSERT INTO TABLE2 (...,...,...) VALUES ('" & > > data1 & "'... > > Record.MoveNext > > Loop > > end if > > Record.Close > > Set Record = Nothing > > > Set Record = cnn.Execute("SELECT SOMEDATA FROM TABLE2") > > LOOP THROUGH THE DATA AND UPDATE TABLE2 WITH something like > > cnn.Execute "UPDATE TABLE2 SET ..." > > Record.Close > > Set Record = Nothing > > > Set Record = New ADODB.Recordset > > Record.CursorLocation = adUseClient > > Record.CursorType = adOpenDynamic > > Record.LockType = adLockOptimistic > > Record.Open "TABLE2", cnn > > LOOP THROUGH THE DATA AND INSERT INTO TABLE2A and TABLE2B based on > > if conditions, i.e. cnn.Execute "INSERT INTO TABLE2A(...) VALUES > > (...)" > > Record.Close > > Set Record = Nothing > > > cnn.Execute "DELETE FROM TABLE2A WHERE..." > > cnn.Execute "UPDATE TABLE2A SET VAR1 =..." > > cnn.Execute "UPDATE TABLE2A SET VAR2 =..." > > cnn.Execute "DELETE FROM TABLE2B WHERE..." > > > Set Record = New ADODB.Recordset > > Record.CursorLocation = adUseClient > > Record.CursorType = adOpenDynamic > > Record.LockType = adLockOptimistic > > Record.Open "TABLE2A", cnn > > LOOP THROUGH THE DATA AND INSERT INTO TABLE3A based on > > if conditions, i.e. cnn.Execute "INSERT INTO TABLE3A(...) VALUES > > (...)" > > Record.Close > > Set Record = Nothing > > > Set Record = cnn.Execute("SELECT SOMEDATA FROM TABLE2B") > > LOOP THROUGH THE DATA AND UPDATE TABLE2B WITH something like > > Record(TIME") = someTime > > Record.Update > > > Record.Close > > Set Record = Nothing > > > Set Record1 = New ADODB.Recordset > > Set Record1 = csvSQL.Execute("SELECT SOMEDATA FROM TABLE2B WHERE > > CONDITION1") > > Loop the data and inside the loop, there's another loop based on > > Record2, > > i.e. > > Set Record2 = New ADODB.Recordset > > Set Record2 = csvSQL.Execute("SELECT SOMEDATA FROM TABLE2B WHERE > > CONDITION2") > > Inside the inner loop, an update is done, i.e. cnn.Execute("UPDATE > > TABLE2B SET SOMEDATA = ...) > > Record1.Close > > Set Record1 = Nothing > > Record2.Close > > Set Record2 = Nothing > > > ... |