[AccessD] Ac2013 running out of resources

[Bill Benson]

The query runs over 60 times in a loop that outputs this same report. I
don't see how the complexity of the query could have anything to do with
consuming resources
On Jun 5, 2015 2:01 AM, "Darryl Collins" <darryl at whittleconsulting.com.au>
wrote:

> In my experience Access is like a fat old guy going for a Jog.  It tries
> hard and sometime can surprise you with bursts of speed, endurance and
> agility, but other times it will get puffed out over nothing, give up and
> stop.
>
> The most simple work-around I found was not to use mega queries ( or even
> simple queries with multiple joins seem to be a culprit here), rather
> building the final data set required via a series of helper queries (or
> even tables).  This approach would nearly always produce much better
> performance and less of a processing struggle for Access.  Also look at
> what you have indexed as well - that can make a big difference.
>
> Keep in mind Access is poor at cleaning up after itself.  I have had
> simple queries push it over the 2GB limit whilst processing and it just
> sh!ts itself and stops.  Bloody stupid, but that is how it is.
>
> As the datasets we are working with are getting larger and more complex,
> we are moving more and more of the grunt processing into SQL server - and
> only use Access for "the last mile" of processing when the data is usually
> significantly compacted.  SQL Server rarely gives up and will run until the
> gig is done.
>
> Anyway...  Good luck. I can relate to your frustration and annoyance.
>
> Darryl.
>
>
>
>
> -----Original Message-----
> From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
> Bill Benson
> Sent: Friday, 5 June 2015 3:44 PM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Ac2013 running out of resources
>
> What is causing resources to max out. The reports are not large, the
> queries not complex. Is it SharePoint? Output to PDF? What could Ac2013
> possibly be straining at here?
> On Jun 4, 2015 11:04 PM, "Darryl Collins" <darryl at whittleconsulting.com.au
> >
> wrote:
>
> > Heh... There is Bill.  It is called 'SQL Server Express'.
> >
> > -----Original Message-----
> > From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf
> > Of Bill Benson
> > Sent: Friday, 5 June 2015 12:10 PM
> > To: Access Developers discussion and problem solving
> > Subject: Re: [AccessD] Ac2013 running out of resources
> >
> > Guys, I sure join in the lament but.... surely there HAS to be a way
> > to not run out of resources. Even if I have to stash the darned data
> > in a remote database (also linked to SharePoint obvously) that I open
> > long enough to run a report in via a auto exec macro and then shut it
> down...
> > surely there has to be a way to do this? I hate abject failure.
> >
> > Would it matter if I ran the reports against a temp table (populated
> > with a consecutive loop of make table queries)? There are seldom more
> > than 30 records per report, as this routine is breaking out project
> > list report by partner - or manager, itdepends on which is called -
> > and no partner has more than 30 projects that is sure.
> >
> > I wanted to test the impact of opening the reports in acPreview as
> > David suggested... but dammit, the database stopped being able to
> > connect to Sharepoint url about 8PM. Maybe they brought it down for
> > maintenance. So I couldn't test it.
> >
> > I don't mind a kluge at this point. Barring that, asking the user to
> > shut down the database to run all remaining reports (in picking up at
> > whatever point the record set left off by comparing to records in a
> > log table is about all I can come up with here... not likely to endear
> me with my users.
> >
> > Help.........
> > On Jun 4, 2015 9:44 PM, "Stuart McLachlan" <stuart at lexacorp.com.pg>
> wrote:
> >
> > > Typical MS. Every second version is OK.
> > >
> > > v 2 OK
> > > 95 cr*p
> > > 97 OK
> > > 2000 cr*p
> > > 2003 OK
> > > 2007 cr*p
> > > 2010 OK
> > > 2013 cr*p
> > > 2016 ?
> > >
> > > :)
> > >
> > >
> > > On 4 Jun 2015 at 20:34, Mark Simms wrote:
> > >
> > > > This is endemic to AC2013. It's all-messed-up.
> > > > I am constantly getting hung queries as well as "Resources Exceeded"
> > > > messages. It's really a P.O.C. Almost unusable as a contrast to a
> > > > darn good 2010 release.
> > > >
> > > > Also: I just got an Office 2013 update installed and there was no
> > > > improvement.
> > > >
> > > >
> > > >
> > > > --
> > > > 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
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>