Access 2k3 using SQL Server 2000: will go very slow after several hours

[B Moor]

Host PC: Win SBS 2003 with 2.6 GHz processor and 1GB RAM
SQL Server 2000 sp3 (or latest).MS Office 2k3

I have the access 2k3 front end running on server (this may get
changed) and all was well until an upgrade 1 week ago when I installed
a new version of the mde (with very minor changes) and a new VB
application via a VB6 installer setup.exe.

Access uses odbc to linked tables on SQL Server, plus a few local
tables.
I also use DAO for all the recordsets I generate (and subsequently
close , set= nothing religiously).
This system has run quite stable for weeks but now, after the upgrade
and new VB app, I have had several resource type errors eg
Error 3035 System resource exceeded
Error ???? There isnt enough memory to perform this operation
+ general ODBC errors if above cleared

also
Error 3633 Cant load dll MSJTER40.DLL (ms jet error handler)
All is well when Access 1st run but after say 8 hours or so the Access
app grinds to a halt. The cpu process % is low,there is 400M RAM
available, other apps are ok but access is in a bad way, still
functioning but at approx 1/10 the speed or less.

Upon checking the task manager I noticed that after the 8 hours or so
I had mem usuage upto 160MB and handle count upto 86,000 !

Now when I start the access app the mem= 28M and the handle count =
260.
On 1 test after 6 hours this had gone upto 46M and 834.
But then on checking 2 hours later the handle count was jumoing up by
the 100 every few seconds.

This is related to frequent timer based queries and functions I run,
but why the dramatic change ?

If I stop/start access only, performance is not greatly improved, I
have to stop/start SQL Server as well.

I am wondering if I have some how corrupted mdac stuff with the VB
install as it installed older dlls :-
MSJTER35.DLL
MSJET35.DLL
dao350.dll
msado20.tlb
So how to fix ?
Well I dont want to just go changing everything & anything that comes
to mind, I would like to be more structured but I am not sure where to
start first, any help appreciated.

[Tom van Stiphout]

On 16 Oct 2004 08:25:31 -0700, mo******@yahoo.co.uk (B Moor) wrote:

I have not seen the behavior you mention, and we have 2 Access apps
(Access 2000) running against SQL Server all the time, and they may
run for weeks doing some serious data manipulation without the system
being rebooted. So it *is* possible.
Some suggestions:
* Make sure you have the latest Jet service pack: Jet 4.0 SP8.
* Use PerfMon to narrow down where the resources are consumed. Perhaps
also use SQL Server Profiler.
* Standard debugging trick: simplify the problem. Try to isolate the
part that causes this behavior by eliminating steps.
* Not sure why you mention dao350; I think you should be using dao360.

-Tom.

Host PC: Win SBS 2003 with 2.6 GHz processor and 1GB RAM
SQL Server 2000 sp3 (or latest).MS Office 2k3

I have the access 2k3 front end running on server (this may get
changed) and all was well until an upgrade 1 week ago when I installed
a new version of the mde (with very minor changes) and a new VB
application via a VB6 installer setup.exe.

Access uses odbc to linked tables on SQL Server, plus a few local
tables.
I also use DAO for all the recordsets I generate (and subsequently
close , set= nothing religiously).
This system has run quite stable for weeks but now, after the upgrade
and new VB app, I have had several resource type errors eg
Error 3035 System resource exceeded
Error ???? There isnt enough memory to perform this operation
+ general ODBC errors if above cleared

also
Error 3633 Cant load dll MSJTER40.DLL (ms jet error handler)
All is well when Access 1st run but after say 8 hours or so the Access
app grinds to a halt. The cpu process % is low,there is 400M RAM
available, other apps are ok but access is in a bad way, still
functioning but at approx 1/10 the speed or less.

Upon checking the task manager I noticed that after the 8 hours or so
I had mem usuage upto 160MB and handle count upto 86,000 !

Now when I start the access app the mem= 28M and the handle count =
260.
On 1 test after 6 hours this had gone upto 46M and 834.
But then on checking 2 hours later the handle count was jumoing up by
the 100 every few seconds.

This is related to frequent timer based queries and functions I run,
but why the dramatic change ?

If I stop/start access only, performance is not greatly improved, I
have to stop/start SQL Server as well.

I am wondering if I have some how corrupted mdac stuff with the VB
install as it installed older dlls :-
MSJTER35.DLL
MSJET35.DLL
dao350.dll
msado20.tlb
So how to fix ?
Well I dont want to just go changing everything & anything that comes
to mind, I would like to be more structured but I am not sure where to
start first, any help appreciated.

[Steve Jorgensen]

There is a lot of misunderstanding around the need to set DAO object
references to Nothing. The key thing is not that they need to be explicitly
set to Nothing ber se, but that they must be closed and released in reverse of
the order of dependency.

For instance, if I open a workspace, open a database, and open a recordset, I
need to close the recordset and release the reference, close the database and
release the reference, then close the workspace and release the reference.

On 16 Oct 2004 08:25:31 -0700, mo******@yahoo.co.uk (B Moor) wrote:

Host PC: Win SBS 2003 with 2.6 GHz processor and 1GB RAM
SQL Server 2000 sp3 (or latest).MS Office 2k3

I have the access 2k3 front end running on server (this may get
changed) and all was well until an upgrade 1 week ago when I installed
a new version of the mde (with very minor changes) and a new VB
application via a VB6 installer setup.exe.

Access uses odbc to linked tables on SQL Server, plus a few local
tables.
I also use DAO for all the recordsets I generate (and subsequently
close , set= nothing religiously).
This system has run quite stable for weeks but now, after the upgrade
and new VB app, I have had several resource type errors eg
Error 3035 System resource exceeded
Error ???? There isnt enough memory to perform this operation
+ general ODBC errors if above cleared

also
Error 3633 Cant load dll MSJTER40.DLL (ms jet error handler)
All is well when Access 1st run but after say 8 hours or so the Access
app grinds to a halt. The cpu process % is low,there is 400M RAM
available, other apps are ok but access is in a bad way, still
functioning but at approx 1/10 the speed or less.

Upon checking the task manager I noticed that after the 8 hours or so
I had mem usuage upto 160MB and handle count upto 86,000 !

Now when I start the access app the mem= 28M and the handle count =
260.
On 1 test after 6 hours this had gone upto 46M and 834.
But then on checking 2 hours later the handle count was jumoing up by
the 100 every few seconds.

This is related to frequent timer based queries and functions I run,
but why the dramatic change ?

If I stop/start access only, performance is not greatly improved, I
have to stop/start SQL Server as well.

I am wondering if I have some how corrupted mdac stuff with the VB
install as it installed older dlls :-
MSJTER35.DLL
MSJET35.DLL
dao350.dll
msado20.tlb
So how to fix ?
Well I dont want to just go changing everything & anything that comes
to mind, I would like to be more structured but I am not sure where to
start first, any help appreciated.

[B Moor]

Tom van Stiphout <no*************@cox.net> wrote in message news:<96********************************@4ax.com>. ..

On 16 Oct 2004 08:25:31 -0700, mo******@yahoo.co.uk (B Moor) wrote:

I have not seen the behavior you mention, and we have 2 Access apps
(Access 2000) running against SQL Server all the time, and they may
run for weeks doing some serious data manipulation without the system
being rebooted. So it *is* possible.
Some suggestions:
* Make sure you have the latest Jet service pack: Jet 4.0 SP8.
* Use PerfMon to narrow down where the resources are consumed. Perhaps
also use SQL Server Profiler.
* Standard debugging trick: simplify the problem. Try to isolate the
part that causes this behavior by eliminating steps.
* Not sure why you mention dao350; I think you should be using dao360.

-Tom.

Thanks Tom,

I will take your advice.

I am unclear, and have not found much info searching around, as to
what could cause excessive handle counts. I understand that a handle
will be used for any new object so ones that I create I must close,
set = nothing , which I do. So what does that leave, perhaps if i
Knew this i would know more about the problem.
Any ideas ?
I mention dao35 as it was included in the project build for a VB6
install although my VB6 uses ADO. During the install process I
selected "leave existing newer file", but I did not take note of which
files this was for. My intuitive (more like guess) feel is that I
have somehow got 2 different versions of dao/ado/odbc (=mdac ?) mixed
up causing odd results ?

regards,

Brian

[B Moor]

I use the currentdb function which is set on logon form open and
closed, set= Nothing on unload. Any recordset or querydef created is
then used and closed, =nothing. Am I missing something ?
Re my main problem I neglected to mention that we use remote desktop
sharing on our SBS 2003 for support and operator useage (the operators
use an adsl vpn connection to perform the Access based data browsing
and filtering; there is very little editing).
I came across an article linking poor performance with SQL 2000 and
terminal services, that might show up as Access fault and handle
leaking - something else to consider.

[Steve Jorgensen]

On 17 Oct 2004 11:05:22 -0700, mo******@yahoo.co.uk (B Moor) wrote:

I use the currentdb function which is set on logon form open and
closed, set= Nothing on unload. Any recordset or querydef created is
then used and closed, =nothing. Am I missing something ?
No, you're probably not missing anything - just so you're releasing objects in
the correct order. Make sure the recordset is closed and set to Nothing, and
the Querydef is set to Nothing -before- the database reference is set to
Nothing
Re my main problem I neglected to mention that we use remote desktop
sharing on our SBS 2003 for support and operator useage (the operators
use an adsl vpn connection to perform the Access based data browsing
and filtering; there is very little editing).
I came across an article linking poor performance with SQL 2000 and
terminal services, that might show up as Access fault and handle
leaking - something else to consider.

Sounds like a likely culprit.

[Stephen K. Young]

"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:br********************************@4ax.com...

No, you're probably not missing anything - just so you're releasing objects in the correct order. Make sure the recordset is closed and set to Nothing, and the Querydef is set to Nothing -before- the database reference is set to
Nothing

In my experience, you do not need this order for recordsets. You can set the
database object to Nothing, and the recordset object is still valid and it
can still be used, because it contains its own database pointer once it has
been opened. I have not tried it specifically in Access 2003, but I have
tried it in Access 97 and 2000.

Of course, I tend to set the database to Nothing afterwards anyway, just
because it is conceptually cleaner.

- Steve

[Steve Jorgensen]

On Mon, 18 Oct 2004 17:57:21 -0400, "Stephen K. Young" <s k y @
stanleyassociates . com> wrote:

"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:br********************************@4ax.com.. .

No, you're probably not missing anything - just so you're releasing

objects in

the correct order. Make sure the recordset is closed and set to Nothing,

and

the Querydef is set to Nothing -before- the database reference is set to
Nothing

In my experience, you do not need this order for recordsets. You can set the
database object to Nothing, and the recordset object is still valid and it
can still be used, because it contains its own database pointer once it has
been opened. I have not tried it specifically in Access 2003, but I have
tried it in Access 97 and 2000.

Of course, I tend to set the database to Nothing afterwards anyway, just
because it is conceptually cleaner.

Yes, that pretty much always functions. That does not mean it is not causing
problems, and it frequently -is- causing problems. You may see these problems
as out of memory errors after using your program for a long time, or Access
failing to close or crashing on close.

[Stephen K. Young]

"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:vv********************************@4ax.com...

On Mon, 18 Oct 2004 17:57:21 -0400, "Stephen K. Young" <s k y @
stanleyassociates . com> wrote:

"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:br********************************@4ax.com.. .

No, you're probably not missing anything - just so you're releasingobjects in

the correct order. Make sure the recordset is closed and set to Nothing,

and

the Querydef is set to Nothing -before- the database reference is set

to Nothing

In my experience, you do not need this order for recordsets. You can set

thedatabase object to Nothing, and the recordset object is still valid and itcan still be used, because it contains its own database pointer once it hasbeen opened. I have not tried it specifically in Access 2003, but I have
tried it in Access 97 and 2000.

Of course, I tend to set the database to Nothing afterwards anyway, just
because it is conceptually cleaner.

Yes, that pretty much always functions. That does not mean it is not

causing problems, and it frequently -is- causing problems. You may see these problems as out of memory errors after using your program for a long time, or Access failing to close or crashing on close.

Ok, well just to confirm I ran a test setting the database to Nothing prior
to closing the recordset, with 10,000 calls within a large application. I
did not have any problems closing Access, and all memory was released when
Access finished, at least within the limits of task manager resolution. So
maybe something else was causing Access to misbehave?

Below is the test code, which has "Set db = Nothing", prior to using and
closing the recordset. I was running Access 2000.

- Steve
___________________

Option Compare Database
Option Explicit

Function Test()
Dim lngCall As Long, lngResult As Long
For lngCall = 1 To 10000
lngResult = TestRecordSet()
Next lngCall
End Function

Function TestRecordSet() As Long

On Error GoTo ErrExit
Dim db As Dao.Database, rst As Dao.Recordset, b As Boolean
Set db = CurrentDb()
Set rst = db.OpenRecordset("SELECT (4<>Null) AS Expr1",
dbOpenForwardOnly)
Set db = Nothing
b = rst.Fields(0).Value
rst.Close
Set rst = Nothing
Exit Function

ErrExit:
MsgBox Err.Description
Stop
Resume
End Function

[Steve Jorgensen]

I have developed my practices based on my experiences with Access and JET over
the last 10 years. The problems that occur with regard to releasing DAO
objects are quirky, and never strictly reproducible, but definitely real.

Although I have also run tests like the one you just ran with no problems, I
have also had more than one application that had consistent, reproducible
problems exactly until I fixed a problem with the order of closing and
releasing DAO objects, after which the problems ceased, permanently. I don't
know what specific things caused these applications to be sensitive to the
problem while simpler attempts to reproduce it are not, but I really recommend
being religious about this if you want to be sure not to have a reliability
issue that could take weeks to track down later.

On Tue, 19 Oct 2004 16:37:24 -0400, "Stephen K. Young" <s k y @
stanleyassociates . com> wrote:

"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:vv********************************@4ax.com.. .

On Mon, 18 Oct 2004 17:57:21 -0400, "Stephen K. Young" <s k y @
stanleyassociates . com> wrote:

>
>"Steve Jorgensen" <no****@nospam.nospam> wrote in message
>news:br********************************@4ax.com.. .
>> No, you're probably not missing anything - just so you're releasing
>objects in
>> the correct order. Make sure the recordset is closed and set toNothing, >and
>> the Querydef is set to Nothing -before- the database reference is setto >> Nothing
>
>In my experience, you do not need this order for recordsets. You can setthe >database object to Nothing, and the recordset object is still valid andit >can still be used, because it contains its own database pointer once ithas >been opened. I have not tried it specifically in Access 2003, but I have
>tried it in Access 97 and 2000.
>
>Of course, I tend to set the database to Nothing afterwards anyway, just
>because it is conceptually cleaner.

Yes, that pretty much always functions. That does not mean it is not

causing

problems, and it frequently -is- causing problems. You may see these

problems

as out of memory errors after using your program for a long time, or

Access

failing to close or crashing on close.

Ok, well just to confirm I ran a test setting the database to Nothing prior
to closing the recordset, with 10,000 calls within a large application. I
did not have any problems closing Access, and all memory was released when
Access finished, at least within the limits of task manager resolution. So
maybe something else was causing Access to misbehave?

Below is the test code, which has "Set db = Nothing", prior to using and
closing the recordset. I was running Access 2000.

- Steve
___________________

Option Compare Database
Option Explicit

Function Test()
Dim lngCall As Long, lngResult As Long
For lngCall = 1 To 10000
lngResult = TestRecordSet()
Next lngCall
End Function

Function TestRecordSet() As Long

On Error GoTo ErrExit
Dim db As Dao.Database, rst As Dao.Recordset, b As Boolean
Set db = CurrentDb()
Set rst = db.OpenRecordset("SELECT (4<>Null) AS Expr1",
dbOpenForwardOnly)
Set db = Nothing
b = rst.Fields(0).Value
rst.Close
Set rst = Nothing
Exit Function

ErrExit:
MsgBox Err.Description
Stop
Resume
End Function

[Stephen K. Young]

"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:80********************************@4ax.com...

I have developed my practices based on my experiences with Access and JET over the last 10 years.
And you have contributed frequently to the newsgroup, thanks for that.
Although I have also run tests like the one you just ran with no problems, I have also had more than one application that had consistent, reproducible
problems exactly until I fixed a problem with the order of closing and
releasing DAO objects, after which the problems ceased, permanently.

I agree your recommendation is better practice.

Perhaps Microsoft implemented it so that people could write code like:

set rst = CurrentDb().OpenRecordset(...)

without losing the recordset.

Code using CurrentDb() like that makes me uncomfortable, so I do
set an explicit database object first and then free it after the recordset
is closed, which is what you are recommending. But on paper it should
work, and I never generated a reproducible error using simple examples..

- Steve