Solved

Access 2010 - Help in using CacheStart

Posted on 2014-11-12
15
404 Views
Last Modified: 2014-11-13
Hi Experts! I'm working on trying to speed up an Access 2010 application with an ODBC connection to SQL Server. I came across the CacheStart function and have no idea how to deploy it. I don't understand how too set the cache size, etc.

One form has  1.5 million records behind it.

Most combo-boxes are populated with Passthrough queries which have sped up the form to some degree -- but I really need help.

Thanks!
0
Comment
Question by:Ei0914
  • 5
  • 3
  • 3
  • +3
15 Comments
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40439485
Some years ago I did some testing using SQL Server 2008.
However, I couldn't observe any difference in behaviour or speed, so I left it.

You will probably be better off filtering the recordset so the form pulls fewer records.

/gustav
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 167 total points
ID: 40439694
Setting up a local cache for ODBC datasources has a limited scope.   As gustav said, you'd get far better "bang for the buck" by looking in other areas, such as filtering records.

 Here are two resources which may help:

"Best of both worlds"
http://www.jstreettech.com/downloads.aspx

 This contains a couple of excellent tips in using Access with an ODBC client/server BE such as SQL Server.

 Also:
Optimizing Microsoft Office Access Applications Linked to SQL Server
http://msdn.microsoft.com/en-us/library/bb188204(v=SQL.90).aspx

 This is more background info rather than anything specific, but there is a wealth of info here.   For example, adding a TimeStamp column in your SQL tables can be very helpful and this explains why.

Beyond the above, anything you can push server side for processing is a must, so things like pass-through queries, stored procedures, triggers, and views are all things you want to look at.

 Last, when working with queries in Access, avoid VBA expressions, JET specific SQL, or joins to local tables if you can.   If possible, JET will push a query server side.   If it can't do that, then quite often you will end up with one SQL statement getting sent to the server for every row of your query, and performance will be dismal.   Using ODBC Tracing is a good way to spot this.

Jim.
0
 
LVL 24

Assisted Solution

by:Bitsqueezer
Bitsqueezer earned 167 total points
ID: 40439697
Hi,

the theory behind that is explained here:
http://msdn.microsoft.com/en-us/library/bb243549%28v=office.12%29.aspx

As you can see, this is only to fill the local memory with a subset (or all) records of a recordset. That maybe would help a little if you try to filter data so that the data will be used out of the local memory but the problem is: When you open the form and open the recordset the data must be loaded once from the server so there is no better performance with that.

In case of 1.5 million records in one form (!) I strongly recommend the same as Gustav, it simply makes no sense to fill one form with such an amount of records. You should i.e. use a stored procedure (on the SQL Server) which you can supply with some parameters, then let the SP filter the data a lot and return only the data needed. In the end you get the result from the SP as a recordset which you can assign to the recordset property of the form (if you want an updatable form, use the SQLOLEDB driver and ADO, not a Pass-Through-Query).

I often had the issue in former database projects that using a form with around 100,000 records lets the Access filter crash heavily, and also the conditional formatting is nearly unusable with that amount.
1.5 million records is a no-go.

Cheers,

Christian
0
 
LVL 57
ID: 40439710
BTW, if your looking for an actual example, one can be found here:

http://msdn.microsoft.com/en-us/library/bb243548%28v=office.12%29.aspx

Jim.
0
 

Author Comment

by:Ei0914
ID: 40440087
Wow! Thanks all. I filter (and maybe there's a better way) the records by an ID on the main form.  I have criteria in the subform's query -- seemed faster to requery the subform that using the Link Master/Child. Any feedback on this would be appreciated.

I also changed the form layout. It was a tabbed form with a dozen subforms. I changed it to one subform and change the SourceObject of that subform as the tabs are clicked.  This really helped. Duh!

I created a stored procedure - but can't seem to get it to run. Despite the fact that I copied the ODBC connection string from within a Passthrough Query -- and Exec the procedure from within it -- it's not firing. I'll keep digging into that issue... I am going to try to push as much of the processing to the server.

There is a caveat here too that I neglected to mention. The SQL server dsn may need to be looked at too. The client environment is a mix of Windows 7 / Office 2010 and Virtual Machines (running on these machines) running XP / Office 2003.

They have a DOS Based Paradox application (running in the Virtual Machine) and I was brought it to migrate the application into the 21st century. Haven't seen a DOS app in 15 years or more.

I will review all of the material you have provided me here. I appreciated all your help.

Thanks. Eileen
0
 
LVL 26

Assisted Solution

by:Nick67
Nick67 earned 166 total points
ID: 40440371
"I created a stored procedure"
So you know that the records returned by a sproc are read-only, right?
It's one thing to populate the contents of a combobox with a sproc, or a report for that matter, and another thing to do that with a form.  With a form, you then have to handle how you are going to deal with editing because of the read-only nature of the sproc.

You know that the delimiters and wildcards for SQL Server are different than for Access, right?
Strings are delimited with single quotes -- and the best way to pass in dates is as a String.
I have a sproc like so
ALTER PROCEDURE [dbo].[qryMcClellandCredit]
      -- Add the parameters for the stored procedure here
      @ClientID int = 1,
      @StartDate smalldatetime = getdate,
      @EndDate smalldatetime = getdate


and the Access passthrough is
Exec qryMcClellandCredit 1 ,'01-Oct-12' ,'31-Oct-12 11:59 PM'
Parameters, in order, separated by commas

You must have linked tables, so the DSN pretty much has to be correct.
If you change the Options of the database to view the system objects, you can open MSsysObjects.  It has a column 'Connect'  Look for a working table, and copy the connect value.  That'll be what your passthrough will need as it's Connection string, preceded by ODBC;

CHANGE NOTHING IN THE SYSTEM TABLES.
That would be bad.

Getting to the ODBC connection string of the passthrough can be annoying.
Open the passthrough in Design View.  ALT-ENTER to open the properties window.
My connection is
ODBC;DRIVER=SQL Server;SERVER=MYSERVER\SQLEXPRESS;DATABASE=My_DB;Network=DBMSSOCN;Trusted_Connection=Yes
(It's a single line, html may wrap it in your browser)

The server's name is MyServer
The instance is SQLExpress, hence MYSERVER\SQLEXPRESS
The database is My_DB
and I use Windows Authentication.

Hope this helps.
Nick67
0
 
LVL 24

Expert Comment

by:Bitsqueezer
ID: 40440516
Hi,

@Nick: That's wrong, a SP is absolutely not read-only, it's the pass-through-query which is always read-only (so that's true also for views, UDFs...).

As I already said above, you can use ADO to open a connection to the server and retrieve an ADO recordset which can be set to the form's recordset property - simply because Access forms can use either DAO or ADO recordsets automatically.

Here are some examples:
http://support.microsoft.com/kb/281998/en
http://msdn.microsoft.com/en-US/library/office/ff835419%28v=office.15%29.aspx

@Eileen: If you use main and subform and you have such an amount of records you should not use the link criteria used in the subform container to link the IDs of both forms. It's better to use the Form_Current event of the main form to set the recordsource of your subform "manually". You must of course also set the Default value of the (maybe hidden) ID control in the subform in this case.

Cheers,

Christian
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 26

Expert Comment

by:Nick67
ID: 40440544
@Bitsqueezer
"So you know that the records returned by a sproc are read-only"
Perhaps I should have rephrased that.
The records returned to Access by the passthrough query of a sproc are read-only.

:)
Nick67
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40440770
Eileen,
I have criteria in the subform's query -- seemed faster to requery the subform that using the Link Master/Child. Any feedback on this would be appreciated.
I use this technique occasionally.  You just have to be careful to populate the foreign key yourself if the subform allows adds.  Do it in the subform's BeforeInsert event so you don't dirty the subform with your own code.

Static code lists that are used for combos can be kept in the FE in addition to the BE.  That way, your combos fill using the cached data but RI still happens on the server.  To support this, you either need to populate the code lists each time the app opens or give the user an option to do it on the fly as needed or something in between.  It really depends on how volatile the lists are.  Obviously clients, products, etc can't be handled this way.  For those long lists, you might try using a partial filter where the user types a few characters and then you run the query that brings back a smaller subset of the RowSource.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40440886
Other things to do with data-heavy controls is to muck with them on GotFocus.
They can start out with a placeholder RowSource, and only get a real RowSource after they obtain focus.
And if the user never touches them, they never hit the database hard.

If the control is REALLY data dense, you can add a second control to supply a filter.  I have a combobox with 350+ really abstruse strings in it (yes I know that isn't really many, but they make your eyes water)  So I have a second unbound combo with LimitToList = False with 8 nice substrings in it.  The AfterUpdate event of this combo changes the RowSource of the main one to strings Like * & the filter combos value & *  Moreover, with LimitToList =  False, the user can enter anything in there as a filter.

"One form has  1.5 million records behind it."
Depending upon the records (is it a multi-table, but editable, query?) Access may force all the records to be pulled.  In SQL Server, you can create Indexed Views.  These can be linked to in Access and remain editable -- but the query behind them is done on the server.  That can enhance performance as well.

I have a form that performs poorly when it is built on a local query, but not editable when built upon a passthrough.  On the other hand, only a very few controls actually need to be editable.  In front of those controls, I put a transparent textbox, and beside them a control button.  When the user enters what they thing is the control, the box is changed from transparent.  They enter the change they'd like, and click the button to commit it, and requery the underneath control.

More ideas, anyway!
0
 

Author Comment

by:Ei0914
ID: 40441018
Fantastic! Do you all recommend ADODB? And --- is it worthwhile to spend the time changing everything from DAO?
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40441037
DAO has been MS's recommend way of doing things since A2003.
In A2013 they deprecated ADP's, which were built upon ADO.

I suspect the ADO rug will get pulled out from under us sooner or later since ADO <> ADO.NET despite the naming similarity, and ADO is not being actively maintained.  That's my two cents, anyway.

Given MS's obsession with Azure & SharePoint I am not really sure any Access Experts views the future of Access with optimism.  That's my gloomy two cents.
0
 
LVL 24

Expert Comment

by:Bitsqueezer
ID: 40441297
It's the old rumour about dying of ADO. In fact the current version is 6.1 in Windows 7, where it was 6.0 in Vista and 2.8 in XP, you see it's continously adapted.

I personally go on using ADPs but that's another story (and I don't want to start a discussion about that here).

So to answer the question: It is not needed to change everything to ADO, you can use DAO and ADO each where it is more useful for you. In case of using a stored procedure in a form where it should be updatable ADO is the way to go (as this is simply not possible with DAO because MS has not spent more than read-only to pass-through queries - nobody knows why, doesn't make sense). Furthermore DAO can link to tables and views only, that's the second really not understandable thing - it would be no big problem to link to a SP in the same way because as you see with ADO, the forms can work with a recordset coming from a SP.

DAO needs to use the JET/ACE database engine all the time in between which costs performance and have issues with datatype conversion (that's why you always need a timestamp column in SQL Server tables for example). ADO works directly with T-SQL (like a Pass-Through-Query) and so it directly asks the server for data and return the data in a recordset, no conversion, not JET/ACE in between.

You can even use a main form with DAO and a subform with ADO if you want, no problem.

But it's also possible to work with DAO alone and instead of a SP you can assemble dynamic SQL in i.e. the form's Load event and execute that, has similar effect because you can add any WHERE parameters here, but it's only Access SQL and not the really powerful possibilities of T-SQL (did you ever wrote a recursive SELECT or a common table expression with cascading SELECTs in T-SQL? Try to do that with DAO and Access SQL...).

So my recommendation is: In case of a simple table/view SELECT DAO is enough, also dynamic SQL can help to add some parameters. Whenever you need more complicate things, use T-SQL and stored procedures instead. You can then use Pass-Through-Queries as long as your form should only display a list of records which don't need to be updatable, then you can add/open a detail form for one record which can be based on DAO to edit the contents. If you want to use T-SQL, stored procedures and updatable forms then there's only the way shown above to open the recordset in ADO and assign it to the form's recordset property. But this is of course the most difficult option of these, because you need some additional programming depending on your SP and your form. For example, it can happen that Access cannot use the F5 key to requery the form automatically so you may catch this key (with the Autokeys macro) and use an own method to re-execute the SP instead - and so on.

So you see, you should always know the tools available and choose the right ones for the right job. But nevertheless that doesn't change the fact that a form should NEVER show 1.5 million records at once, you need to develop a logic which only shows the records the user really need to see at once.

Cheers,

Christian
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40441594
"It's the old rumour about dying of ADO"
I have no idea about MS's plans.  But what you can google up isn't encouraging.
In Desktop-Dev, the page hasn't been updated since Vista.  
http://msdn.microsoft.com/en-us/library/windows/desktop/ms676506(v=vs.85).aspx
And the only thing said about ADO 6.0 is that it is functionally equivalent to ADO 2.8

Then there was when ADO got updated in Win 7 SP1 and it blew things up.
http://blogs.msdn.com/b/psssql/archive/2011/10/03/yes-we-made-a-mistake-and-are-finally-going-to-fix-it.aspx
It is not encouraging when the blurb starts with
"If you are still [italics are mine] using ADO in some of your code and tried to upgrade your build machine to Windows 7 SP1..."

"you see it's continously adapted."
I'm not seeing that, but your vantage point may vary.
From where I am standing it looks like MS is intent on ramming everything into SharePoint, despite the fact that the vast majority of the VBA coding community has no use for the vast degradation of functionality that entails.  All we can do is take our best guess about what will be supported over the long run and go with it.

This is a project in its inception "They have a DOS Based Paradox application (running in the Virtual Machine) and I was brought it to migrate the application into the 21st century."  In regard to MS Access -- are we at the FoxPro v8 point at the moment?

One wonders.  Things to think about.  Should this really be a VB.NET app instead?  Hell of a lot more overhead, but perhaps a longer lifecycle.

Maybe I am just November-gloomy

Nick67
0
 

Author Closing Comment

by:Ei0914
ID: 40441782
Thanks to all the experts who helped me today. And guess what? My app crashed and become corrupted beyond repair -- all the vba code is gone. I've tried everything to get it back but was not successful... I just implemented auto backups of the front end for every hour. The environment I'm developing in is not stable and I think they had a power surge today. Anyway -- onward and upward. Good night.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

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…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

707 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

12 Experts available now in Live!

Get 1:1 Help Now