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.
Thanks!
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!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
http://msdn.microsoft.com/en-us/library/bb243548%28v=office.12%29.aspx
Jim.
ASKER
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 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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
@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
@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
"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
Eileen,
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.
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!
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!
ASKER
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.
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.
Cheers,
Christian
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
"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
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
ASKER
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.
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