[AccessD] Find First in an Array?

jwcolby jwcolby at colbyconsulting.com
Fri Feb 20 08:46:12 CST 2009


ROTFL.  Could be.  ;)

John W. Colby
www.ColbyConsulting.com


Rocky Smolin at Beach Access Software wrote:
> Uh-oh.  I think John is 'bound' to take issue with that. 
> 
> 
> 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 Jim Dettman
> Sent: Friday, February 20, 2009 5:43 AM
> To: 'Access Developers discussion and problem solving'
> Subject: Re: [AccessD] Find First in an Array?
> 
> John,
> 
>   I posted a couple of comments yesterday to you and Drew, which still
> haven't shown up on the list, but I'll answer the question you just asked
> with "It depends on how big the collection is".
> 
>   What I wrote yesterday is that I would use a global recordset variable,
> thus avoiding opening/closing the recordset repeatedly and I'd use seek.
> 
>   You mentioned that JET always goes across the wire and that's not true.
> If a page is in the local JET cache, then it won't.
> 
>   But here that is really not an issue because a language translation table
> is certainly going to be part of the front end, so the table will be local.
> It's not going to change unless the app changes.
> 
>   Rocky has been talking about pulling 2500 records; if all that is pulled
> into memory, that is a fair sized chunk.  I'd much rather let the system use
> that memory as it sees fit rather then tying it up with one specific task.
> If I have an application that is a couple of hundred forms and user Jim D
> uses only one form, then that's a big waste.  Of course you could mitigate
> that somewhat by only loading the translation when the form loads and for
> the specific language as you mentioned.
> 
>   But if every programmer were to approach application design with the "load
> it into memory" approach because it's fast, then very quickly you can find
> that the OS will start paging out to disk.  So your really not in memory any
> more.
> 
>   That's why I think pulling something into memory like this is a waste.
> 
> FWIW,
> Jim.
> 
>  
> 
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
> Sent: Thursday, February 19, 2009 8:25 PM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Find First in an Array?
> 
> I was trying so hard to keep quiet.
> 
> ;-)
> 
> And how could seek over and over and over and over be any faster than
> sucking the results out of a collection?
> 
> OK, I'll go back to being quiet.
> 
> John W. Colby
> www.ColbyConsulting.com
> 
> 
> Drew Wutka wrote:
>> 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
> 
> --
> 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