Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old April 15th, 2005, 07:18 AM
Authorized User
 
Join Date: Apr 2005
Posts: 16
Thanks: 0
Thanked 0 Times in 0 Posts
Default

It does look like pass-through is the way to go. I'll change all my queries.

mmcdonal - your last post explains what is happening. I have a form that has 3 tabs, each showing data relevant to different departments (telesales, Maintenance and inventory) I'm assuming that each time that form is opened data for all three tabs is being retrieved. How do I tell the form to only retrieve the data when the tab is clicked?

 
Old April 15th, 2005, 07:19 AM
Authorized User
 
Join Date: Apr 2005
Posts: 16
Thanks: 0
Thanked 0 Times in 0 Posts
Default

All my queries access SQL tables, so can they all be converted? Thanks for your patience :)

 
Old April 15th, 2005, 07:32 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

A pass-through query just drops the jet engine activity, and passes it through directly to the DSN for your dbms (DSN's have their own performance issues, but they are very solid, so I always use them.)

If you are using your pass-through query to select ALL the records in a table, then even though the query is limited to the data requested, it is requesting all the data, which a query can do, of course. SO the only benefit would be to remove the jet engine from the equation - which can be a good performance gain.

So limiting calls to remote back ends is always a good strategy. How you do this depends on what method you are using to call the data.

I link tables in my Access front ends, and then generally make limited calls directly to the linked tables with code instead of using pass-through queries to call records. Both methods are valid. It just depends on what you are doing.

To get you started, you could build a combo box on your main form with a drop down list of meaningful data so that your users can preselect the data. Then put a button beneath to call the form, and use the on-click event of the button to call the form with the pre-selected record. Alternatively, you can limit the call from your current button to the first record on your form only. You will see an immediate increase in speed if you do this.

Then use this code on a combo box After Update event on your form that looks up records from the main table:

'=====
Me.RecordSource = "SELECT * from tblUserName WHERE UserID = " & Str(cboUserName)
'=====

This example is from my User Name table which is a personnel record core for my inventory management dataabase.

This will call one UserName record at a time, and then call ONLY those records on the subform related to that one record.

If you use this sort of technique on your form, you will only populate the tabs with data for the current record, and this will be very fast.



mmcdonal
 
Old April 15th, 2005, 07:43 AM
Friend of Wrox
 
Join Date: Nov 2004
Posts: 248
Thanks: 0
Thanked 1 Time in 1 Post
Default

You can limit as mmcdonal suggests by not setting the RowSource or RecordSource until you have collected values from the user to limit the amount of data being returned.

As far as the tabs go, I'm assuming that the data displayed on each tab is in a subform, you can set the SourceObject of the form control to blank until the tab is selected. Use the On Change event of the tab control to see when the tab changes. The value of the tab control is set to the Page Index of the page that is selected. Set the SourceObject to whichever tab is selected. I've had some trouble with the subform not being refreshed when I do this. You may need to Requery the subform object to make it show data. You can also set the RecordSource of the subform to blank until the tab is selected. The syntax you'll need is...

Me.subformcontrolname.Form.RecordSource = "Select statement"

The key being the ".Form." part.

I would say all queries CAN be converted. There is a trade off between development/testing time and the amount of performance gained. As long as you are sufficiently limiting the data, changing everything to pass-through is just adding more work. So check to see that you're limiting data first.

Randall J Weers
Membership Vice President
Pacific NorthWest Access Developers Group
http://www.pnwadg.org
 
Old April 15th, 2005, 07:47 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

I agree with you on the tabs issue. Since you will be limiting data on the main form to one record, populating the tabs normally should not be a big deal, and the other method can be problematic. I have a medical surveillance database that shows one record at a time with 4 tabs and tons of patient records in each tab, and loading is not an issue, even from an Access back end.



mmcdonal
 
Old April 16th, 2005, 11:15 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 217
Thanks: 0
Thanked 1 Time in 1 Post
Default

Pardon my interruption, I have a related question and situation, and was planning on trying late binding with a tab control to see if it would speed up load for a set of forms in our db, and am now wondering if it is worth the effort.
In my situation, we are using Access only- no SQL Server. The form loads after the user chooses which provider record they want to review (so there is only 1 record in the RS of the form). There are approx. 10 tabs on the largest form, and about 8 of the tabs contain a subform. I have not split the DB as it takes approx. 24 seconds to load the form when split (and that was when it had a fraction of the records it now contains). The mdb is on a server and 9 people use it.
Being we're using Jet, is there anything to be gained with trying late binding of the subforms? (I thought I had read somewhere that with Jet the entire rs was delivered to the local machine for the local machine to sort out.???) Any advise?

 
Old April 17th, 2005, 09:16 AM
Friend of Wrox
 
Join Date: Nov 2004
Posts: 248
Thanks: 0
Thanked 1 Time in 1 Post
Default

I would definitely say that you can improve the initial load performance of the form if you don't have a SourceObject for subforms on tabs that might not be seen. I and others use this technique with positive results.

It occurs to me that you can probably improve the performance of the subform loads by not relying on the Master/Child link fields to fetch the appropriate data. I haven't tried this or heard of anyone who has. The idea being that instead of having Access (Jet) sort out the data to display on the form by limiting to the link fields, your RecordSource to the subform should include the Where clause to limit the data.

This all depends on whether or not for subforms Access "gets the data, then limits the data after it's been retrieved" versus "get the data limited to the Master/Child link fields." I don't know how well it is tuned in that regard. I'll ask the Access developers Tuesday at the PNWADG meeting.

The trick will be getting the RecordSource of the subform into the subform before Access starts to query the database. Perhaps the Where of the subform's RecordSource could be built to refer to the Parent form's ID with something like [Forms]![formname]!ID? Then you won't need the Master/Child link fields.

It would be an interesting experiment. Right now I don't have a database where that kind of performance is an issue.

Randall J Weers
Membership Vice President
Pacific NorthWest Access Developers Group
http://www.pnwadg.org
 
Old April 17th, 2005, 03:10 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

rjweers, I would be very interested in what you come up with. In the meantime, I will look up this issue in Access advisor, since I remember an article on improving tab performance.

Loralee, why would splitting the database limit performance? I think you need to split, and then copy the front end from the network drive, and paste the whole file, not just a shortcut to it, to each users' desktop.

Many Access developers will just put shortcuts to a single Access front end file on the network to each users' desktop, and this will give you a big performance bottleneck. Try pasting a copy of the entire file to each users' desktop and see if that improves performance on a split database.

The only caveat when you do this is if you do more development on the front end, you need to get each user to delete the copy of the front end from their desktop, and copy and paste the new version. You would be surprised how many users think this deletes all the data, and this causes them to make a fuss. I had one user who never deleted the front ends from her desktop and had like 5 old versions that she couldn't bear to delete.



mmcdonal
 
Old April 17th, 2005, 03:12 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Sorry Loralee, another caveat is that all the users have to have a drive mapping to the same location for the back end using the same drive letters etc. Otherwise you will have to go to their desk, delete the links to the back end and then recreate them using their drive mappings.



mmcdonal
 
Old April 17th, 2005, 03:20 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Here is the link to the article that covers this topic on Advisor.com. You need to have an account to see it. I could send a copy to anyone interested, or post some of it.

http://my.advisor.com/articles.nsf/aid/14698


mmcdonal





Similar Threads
Thread Thread Starter Forum Replies Last Post
Multiple SQL Databases for Same Access Front End darrenb Access 0 May 27th, 2008 12:07 AM
Ms Access front End with Oracle 10g Back End rahul123 Oracle 1 July 9th, 2007 01:03 AM
Oracle back-end MS-Access 2003 client front-end Corey Access 2 February 16th, 2007 08:31 AM
Oracle Back End - MS Access Front End - Multi User ckaliveas Oracle 1 February 1st, 2007 06:00 AM
using msde and sqlserver with access front-end catkins Pro VB 6 1 April 1st, 2004 04:00 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.