[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

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

Posted on 2004-04-22
9
Medium Priority
?
542 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 1500 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

656 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