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 >