[AccessD] Ac2013 running out of resources

[Darryl Collins]

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