[AccessD] Find First in an Array?

jwcolby jwcolby at colbyconsulting.com
Thu Feb 19 22:47:11 CST 2009


When you can write classes in your sleep, refactoring to a class is a 1/2 hour task so the "refactor 
time" isn't really an issue.  In Rocky's case it truly would be a refactor task.  If he has solved 
his speed problem I am going to say "job done" and let him go to sleep.

John W. Colby
www.ColbyConsulting.com


Drew Wutka wrote:
> Hmmmmm, probably true.  The best way to go is still with a class and
> collection.  But reversing the logic to loop through the recordset is
> probably the best solution, between speed and least coding....
> 
> Drew
> 
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Salakhetdinov
> Shamil
> Sent: Thursday, February 19, 2009 6:04 PM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Find First in an Array?
> 
> Hi Drew,
> 
> But the point isn't that ".Seek is faster then simply running through
> the recordset" - the point is that .Seek is good(/speedy) enough and
> that using .Seek involves the least amount of code refactoring work for
> Rocky AFAIS - please correct me if I'm wrong...
> 
> Thank you.
> 
> --
> Shamil
> 
> -----Original Message-----
> From: "Drew Wutka" <DWUTKA at marlow.com>
> To: "Access Developers discussion and problem
> solving"<accessd at databaseadvisors.com>
> Date: Thu, 19 Feb 2009 17:38:01 -0600
> Subject: Re: [AccessD] Find First in an Array?
> 
>> But how could seek be any faster then simply running through the
>> recordset?
>>
>> Drew
>>
>> -----Original Message-----
>> From: accessd-bounces at databaseadvisors.com
>> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
> Salakhetdinov
>> Shamil
>> Sent: Thursday, February 19, 2009 3:40 PM
>> To: Access Developers discussion and problem solving
>> Subject: Re: [AccessD] Find First in an Array?
>>
>> You decide, Rocky,
>>  
>> Do you have time for that experimenting?
>>
>> May I warn you about what is known as:
>>  
>> "Premature Optimization"
>> http://c2.com/cgi/wiki?PrematureOptimization 
>>  
>> and 
>>  
>> "Premature Generalization" 
>> http://c2.com/cgi/wiki?PrematureGeneralizationIsEvil 
>>  
>> ?
>>  
>> Have a look:
>>  
>> "Death by premature generalization" 
>> http://ryanfarley.com/blog/archive/2004/04/30/570.aspx
>>  
>>  
>> My name is Shamil, you know :) and I have been a premature generalizer
>> in many cases I must admit - but I'm getting more and more XP/SCRUM
>> agile habits these days, and I'm getting more fun from programming
> real
>> life business tasks without my premature generalization and
> optimization
>> not so good(?) past habits...
>>  
>> IMO in the case of the context of this thread using .Seek is the
> closest
>> and quickest yet good enough (and maybe the only one needed for many
>> years ahead) solution/transition from your existing .FindFirst-based
>> code...
>>  
>> If that would not be good/speedy enough in your case then I'd
> probaably
>> use static array loaded on first call/after reset using .GetRows()
> with
>> accompanying static collection with FormName/ControlName as a key to
>> keep value for static array entry index, and a function to get
>> translation value using this collection/array...
>>  
>> etc...
>>   
>> Thank you.
>>  
>> --
>> Shamil
>>
>> -----Original Message-----
>> From: "Rocky Smolin at Beach Access Software" <rockysmolin at bchacc.com>
>> To: "'Access Developers discussion and problem
>> solving'"<accessd at databaseadvisors.com>
>> Date: Thu, 19 Feb 2009 09:56:55 -0800
>> Subject: Re: [AccessD] Find First in an Array?
>>
>>> Dang.  I think you're right.  Gotta try that.  You think it'll be
>> faster
>>> than SEEK? 
>>>
>>>
>>> Rocky Smolin
>>> Beach Access Software
>>> 858-259-4334
>>> www.e-z-mrp.com
>>> www.bchacc.com
>>>  
>>>  
>>>
>>> -----Original Message-----
>>> From: accessd-bounces at databaseadvisors.com
>>> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Drew
> Wutka
>>> Sent: Thursday, February 19, 2009 9:33 AM
>>> To: Access Developers discussion and problem solving
>>> Subject: Re: [AccessD] Find First in an Array?
>>>
>>> Hmmmm, actually, the way you have this set up, you could do this
> just
>> fine
>>> without an array or collection.
>>>
>>> Instead of this:
>>>
>>> For Each ctl In frm
>>>     rstControls.FindFirst "fldLanguageForm = '" & argForm & "' and
>>> fldLanguageControl = '" _
>>>         & ctl.Name & "'"
>>>
>>> Do this:
>>>
>>> strSQL="SELECT fldLanguageControl, fldLanguage" &
>> strLanguageToTranslate & "
>>> FROM tblYourTableName WHERE fldLanguageForm=""" & me.Name & """"
>>> set rs=New Recordset
>>> rs.open strsql,currentproject.connection,
> adopenkeyset,adlockreadonly
>> if
>>> rs.eof=false then rs.movefirst do until rs.eof=true
>>> 	me(rs.fields(0).value).Caption=rs.fields(1).value
>>> Loop
>>> Rs.close
>>> Set rs=nothing
>>>
>>> That way you are pulling the recordset up....and just running
> through
>> it
>>> once...instead of trying to find every record based on the control.
>>>
>>> Drew
>>>
>>> -----Original Message-----
>>> From: accessd-bounces at databaseadvisors.com
>>> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky
>> Smolin at
>>> Beach Access Software
>>> Sent: Thursday, February 19, 2009 12:57 AM
>>> To: 'Access Developers discussion and problem solving'
>>> Subject: Re: [AccessD] Find First in an Array?
>>>
>>> John:
>>>
>>> The table layout is:
>>>
>>> fldLanguageID             Autonumber
>>> fldLanguageForm           Text Name of the form that the control is
> on
>>> fldLanguageControl        Text Name of the control 
>>> fldLanguageControlType    Text Not Used
>>> fldLanguageEnglish        Text
>>> fldLanguageChineseComplex Text
>>> fldLanguageChineseSimple  Text
>>> fldLanguageSpanish        Text
>>> fldLanguageFrench         Text	
>>>
>>> In the translate routine I use:
>>>
>>> Set frm = Forms(argForm)
>>> For Each ctl In frm
>>>     rstControls.FindFirst "fldLanguageForm = '" & argForm & "' and
>>> fldLanguageControl = '" _
>>>         & ctl.Name & "'"
>>>
>>> Where argForm is the form name passed to the function.  So I think I
>> might
>>> get a big boost in response time by indexing fldLanguageForm and
>>> fldLanguageControl.
>>>
>>> There are only about 2500 records in the table so I'm thinking that
>> the
>>> whole recordset is probably in memory anyway.
>>>
>>>
>>> Rocky Smolin
>>> Beach Access Software
>>> 858-259-4334
>>> www.e-z-mrp.com
>>> www.bchacc.com
>>>  
>>>  
>>>
>>> -----Original Message-----
>>> From: accessd-bounces at databaseadvisors.com
>>> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
>>> Sent: Wednesday, February 18, 2009 5:46 PM
>>> To: Access Developers discussion and problem solving
>>> Subject: Re: [AccessD] Find First in an Array?
>>>
>>> Does this imply a table structure of:
>>>
>>> TR_ID		Auto
>>> TR_CtlName	Text
>>> TR_L1		text (or memo?) for language 1
>>> TR_L2		text (or memo?) for language 2
>>> Etc
>>>
>>> I would suggest that you add a form (or container) field.  A control
>> name is
>>> unique on a form, however it could be the same on different forms
> but
>> have a
>>> different language string.  By having a form field you could pull
>> subsets of
>>> records based on the form name, then use the control name (now
>> guaranteed to
>>> be unique), then obtain the language string.
>>>
>>> It appears from your email however that you already have this stuff
>> set up,
>>> so you might be resistant to modifying how it works.
>>>
>>> If this is all just one big table with control names guaranteed to
> be
>> unique
>>> then you could just stash it in one big collection.  There is a
>> problem
>>> however which is that classes get pretty slow as they get large.  If
>> you get
>>> up past 10,000 strings (I don't know the exact point) it would bog
>> back
>>> down.  If you broke it down into controls on a form, then you would
>> ensure
>>> that the total strings in any given collection is pretty small.
>>>
>>> Again I would build a class for all the code and the collection to
>> load one
>>> form.  then a supervisor class to hold instances of this form class
>> keyed on
>>> form name.
>>>
>>> John W. Colby
>>> www.ColbyConsulting.com
>>>
>>>
>>> Rocky Smolin at Beach Access Software wrote:
>>>> Max:
>>>>
>>>> That would work except the table driven approach is so much more
>>> easier.
>>>> Easy to add a language (got French and Spanish now in addition to 
>>>> traditional and simplified Chinese), too, or make a change to a
>>> translation.
>>>> To add a language I just add a column for that language to the
>> Control
>>>> and Messages tables and send them to the translator.  Add that 
>>>> language to the language selector combo on the Preferences form,
> and
>>>> walla! Another language.
>>>>
>>>>
>>>> Rocky Smolin
>>>> Beach Access Software
>>>> 858-259-4334
>>>> www.e-z-mrp.com
>>>> www.bchacc.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
>>>
>> -- 
>> 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
>>
> 



More information about the AccessD mailing list