Drew Wutka
DWUTKA at Marlow.com
Wed Feb 17 18:23:40 CST 2010
The problem is two fold. First, Jet is HORRIBLE with custom functions. Second, the way the query is setup (I downloaded the database from the link you posted), it is going to run itself in an exponential manner. I've emailed you offlist a modification of the original database. It has two global collections, one of the addresses keyed by the ID field, and one keyed by the fixed address result. The query is modified so that it retrieves the 'fixed address' and the 'count of similar addresses' from two functions that then retrieve the data from the collections. If those functions error out (cause the collections haven't been initialized), they run the initializing function. I upped the original 'data' table to about 90k records (all duplicates of the original data). It takes a few seconds to run through all the records, but then retrieving the data from 'data functions' for the query is lightning fast. Hopefully this gets the ball rolling for you. There is one more obstacle to overcome. If users are editing these, the original would requery the subform (it still does this), which will go fine, except the collections aren't being rebuilt. So you would need to rebuild the collections along with the background. And one more tidbit, this solution is a little hackneyed cause I'm cramming a class/solution problem into a bound query. This same process would work better without the query at all, using a treeview. Would look far better, and be easier to update. Holler if you'd like to see something like that instead. Drew -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of William Hindman Sent: Wednesday, February 17, 2010 3:29 PM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] ...for AD and anyone with an idea ...tks Jim http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=76 is the mdb that has the code ...I'm reluctant to post the code here w/o AD's permission ...basically it's a function that's being called in a query that "normalizes" standard address things like St Str Street etc and then compares them for duplicates ...really nice code that is easy to add functionality to and it runs fine on small record sets ...but there's the killer. William -------------------------------------------------- From: "Jim Dettman" <jimdettman at verizon.net> Sent: Wednesday, February 17, 2010 4:02 PM To: "'Access Developers discussion and problem solving'" <accessd at databaseadvisors.com> Subject: Re: [AccessD] ...for AD and anyone with an idea > William, > > I haven't seen the code, but using .Seek on an index is the fastest way > to > find something with JET. > > With 38K+ records, your definitely in that territory if you want fairly > quick (sub second) response. > > Jim. > > -----Original Message----- > From: accessd-bounces at databaseadvisors.com > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of William Hindman > Sent: Wednesday, February 17, 2010 2:42 PM > To: Access Developers discussion and problem solving > Subject: [AccessD] ...for AD and anyone with an idea > > AD > > ...I'm trying to implement your Match Similar Address sample (from Roger's > site) in a client app. > ...I'm working against a linked table w/38k+ records and it is impossibly > slow > ...I imported the relevant fields into a local temp table and replaced the > DCount with a standard replacement function > ...its faster ...but only marginally ...still impossible to deploy to > client > > users > > ...any ideas on how I can speed this up? > > ...I considered splitting the records using some criteria but that > essentially eviscerates the primary purpose of the code in the first > place. > > ...any ideas appreciated ...it's a sweet little piece of code and works > well > > on small tables ...but that's not what I have. > > William > > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com > -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com The information contained in this transmission is intended only for the person or entity to which it is addressed and may contain II-VI Proprietary and/or II-VI Business Sensitive material. If you are not the intended recipient, please contact the sender immediately and destroy the material in its entirety, whether electronic or hard copy. You are notified that any review, retransmission, copying, disclosure, dissemination, or other use of, or taking of any action in reliance upon this information by persons or entities other than the intended recipient is prohibited.