Prev: MSFlexgrid Bug
Next: Mapi email problem (and Apology)
From: albertleng on 6 Apr 2010 13:57
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: MikeD on 6 Apr 2010 15:06


"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.


You need to tell us which line(s) is/are causing the error(s). With that
much code, and much of it quite similar, it makes it kinda hard for us to
determine which line of code is the culprit.

--
Mike


From: MikeD on 6 Apr 2010 16:05
Guess I can maybe offer some insight though. See inline comments.

Oh, and I'm making an assumption of SQL Server since you didn't even tell us
what the backend database was. Certain SQL statements below might not be
applicable or require slightly different syntax for another RDMS.

"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.

There should be no problem reusing the same recordset object variable. You'd
close the recordset when you're done with it. However, instanting a new
object for the same object variable will implicitly close it. Furthermore,
when closing a recordset, I find its best to check to see if it's open,
since attempting to close a recordset (or connection) that is already closed
will cause an error. Here's the code I use for this (air code, hopefully no
mistakes):

Public Sub CloseRecordset(ByRef oRS As ADODB.Recordset)

If Not oRS Is Nothing Then
If (oRS.State And adStateOpen) = adStateOpen Then
oRS.Close
End If
Set oRS = Nothing
End If

End Sub

Just call that sub and pass it the recordset object variable.

>
> 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

The above could probably be optimized tremendously. Rather than opening the
entire table into a recordset and looping through all rows, write a SQL
statement which "combines" querying the data from Table1 while inserting it
into Table2. It'll be MUCH faster.

An example SQL statement would be:

INSERT INTO Table2 SELECT * FROM Table1

You can specify columns, if you want, for both Table2 and Table1:

INSERT INTO Table2 (Column3, Column1) SELECT Column4, Column2 FROM Table1

Note that the column names don't have to be the same, but the data types
must be compatible (if not identical, then must be coerceable [if that's a
word])....and you can include a WHERE clause for Table1 if you wish (it can
be any valid SELECT query). Unless there are only a few rows in Table1, this
will almost certainly be many times faster than looping through each row and
inserting it into Table2.

Of course, you may not be able to do this since I have no clue what "data1"
is and you apparently shortened your SQL code somewhat.

>
> 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

Same as above. You probably don't actually need to use a recordset at all.

>
> cnn.Execute "DELETE FROM TABLE2A WHERE..."

Didn't you just insert data into Table2A? Well, maybe you're deleting old
data in it. <g>

> cnn.Execute "UPDATE TABLE2A SET VAR1 =..."
> cnn.Execute "UPDATE TABLE2A SET VAR2 =..."
> cnn.Execute "DELETE FROM TABLE2B WHERE..."

Yet again, all of these things can maybe be written into the earlier
INSERT/UDPATE SQL statement so you don't have to do these separate updates
and deletes.

>
> 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

Here, I'd recommend that you stick with one method. Either use SQL
statements to do your data manipulations or work with the recordset object
and its properties, methods. Don't go back and forth between them.

>
> 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

These are just tips to possibly make your program a little better. As it is
now (which you sort of alluded to), it's kind of a hodgepodge. But I don't
know if anything I've suggested will actually take care of your errors. If
they do, it's because use of recordsets have been eliminated somewhat. If
you don't want to make the changes I've suggested, you'll have to provide
some more information, notably the code causing the error.

--
Mike



From: Saga on 6 Apr 2010 17:13
I agree with MikeD in that we need to see the code that generates
the error(s). Looking at the code that you provided gives me the
ipressionthat it was modified. This further obfuscates the offending
code. More remarks in line. Saga

> Dim cnn As Connection
> Dim Record As Recordset
> Dim Record1 As Recordset
> Dim Record2 As Recordset
> Dim Record3 As Recordset
> Dim Record4 As Recordset
>
You need to specify ADODB object above in all lines.

> Record.Close
> Set Record = Nothing
>
You can either Close, set to nothing then reinit using:
>
> Set Record = New ADODB.Recordset

or you can just close and reopen using the Open method. Note
that if you do this you won;t need to repeat the following over
and over again:

> Set Record = New ADODB.Recordset
> Record.CursorLocation = adUseClient
> Record.CursorType = adOpenDynamic
> Record.LockType = adLockOptimistic

Juat take note of what MikeD says regarding opening and closing
recrdsets.

Is the following your actual code? If so, it should error here:

> Record(TIME") = someTime

However, most likely, it won;t be the 91 error that you mention.

Regards, Saga



From: MikeD on 6 Apr 2010 17:28


"Saga" <antiSpam(a)nowhere.com> wrote in message
news:ubMQ43c1KHA.224(a)TK2MSFTNGP06.phx.gbl...
> I agree with MikeD in that we need to see the code that generates
> the error(s). Looking at the code that you provided gives me the
> ipressionthat it was modified. This further obfuscates the offending
> code. More remarks in line. Saga
>
>> Dim cnn As Connection
>> Dim Record As Recordset
>> Dim Record1 As Recordset
>> Dim Record2 As Recordset
>> Dim Record3 As Recordset
>> Dim Record4 As Recordset
>>
> You need to specify ADODB object above in all lines.

You don't need to, but it's advisable. There'd only be a problem if another
library were referenced that has these same objects (for example, if the DAO
library was also referenced). And then priority would be given according to
how the libraries are listed in the References dialog box.


>
> Juat take note of what MikeD says regarding opening and closing
> recrdsets.

After re-reading what I wrote, some of it didn't even make sense to me. I
could have been clearer on a couple points, but hopefully they got across
and were clear enough. <g>

>
> Is the following your actual code? If so, it should error here:
>
>> Record(TIME") = someTime
>
> However, most likely, it won;t be the 91 error that you mention.

I looked at that rather closely in fact. I don't think it would cause an
error. Not good code IMO though because it's relying on way too many default
properties.

--
Mike



 |  Next  |  Last
Pages: 1 2
Prev: MSFlexgrid Bug
Next: Mapi email problem (and Apology)