Drew Wutka
DWUTKA at Marlow.com
Thu Feb 18 11:21:54 CST 2010
How diehard set are you on keeping the address correcting portion in Access? I ask, because while you can create a neat treeview interface in Access, you can create a whamadyne one in VB. In posting Treeview help here in Access, one of the limitations that I discovered that Access has when dealing with treeviews, is menus. You can't create (very easily) popup menus like you can in VB. For example, in VB, I can give you a treeview that displays all the 'in dispute' addresses, and then with a right click, gives a menu list of suggested addresses to change it too. Can't do that in Access, at least not very easily. I think you can use API's to create similar popup menus that you get in VB, but I must admit a complete lack of experience in creating those kinds of heavily code intensive menus. The advantage of a treeview (in Access or VB), is that the collection/class driven method would link to such a structure so that you can modify an individual component of the collection without having to be tied to a live data source. In the example I sent you offline, any change is to the data source, and you'd have to refresh the collection in it's entirety. With a Treeview, you are refreshing the data in memory (which can then update the data in the table), but you don't have to constantly rebuild the collection. I'm pretty sure you might be resistant to a VB 6 solution since it sounds like you have an app already in access. Maybe an ActiveX control made with a VB Treeview..... Drew -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of William Hindman Sent: Thursday, February 18, 2010 8:59 AM To: Access Developers discussion and problem solving Subject: Re: [AccessD] ...for AD and anyone with an idea 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 > -- 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.