Solved

subform efficiency, or is there another way?  plus executing a SQL stored procedure

Posted on 2004-04-22
9
515 Views
Last Modified: 2008-02-20
I am developing an application that uses an Access 2003 .adp GUI and a SQLServer database.  I have read elsewhere that using subforms in the GUI will cause a performance hit because of extra unnecessary database calls.  Can anyone comment on this?   I have been attempting to avoid using subforms because of this but I think I need to use one because I essentially need a multi-column listbox where each of the cells is updatable rather than just read-only.  

Specifically, my form contains a dropdown with a list of names.  When a name is selected (AfterUpdate event), I want the subform/listbox to be populated so that individual items can be edited.  I made a multi-column listbox based on a SQL query, but the data is read-only.  Is a subform the only way to accomplish this and if so, any suggestions on how to keep the performance as efficient as possible?  

Finally (and this is kinda unrelated so I will spread the points around if necessary), whichever display method I use (subform or some wonderful updatable listbox), I would like to populate it from a stored procedure with a parameter rather than a query since the query is somewhat wordy.  If someone can point me to an example of this I would appreciate it.  

Thank you.
0
Comment
Question by:rsoble
  • 5
  • 4
9 Comments
 
LVL 1

Author Comment

by:rsoble
ID: 10892526
I guess it's a busy day for you Access Wizards & Gurus.  Let me summarize my questions and add a few points...

1) are there performance issues involved with using subforms with a SQL Server backend?

2) if using a subform is suboptimal, is there any other way to have a one-to-many multicolumn UPDATABLE list?

3) code example of how to populate subform or listbox using a stored procedure with a parameter.  

Thank you.
0
 
LVL 54

Accepted Solution

by:
nico5038 earned 500 total points
ID: 10912585
Hi rsoble,

Without the article that poses the performance problem with subforms I'll have to guess why they do state this.
I think they are referring to so-called "linked" subforms.
Normally you use a subform in access to "follow" a relation. (E.g. OrderDetails linked to a main Order Form)
When you link such an order detail subform access will synchronize the main and the subform and thus issue a query to extract the set of detail rows for the order. Perhaps that's the mechanism the article is pointing to as being a slowing down factor.
When you fill the subform yourself I can't see any performance issue.

For the .adp and SP part I regrettably have only some theoretical knowledge so I can only give you this MS link:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/off2000/html/IDH_acdefAccessProject.asp
hoping it's holding some more information for you.

Nic;o)

0
 
LVL 1

Author Comment

by:rsoble
ID: 10912897
The article I read that discouraged the use of subforms is here:
http://sql-server-performance.com/access.asp.  
The particular paragraph reads:
"If possible, avoid using subforms in your Access forms. Subforms require at least two queries to be issued to SQL Server and require more overhead. Instead, use a query to join the tables you need and display the results in the form. This only requires one query to be sent to SQL Server and has much less overhead. Most columns from multiple-table tables can have data inserted or updated into them, so subforms can often be avoided. [6.5, 7.0, 2000]"

I don't understand the alternative they are suggesting.  I do want to use the form as you describe:  I would have the Orders in a listbox, and then when one is selected I would populate the subform with the OrderDetails (from the event handler code of the listbox).  

Thanks for your help.
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 1

Author Comment

by:rsoble
ID: 10912914
BTW that link was helpful about the SP question.  I followed it to here:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsmart00/html/sa00f10.asp  which looks to be very useful.  

Interesting how they say "To get the most out of Access Data Projects, you must use stored procedures. But trying to use them for some of the most common tasks isn’t covered in the documentation."  

haha  -- ain't that the way it always is?

0
 
LVL 54

Expert Comment

by:nico5038
ID: 10913025
Hmm, the article is from 2000/2001 and so not taking the Access Data Project in account as far as I can see.
They are right that you'll need to issue separate queries for the main/subform and indeed one query joining two tables will be faster.
Personally however I always start developing "straight forward" and with the most "heavy" queries first.
Only when performance proves to be a problem I'll start looking into alternatives as often more time is spent looking into alternatives and coding for a possible "worst case" that never is reached in the running application.

Remains ofcourse the question why to use access .adp while you could also use other languages like ASP to access the MS SQL database.

About the quality of the MS link, you found out why I often get irritated looking into their knowledge base :-)

Nic;o)
0
 
LVL 1

Author Comment

by:rsoble
ID: 10913250
This previous question explains why I'm using .adp.
http://www.expertsexchange.com/Databases/Microsoft_SQL_Server/Q_20949132.html
In retrospect I realize I didn't ask my question very well since nobody mentioned .adp as a possible solution (or perhaps it was too obvious but I didn't know it existed).  I found out about that possibility on my own afterwards and immediately knew it was what I needed.

I think your idea is a good one - just use the subform and observe the performance before I go to lengths to avoid it.  Especially since this is a relatively small system (but the users have been burned by performance issues because of previous poor design so they are extra sensitive to the issue).   Also this is phase one of a long redesign process so I'll have time to see it in action while I'm still working on it.

I'll leave the question open another day or so in case any other great ideas come along.  Thanks for your help!
0
 
LVL 54

Expert Comment

by:nico5038
ID: 10913306
You're welcome.

Nic;o)
0
 
LVL 1

Author Comment

by:rsoble
ID: 10928175
Thanks for your help Nico.  I had a little more trouble with the stored procedure stuff but finally worked it out thanks to finding this PAQ:
http://www.experts-exchange.com/Databases/MS_Access/Q_10028840.html
0
 
LVL 54

Expert Comment

by:nico5038
ID: 10928255
Glad it all worked out, great job rsoble !

Success with the application !

Nic;o)
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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…

803 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