Jim Dettman
jimdettman at verizon.net
Mon Jan 12 15:15:33 CST 2015
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
> From: bensonforums at gmail.com
> To: accessd at databaseadvisors.com
> Date: Sun, 11 Jan 2015 14:40:35 -0500
> Subject: Re: [AccessD] Access refuses to embed event procedure for new
command buttons
>
> I know, thanks. I need the wizard because in my lack of recollection I
need
> to see what commands are generated - and shown in a VBA friendly manner.
> Bill
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Charlotte Foust
> Sent: Sunday, January 11, 2015 2:34 PM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Access refuses to embed event procedure for new
> command buttons
>
> Part of your problem lies in using wizards. Wizards are pre-built to do
> things a certain way, regardless of settings. If you just add the control
> yourself, you'll be able to create the code for it. The only wizard I use
> in Access (any version) is to to create find duplicates or crosstab
queries.
>
> Charlotte
>
> On Sun, Jan 11, 2015 at 10:54 AM, Bill Benson <bensonforums at gmail.com>
> wrote:
>
> > Ac2013.
> >
> >
> >
> > I have set the option to always use event procedures in forms and
> > report designers in the Acces s options, restarted access and the
> > database - but Access refuses to offer this, it keeps embedding macros
> > when I add controls with the controls wizard. Also, because they are
> > embedded, I cannot see how to convert them to VBA procedures, as I
> > used to when I could find them in the object navigator.
> >
> >
> >
> > Is there corruption here or is there something I can do about this, it
> > is really frustrating to me. It used to put in the click event, now it
> > has switched to and embedded macro, without my doing anything I am
> > aware I have done.
> >
> >
> >
> > TIA.
> >
> > --
> > 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
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com