DataWriter

November 13, 2010

An Access Bound Form Reminder

Filed under: Uncategorized — Joe Casella @ 3:03 pm
Tags: ,

I was recently called in to help troubleshoot a query that was being used as the record source of an MS Access form.   The query’s execution plan looked fine and it executed quickly through SSMS.  In Access, however, the query took about twice as long to run.  I ran profiler, and to my surprise (and the developer’s), we found that the query was actually executing twice.    

 The culprit was the following lines of VBA code behind his button:

frm.RecordSource = sSQL
frm.requery

The developer’s intent was straightforward: he wanted to make sure the form had the latest data after the  record source had changed. It was also unnecessary.

A bound control’s recordsource property connects it to a table or query. When an open control’s recordsource is changed, it automatically requeries the underlying data. The requery method also runs the query on which the control is based.

In the case above, the developer was unintentionally querying the source twice! Simply removing the extra requery was all it took to cut the runtime in half.

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: