Global Variable giving error "Object Variable or With block Variable

Hello everyone: Using Access 2003 with links to SQL tables.

Trying to create a persistent recordset connection to help with
perfromance.

In General Declarations I have the following Code:
Public AlwaysOpenRecordSet As Recordset

In a hidden form that is always open I have the following code on the
Open Event:
Set AlwaysOpenRecordSet = CurrentDb.OpenRecordset("Select * from
dbo_udINToolOrderEdit where Co = 500")


In the same hidden form on The Close Event I have the following code:
AlwaysOpenRecordSet.Close
Set AlwaysOpenRecordSet = Nothing


On exiting the application I get the following error: "Object
Variable or With block Variable Not set" I believe the
"AlwaysOpenRecordSet.Close" is causing the error. This error does not
happen everytime I exit the application.

Any help would be appreciated. Thanks Ray

Re: Global Variable giving error "Object Variable or With block Variable Not Set"

eighthman11 wrote:
> Hello everyone: Using Access 2003 with links to SQL tables.
>
> Trying to create a persistent recordset connection to help with
> perfromance.

The persistent connection performance advantage does not apply to ODBC links to
SQL Server tables. Only to linked tables in other MDB files.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Re: Global Variable giving error "Object Variable or With block Variable Not Set"

On Sat, 5 Jan 2008 08:47:31 -0800 (PST), eighthman11 wrote:

> Hello everyone: Using Access 2003 with links to SQL tables.
>
> Trying to create a persistent recordset connection to help with
> perfromance.
>
> In General Declarations I have the following Code:
> Public AlwaysOpenRecordSet As Recordset
>
> In a hidden form that is always open I have the following code on the
> Open Event:
> Set AlwaysOpenRecordSet = CurrentDb.OpenRecordset("Select * from
> dbo_udINToolOrderEdit where Co = 500")
>
>
> In the same hidden form on The Close Event I have the following code:
> AlwaysOpenRecordSet.Close
> Set AlwaysOpenRecordSet = Nothing
>
>
> On exiting the application I get the following error: "Object
> Variable or With block Variable Not set" I believe the
> "AlwaysOpenRecordSet.Close" is causing the error. This error does not
> happen everytime I exit the application.
>
> Any help would be appreciated. Thanks Ray

You could avoid the message by embedding:
if not (AlwaysOpenRecordSet is nothing) then AlwaysOpenRecordSet.Close

But .., it don't tells what made the AlwaysOpenRecordSet cleared - i belive
that just any error can clear all variables.

wouldn't it be better, just to create the recordset on demand - drop the
hidden form and put this in module:

private AlwaysOpenRecordSetObject as recordset

function AlwaysOpenRecordSet() as recordset
if AlwaysOpenRecordSetObject is nothing then
set AlwaysOpenRecordSetObject = ...
endif
set AlwaysOpenRecordSet=AlwaysOpenRecordSetObjec
end function

--
Benny Andersen