William Hindman
wdhindman at dejpolsystems.com
Thu Feb 18 08:58:56 CST 2010
Drew/AD ...amazing ...thanks for both your efforts ...on first look both appear to solve the problem, although AD's is a bit easier to implement from my perspective ...but I'm withholding further changes on my end in case Drew wants to feed me his proposed treeview solution ...I think I've mentioned many times how much I like treeviews :) ...note that I'm a bit reluctant to use a collection for this since it eats memory and this app already uses a large collection for mapping a trade show floor plan and it will be open simultaneously on several user's desks throughout the workday ...I also expect this Address Matching form will be open for extended periods of time on one or more workstations since there is a myriad of dirty data to be corrected ...thus I suspect I might run into some memory hogging problems with another large collection as a solution here. ...as always, I'm open to being taught or corrected :) William -------------------------------------------------- From: "Drew Wutka" <DWUTKA at marlow.com> Sent: Wednesday, February 17, 2010 7:23 PM To: "Access Developers discussion and problem solving" <accessd at databaseadvisors.com> Subject: Re: [AccessD] ...for AD and anyone with an idea > 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. > > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com >