Jurgen Welz
jwelz at hotmail.com
Mon Jan 12 18:14:35 CST 2015
The mdb variable is set at the form open event and is killed in the form close event I pasted in the email below. The reason I close it is because it is good practice to clean up object variables in code that is 'guaranteed to run' via an exit routine using 'On Error Resume Next'.
I explained that I was returning a pointer in memory to a static variable via a function. The advantage is that I can guarantee a refreshed collection any time I want. Using a function that returns a pointer to a static variable already instantiated with CurrentDb is many times faster than calling CurrentDb, though the difference depends on the complexity of the collections of objects. I have a relatively complex database and calling currentdb in a loop 1000 times takes 2.102 seconds in our environment. Calling the function I've used (since Access 95) takes 4 milliseconds for 1000 iterations. There's no way any user will be able to detect 4/1000 ths of a millisecond, and that time includes the time to increment the counter.
The test is easy to perform. timeGetTime is a simple API call that returns milliseconds since boot. It has a granularity of one millisecond. Lookup the QueryPerformanceCounter for sub microsecond accuracy. Timegettime is a simple one line declare.
In the SpeedTest sub below, just swap out the line:
Set db = fnDB
with
Set db = CurrentDb
and check the results in the immediate window.
Public Declare Function timeGetTime Lib "Winmm" () As Long
Public Sub SpeedTest()Dim lngx As LongDim lngi As LongDim db As DAO.Databaselngi = timeGetTimeFor lngx = 0 To 999Set db = fnDBSet db = NothingNextDebug.Print timeGetTime - lngiEnd Sub
fnDB is the substitute I use for CurrentDB as shown below:
Public Function fnDB() As DAO.Database On Error GoTo ErrorHandler
Static db As DAO.Database Dim lngi As Long
lngi = Len(db.Name)
ExitRoutine: On Error Resume Next Set fnDB = db Exit Function
ErrorHandler: With Err Set db = CurrentDb End With 'Resume 0 Resume ExitRoutineEnd Function
Ciao Jürgen Welz Edmonton, Alberta
> From: jimdettman at verizon.net
> To: accessd at databaseadvisors.com
> Date: Mon, 12 Jan 2015 16:15:33 -0500
> Subject: 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
>
> > 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
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com