[AccessD] ...for AD and anyone with an idea

Drew

...truth be told I'd never let a user fix the address anywhere except in my 
own form bound to the actual data ...my intent is to modify the final 
approach to open a form from the recordID when the user needs to correct or 
merge a record

...I'm not adverse to ActiveX except for version control problems ...if it 
was built in vb.net I'd be ok with it as long as the source was available.

...afaik both VB and Access use the same MSCOMCTL (SP6) to produce the 
treeview ...I've started using the Listview from that same control to 
replace listboxes where possible ...my whole menu system is built on a 
treeview ...if it can be done in vb, then it can likely be done in Access 
ime ...limited though that is ...but I do a lot of api stuff with my 
treeview that most Access references say can't be done

...don't kill yourself over this ...much as I like the treeview, I have a 
solution in hand that I can adapt to make it work well as a standard plug-in 
for my apps ...a treeview would just be icing on the top :)

William

--------------------------------------------------
From: "Drew Wutka" <DWUTKA at marlow.com>
Sent: Thursday, February 18, 2010 12:21 PM
To: "Access Developers discussion and problem solving" 
<accessd at databaseadvisors.com>
Subject: Re: [AccessD] ...for AD and anyone with an idea

> 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.
>
>
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>