Resource grabbing Access - Jet - ODBC - SQL Server problems.

[B Moor]

Hello,
I am quite bogged down with this problem and would like some tips/help
if any one has any.

Thanks in advance.
The Problem
-----------
This system initially seemed quite stable for a few weeks but now,
after several upgrades,and having moved more tables to SQL Server
(that were local Access tables), and having more functionality 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)
Sometimes Access can crash.
I have tried to re-instate earlier versions to see if stable with no
luck; it is posssible problem always there but not seen.

All is well when Access 1st run but after a few 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/100 the speed or less.
Some of the symptoms
--------------------

Upon checking the task manager the mem usuage upto 160MB and handle
count upto 86,000 !

When I start the access app the mem= 28M and the handle count = 260.

** I have 2 forms continously open, with timer based functions, and
the handle count jumps (and does not recover) every time these are
called WHEN
one of my other bound forms is opened **
** After the "Access Fail" if I stop/start access only, performance is
not greatly improved, I have to stop/start SQL Server as well **

** After the "Access Fail" if I look at one of my clients, running the
same Access app on another PC, it seems normally responsive when using
one my bound forms to browse the data. **

Design Used
-----------

This application started as Access 97 front/back end. I now use the
above.
Access uses odbc to linked tables on SQL Server
Most of my forms are bound, using stored queries that are modified on
form open to minimise the row count to a predetermined max rows
value.One of the primary selection criteria is date using the datediff
function.

Use global variable dbs = currentdb(); dim rst as dao.recordset ; set
rst=dbs.openrec ordset(strSQL) etc
DAO used for all the recordsets I generate (and subsequently close ,
set= nothing religiously).
DAO also used in a function to replace DLookUp, and used a lot.
I also use ADO, in some of the latest code, to insert data via SQL
Server stored procedures.

Refs:
Visual Basic For applications
Microsoft Access 11.0 Object Library
OLE Automation
Microsoft DAO 3.6 Object Library
Microsoft ActiveX Data Objects 2.1 Library
Pegasus ImagXpress Control v6.0
Microsoft Web Components 11.0

Setup
--------

Server PC: Win SBS 2003 with 2.6 GHz processor and 1GB RAM
SQL Server 2000 v 2000.8.00.76 (sp3)
MS Office 2k3
MSJet ms04-014 (latest ost sp8)
MDAC v2.8 RTM

Client PC's (Quantity = 10)
I have one Access application that with configuration is a basic
client or a "managment client"
x4 Access 2k3 applications running on XP Pro (configured as clients)
x1 Access 2k3 application running on server that has all the client
functionality plus some central management features.
x5 VB6 Data points, automatically receiving and inserting data into
SQL Server using ADO.

On average, ammount of new data/day = 5000 records (2MB in total)

[TJ]

Is the Access app stored on the server and pulled across the network on
open? Or is the app stored on the local workstation?
TJ