Jim Dettman
jimdettman at verizon.net
Tue Jan 13 05:48:09 CST 2015
<<Jim, I can't get to the full article (not a member of experts exchange
unfortunately). >>
huh, articles are supposed to be freely shareable even before the pay wall
came down.
But since last Feb, you now should be able to view any question or article
without being a member; using the link that was not the case for you?
Jim.
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of John Bodin
Sent: Monday, January 12, 2015 05:45 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] System resource exceeded. Error No 3035
Jim, I can't get to the full article (not a member of experts exchange
unfortunately). What is CurDB(), a dao.database object? I use this in my
code:
Global dbCurrent As DAO.Database
Set dbCurrent = CurrentDb
and reference CurrentDb.execute, etc. throughout my code. Only issue I have
is when an untrapped error occurs (like a null I'm not testing for),
CurrentDB loses its reference and I can't bail out gracefully.
Thanks for any info. Looked like a nice start to the article...
John
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Dettman
Sent: Monday, January 12, 2015 5:31 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] System resource exceeded. Error No 3035
The difference is about an order of a magnitude, which I checked many years
ago (like A97 days).
Haven't bothered to check since then. I always use the routine in the
article.
I can say that on a number of inherited DB's, it's one of the first things
I do (swap out all currentdb()'s with CurDB()) and I always get a comment
from the users about how much faster it is already.
Between this and always holding a reference open to the BE DB's, I've
earned the title of "miracle worker", which always seems to me to be a bit
of a cheat<g>.
I would suggest trying it...take a copy of Northwinds (or any app) and
with Find and Replace, you can change it over pretty quickly. You will
notice a difference.
I would add that in all these years, I've never found a down side to using
it either.
Jim.
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Monday, January 12, 2015 04:25 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] System resource exceeded. Error No 3035
Hi Jim
I must admit I use CurrentDb as it is easier(!) to type than DbEngine(0)(0).
> .. I do find apps using this routine to be "snappier" then those
> without
it.
How you done any measurements?
/gustav
________________________________________
Fra: accessd-bounces at databaseadvisors.com
<accessd-bounces at databaseadvisors.com> på vegne af Jim Dettman
<jimdettman at verizon.net>
Sendt: 12. januar 2015 22:15
Til: 'Access Developers discussion and problem solving'
Emne: Re: [AccessD] System resource exceeded. Error No 3035
We certainly you don't want to close it as you never opened it here in the
routine.
Also not sure why your bothering with the mdb variable. Take a look at
this:
http://www.experts-exchange.com/Database/MS_Access/A_2072-CurrentDB-vs-dbEng
ine-Workspaces-0-Databases-0-and-an-alternative.html
Similar to what your doing now, but a tad different and would be faster
and eliminate overhead.
Jim.
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jurgen Welz
Sent: Monday, January 12, 2015 01:22 PM
To: Access Developers discussion and problem solving
Subject: [AccessD] System resource exceeded. Error No 3035
Running 32 bit Access 2013 on 64 bit Windows Server 2012 R2, 60 Megabyte
front end (decompiled/compancted) to data primarily stored in 2012 SQL
Server. We have our servers in a capable co-locate facility in our city and
run RDP from Win 8.1 clients.
Each user runs their own front end and that front end has a local Access
table that stores a small lookup table of 3 Short Text fields containing 5
records. Two of the columns are static and they contain the name of a table
in one field and a field name in the second. The 3rd column contains a sql
string that defines a filter for that field in the table. The length of the
3rd field allows 255 characters and the length of the data actually stored
does not exceed 25 characters.
Since July, I have logged 90 errors in a form close event that clears the
3rd field for one of the records. The form has a module level variable for
a DAO Database object (mdb) that is correctly instantiated. The system
resources error has also been triggered once when the field was set to a
string and fewer than 5 times for procedures in other forms. Among others,
I have references to the Access 15 library, ActiveX Data Objects 2.6, Office
15.0 Access database engine objectsand Jet and Replication Object 2.6 but
attempting to set a reference to an actual version of the DAO library throws
an error "Name conflicts with existing module, project, or object library".
the database object is declared at module level:
Dim mdb As DAO.Database
It is then set by a function that is set to a static variable itself set by
CurrentDb.
The procedure follows:
Private Sub Form_Close()
On Error GoTo ErrorHandler
mdb.Execute "Update tblFilter Set Strsql = '' Where FieldName =
'DeptID'", dbSeeChanges
ExitRoutine:
On Error Resume Next
mdb.Close
Set mdb = Nothing
Exit Sub
ErrorHandler:
With Err
Select Case .Number
Case Else
LogErr Me.Name, "Form_Close", .Description, .Number
End Select
End With
'Resume 0
Resume ExitRoutine
End Sub
There are several hundred instances of mdb.execute strsql syntax in the
database that never cause the system resources error and the form close
event above may only trigger an error in the neighborhood of 1 time in 50
(or in 200). As a rule, though, those executes are primarily against ODBC
linked SQL Server data. Checking system resources when this error happens
shows server cpu at 20 - 40% and memory at 20 - 45%. These numbers are
typical when we have about 60 users on.
The problem is not huge, but I'd like to figure out what is causing it.
Google finds some suggestions but nothing that really helps. I'm going to
add a dump of the exact system resources when the error is thrown, but it
seems to me this is spurious and related to the data being in the users'
local FE. A bug like this was reported in Access 2010 with .executes and I
will switch to an 'Open Recordset, Edit, Update' syntax to see if that
addresses the issue.
Does anyone have any insight?
Ciao
Jürgen Welz
Edmonton, Alberta
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com