Martin Reid
mwp.reid at qub.ac.uk
Mon Feb 2 07:12:34 CST 2004
Have you tried using a stored procedure as the recordsource of a form. Try it and see what happens!! Martin ----- Original Message ----- From: "John W. Colby" <jwcolby at colbyconsulting.com> To: "Access Developers discussion and problem solving" <accessd at databaseadvisors.com> Sent: Sunday, February 01, 2004 6:31 PM Subject: RE: [AccessD] was SQL Server queries - appending strings > Martin, > > I long ago modified my framework to use only ADO > > >The framework you use is I assume dictating the path you can take?? > > Not entirely. To be honest, I haven't yet tried to hook it up to an ADP and > see what happens. However I am a programmer at heart. In my apps I do > things like setting up a query, then opening the query and changing the SQL > statement. There are instances where this is a very useful trick. Try that > with an SQL Server BE! It may be possible, but not just using DAO and the > queries collection! > > I use the "LIKE MyForm!MyControl" a LOT. I use VB functions in aliased > fields in queries. I write my own functions and use them in aliased fields > in queries. > > ALL of these things will cause massive headaches porting to SQL Server, and > referencing controls on a form simply can't be ported. > > A2K, even in an ADP (by my understanding) can't hook a stored procedure to > the recordsource of a form and end up with an editable form. > > So the answer to your question is yes, my framework is a limitation, but a > tiny one compared to my free usage of Access "goodies" that just kill you > using SQL Server. > > This is an application with over a hundred forms, thousands of controls, > tabs with JIT subforms set up, combos that swap out their SQL to allow > on-the-fly filtering, and so forth. All this is entirely legal and > encouraged in an Access only app. > > !!!!!!!!!!!!*********** > > Did you know that NO code runs in a form until after the data loads for the > form? So if you want to run code that in any way modifies the actual SQL > statement of the form, you will either: > > Load all your data, then reload using the modified SQL > > or... not have a recordsource and assign one from the code that runs after > the form's class finally gets around to loading. > > !!!!!!!!!!!!*********** > > Did you know that subforms load before the main form code runs? > > Did you know that subforms expect the parent data to be loaded so that the > Link Child Field / Master Field works? > > !!!!!!!!!!!!*********** > > All of this means that if you want to use bound subforms / forms, the parent > data must load, then the child forms load (with their data), THEN the parent > form's CODE finally runs. > > > I noticed that all of my data was loading twice. Why is that? Because I > use code to set a control which is used in the where clause of the form's > SQL statement. The first time, the data loads, but the CODE hasn't started > running yet. So... I have to set the control and requery the form. > Hmmm.... > > So you could just not set the Recordsource at all (leave it blank) until the > code runs, then set the control, then set the recordsource RIGHT? > > Well... yea, EXCEPT the subforms expect data to be there when they load or > you get the stupid pop up dialog asking you for the PK for the Master Field. > THAT MEANS that JIT Subforms are REQUIRED so that the parent form has NO > data, it's code runs, it loads ITS data, then it loads any subforms which, > by that time, have valid data in the parent form and can load without asking > the user for the PK. > > Holy @#$% Batman! > > Now WHY oh WHY didn't MS just allow the code to run in the parent form > before loading its data? Because lots of code expect to go looking at > (already loaded) data. Oh the joys of Access. > > !!!!!!!!!!!!*********** > > My framework is CAPABLE of doing exactly this stuff, automagically. If the > parent form's Recordsource is not set (is blank), AND (of course) the > subform controls are not bound to a subform, then my form's class looks for > a query named 'q' & Thisform.name. If found, the class set the form's > recordsource to that query name, which causes that data to load (after > setting the above mentioned control which is used in the Where clause). The > form's class has a control scanner that finds all subform controls and drops > a pointer to them into a collection. Once the main form's recordsource is > set, each subform is able to be bound to a subform. USUALLY this is done > when a specific tab of the tab control is clicked. i.e. JIT. However if the > subform is on the FIRST tab, then it is bound by the main form's class so > that the subform loads and is populated. > > So now my forms only load their data once, JIT works as expected, I can > filter using controls on the form etc. > > Will ANY of that work in A2K using SQL Server as a BE? DAMNED DOUBTFUL. > > !!!!!!!!!!!!*********** > > SQL Server MAY be the holy grail, but it is far holier if you are just > pulling one record from one table RE 1970s style terminal based apps. It > isn't very holy at all if you have already used all of Access' built in > TRICKS to the max in your apps and now want to move that app to SQL Server! > > John W. Colby > www.ColbyConsulting.com > > -----Original Message----- > From: accessd-bounces at databaseadvisors.com > [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Martin Reid > Sent: Sunday, February 01, 2004 12:36 PM > To: Access Developers discussion and problem solving > Subject: Re: [AccessD] was SQL Server queries - appending strings > > > John > > Tha main drawback you face moving to SQL Server is your use of the > framework. Continued reliance of this tool will place you at a disadvantage > when working with for example an ADP file. > > Of course you could consider over time rewriting the tool for use with SQL > Server. > > Moving to an SQL Server backend generally gives you a few options > > Use ODBC and stay with DAO > Rewrite from DAO to ADO using OLEDB > Use an ADP > USee Pass Through > > etc etc > > You could also consider bring the data local on login , work with it local > and then update the server with changes. > > I am currently working on rewriting a complex app from DAO to ADO. Taking a > lot longer than I assumed but almost done now. > > The licence issue is OK. Usually licenced on a per processor basis now so > connections dont matter. > > The framework you use is I assume dictating the path you can take?? > > > > Martin > > > _______________________________________________ > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com >