Solved

Sudden issue opening large sql table in access

Posted on 2014-09-08
17
359 Views
Last Modified: 2014-09-11
Hi Experts,

A serious issue suddenly occurred to my Access ADP as follows.

When opening a large table (1.5 Million records), I get an error when trying to scroll down to the bottom of the table,

error message states "Data Provider or other service returned an E_Fail Status"

what's strange is that I can still open that with an previous version of the app.

Any suggestion?

Compact/repair did not help, also did change connection to test and back to production, nothing changed.

FYI- this is an Access 2000 ADP linked to SQL Express 2005.
0
Comment
Question by:bfuchs
  • 5
  • 5
  • 5
  • +1
17 Comments
 
LVL 45

Assisted Solution

by:aikimark
aikimark earned 208 total points
Comment Utility
Convert the ADP to an ACCDE

How are you running the Access program (OS version, 32/64 bit, compatibility mode?)
0
 
LVL 45

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 84 total points
Comment Utility
Suggestion is to upgrade Access and SQL Server to more recent versions.
Also check which provider are the application using to connect to SQL Server.
0
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 208 total points
Comment Utility
Can you link to the table from a different app?  If so, then rebuild the .adp from scratch.  Start with a new empty database and import all the objects.  This should eliminate any corruption.  Don't forget to fix your references and startup settings.

An unhelpful suggestion is to not scroll to the end of a 1.5 million row record set.  You are forcing Access to retrieve all that data from the server and completely eliminating any benefit you might derive from having server side processing of queries.
0
 
LVL 3

Author Comment

by:bfuchs
Comment Utility
Hi Experts,

First of all, thanks for replying.

@aikimark, Vitor Montalvão
My manager is not a fan of upgrading systems unless its something really crucial, this is part of the reason we are still on that version, although some of our users have already upgraded to Office 2003.
However I also refrain trying to convince him to upgrade until I have a solution for all problems that arise from such upgrades, see for example link below, that I had posted recently, w/o solution yet..we use conditional formatting allot in our app especially in continuous forms, and I see a major draw back in later versions.


@PatHartman,
I am not purposely scrolling down to the end, its just when we have a continuous form and user does not specify a filter criteria, it then tries to bring up all records, I know there is a max records property, users don't like to have that limitation as this will not show exact count of records matching filter criteria.

Re the Initial problem, interesting that it went away just the way it came...Will keep you posted if this comes back.

Thanks again,
Ben
0
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 208 total points
Comment Utility
A form should NEVER be bound directly to a table or even to a query without criteria when the BE is a server based database.  This completely defeats the purpose of using a server based database as the BE.  You would be better off with using Jet/ACE as the BE since Access is at least optimized to work with those database engines using desktop techniques.  Jet and ACE support large row counts quite efficiently as long as you don't exceed the total database size.  Even then, you may be able to split the data into multiple BE's.

I don't like the max records setting either since it is an arbitrary cut off.  Instead, if you build your form's correctly and do a little pre-processing to ensure that criteria has been applied, you can count the rows selected by the criteria and if the count exceeds some number, give the user the option to continue or to refine the criteria.
0
 
LVL 3

Author Comment

by:bfuchs
Comment Utility
Hi Pat,
Just want to clarify, which is the way you think would work better?

1- change the record source of the form according to what the user selects.
2- leave the original record source intact, just apply the forms server filter.
3- have a function/stored procedure with a parameter that returns a recordset in the BE  and you send the filter string as a parameter.
4- Perhaps you have additional methods that you're referring to?

Thanks,
Ben
0
 
LVL 45

Assisted Solution

by:aikimark
aikimark earned 208 total points
Comment Utility
I think that if you're returning 1.5M rows to a form, then you aren't doing sufficient filtering in the back-end database.
0
 
LVL 3

Author Comment

by:bfuchs
Comment Utility
no, that's not always the case, let me explain in detail.
we have for example an EmployeesNotes table that contains all those records.
now we have a continuous form with a filter section on top, where the user usually selects what they want to filter for, now what happens if they don't select anything to filter for, just click on the view records button, it brings all records of that table.
I guess you want me to restrict users from doing that, but what happens if they do want (for whatever reason) see all the records?
0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 45

Assisted Solution

by:aikimark
aikimark earned 208 total points
Comment Utility
* Make a business case against returning all the records (time, resources, inability for a human to process that much data, etc.)
* Automatically add a Top # operator to the Select statement if there is no filter.
* Use windowing for the row viewing.
* Pop up a nag screen or force the user to supply a filter
* Issue a Count(*) query with the user's filter criteria and add a Top # if the number of rows exceeds 5000-10000.  I would have said 1k-2k, but I'm giving your users the benefit of the doubt.
* If the user needs the data at the bottom, work with them to find a filter that will retrieve those rows.
0
 
LVL 45

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 84 total points
Comment Utility
Sorry to back to this question but there's some management decisions that I can't understand.

MS Access 2000 mainstream support ended 10 years ago and the extended support ended 5 years ago.
MS Access 2010 mainstream support will end next year so suggest him to move to MS Access 2013.

MS SQL Server 2005 mainstream support ended 3 years ago.
MS SQL Server 2012 mainstream support will end in 2017 so suggest him to move to this version.

Or he can't see the risks by using products that aren't supported anymore?
Plus what he's loosing by not using the new functionalities and he prefer to spend time (and money) trying to fix the problems every time they appear, like in this case?

Cheers
0
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 208 total points
Comment Utility
The problem Victor is that the OP is using an .adp so conversion to A2013 is not possible at all since the feature was deprecated years ago.  Internally .adp's work differently from .mdb/.accdb's and require substantially more code so converting from .adp to .mdb/.accdb is not trivial.  I would convert to the newest version of Access that still supports the .adp and leave it at that.
0
 
LVL 3

Author Comment

by:bfuchs
Comment Utility
Hi Experts,

@aikimark
I see what you're saying, guess if this problem will appear again & no other solution will be in place, I will be forced to apply that.

@Victor, Pat
Let me tell you a little of our history and you will get an idea whats going thru our managers mind...
We had an old large access mdb back in version 97 that was converted to 2000.
At one point we had an independent consultant that convinced us to upgrade to ADP, he gave us an estimate of 4 months, however we had to hire a team of programmers to complete the conversion and it took at least double the time he had estimated, don't need to tell you what all this summed up in$$..
Now if I need to convince him to do another conversion..
a-downgrade from ADP to MDB/Accdb just because microsoft is not supporting it..
b-purchace new licenses of Office to all users for every new version that get released.
c-even just to change the app version to accommodate the new programming environment.

The first thing he would tell me is, please present to me a reason or perhaps a list of reasons that would justify such a investment,

Secondly, he would ask if I have already an answer to all things that don't work at all, (or don't perform well) in later versions, like the one i posted above.

and here I have another one:


And my experience is that with every new version, Access performance gets a big turn back, the only nice thing I really enjoyed was the ADP feature, and this got abandoned by the MS..


Thanks,
Ben
0
 
LVL 34

Accepted Solution

by:
PatHartman earned 208 total points
Comment Utility
Most people didn't consider the .adp to be an "upgrade" and so didn't adopt it.  Any properly designed .mdb/.accdb could use linked tables and get approximately the same performance.  You might occasionally need to create stored procedures, views, or pass through queries but you would still be able to use bound forms and all the benefit that entails.  The other problem with the .adp was that it was tied to specific versions of SQL Server and so wasn't easily upgraded.

If you want to stick with the .adp, you won't be able to go past A2010.  A2013 didn't offer anything for the client/server app so there is no loss there.

You don't need to purchase licenses for each user.  Only the developer needs a full version of Access.  Everyone else can use the runtime which is a free download.

You could try converting to A2010 and then upgrading SQL Server to 2012.

When you say "previous version of the app", I presume you are talking about your app.  Make a backup of this version.  Then make a minor change and recompile the app.  Does it fail?  If it does, the problem is with something that MS changed in the most recent patch you have applied for Access.
0
 
LVL 45

Assisted Solution

by:aikimark
aikimark earned 208 total points
Comment Utility
You can test the run-time user experience by changing the file extension to .accdr
0
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 208 total points
Comment Utility
@aikimark,
I'm not sure that will work with an .adp but it might.  If it doesn't work, then you can create a shortcut that includes the /r switch.  It's been a while so it might be /runtime.
0
 
LVL 45

Assisted Solution

by:aikimark
aikimark earned 208 total points
Comment Utility
My comment only applies to an upgrade/migration scenario.
0
 
LVL 3

Author Closing Comment

by:bfuchs
Comment Utility
Hi Experts,

At this point, since the original problem is gone and so far didn't return yet... I decided not to do anything yet as my manager will anyway not approve it, (unless as mentioned, I can present him a list of benefits we will gain with those upgrades), but at least I have saved all your valuable input, so in case this ever happen again I will present him with those options, and maybe then he will reconsider..

Thanks very much!!
Ben
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now