Access 2010 - Help in using CacheStart

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.

Eileen MurphyIndependent Application DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
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.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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"

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

Optimizing Microsoft Office Access Applications Linked to SQL Server

 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.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial

the theory behind that is explained here:

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.


The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
BTW, if your looking for an actual example, one can be found here:

Eileen MurphyIndependent Application DeveloperAuthor Commented:
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
"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;

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
(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.

@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:

@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.


"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.

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.
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!
Eileen MurphyIndependent Application DeveloperAuthor Commented:
Fantastic! Do you all recommend ADODB? And --- is it worthwhile to spend the time changing everything from DAO?
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.
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.


"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.
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.
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

Eileen MurphyIndependent Application DeveloperAuthor Commented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.