[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