Arthur Fuller
artful at rogers.com
Wed Feb 4 12:00:37 CST 2004
Use views not sprocs for this sort of thing. Or alternatively, use table UDFs that accept a parameter so you can scope them to your needs. You might think that the performance hit of views will be huge, but not so. For example, I have a form that I call the Sales Browser, which defaults to sales of the past month by date descending. An option group on the form footer offers various selections: 30, 60, 90 and All. Each of these corresponds to a view that SELECTs * FROM Sales WHERE DateEntered >= GetDate()- x, where x is one of those values. The All button uses a view that skips the WHERE clause. (That's an oversimplification. There's one view corresponding to ALL and then 3 other views that select from that view and apply the GetDate() thing.) I realize that this answer does not correspond exactly to your question. However, it offers a solution that works, and has been tested with loads of data and a large number of users. HTH, Arthur -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of John W. Colby Sent: Sunday, February 01, 2004 1:31 PM To: Access Developers discussion and problem solving 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 --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.564 / Virus Database: 356 - Release Date: 1/19/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.564 / Virus Database: 356 - Release Date: 1/19/2004