[AccessD] Upsize wizard => Queries

Heenan, Lambert Lambert.Heenan at AIG.com
Wed Jul 11 14:26:16 CDT 2007


With respect, at no point did I see the term "embedded SELECTs" or anything
like it. The questioner was asking about "Queries", which are of course
QueryDefs in code land.

That said, you're quite right. SQL code in modules come back to bight
because of their poor maintainability.

Lambert

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Arthur Fuller
Sent: Wednesday, July 11, 2007 3:21 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Upsize wizard => Queries


The biggest single thing you can do to get from Here to There is check all
your recordsouces and rowsources for instances that begin with "SELECT".
Before you migrate, visit every instance and turn it into a named query.
Failure to do this results in a) a bunch of views/sprocs with names similar
to "~#;flkjh", which may be meaningful to you but certainly are not to me.
Following this simple rule results in error messages that precisely identify
the queries that wouldn't port. These failures can occur for several
reasons, among which are:

1. use of a built-in Access function that doesn't exist in SQL. 2. (the one
that got me many times) use of static functions that I wrote that don't
exist in SQL. 3. differences in syntax (they're aren't a lot but there are
more than a dozen).

So. My recommendation is, if you're not already too deep in the puddle to
retreat, back up and start again. Somewhere around I have code that walks
all the rowsources and recordsources looking for the word SELECT as the
first part of the string, then dumps all those and their consumers to a
file. I haven't needed it for a while, so it may take a bit of time to dig
it up. Alternatively, if you have Rick Fisher's FR or Speed Ferret you can
narrow the search scope and quickly locate these errant bits of code.

My second recommendation is, even if you think you're going to work in
Access to your twilight days, STOP planting SELECT statements in the code
and control properties. It's asinine. Create named queries.

In case my point wasn't clear, Create Named Queries!

(With respect, your air code misses most of the problems that arise.
QueryDefs are easily identified and fixed or rewritten. It's the embedded
SELECTs that kill ya.)

Arthur


On 7/11/07, Heenan, Lambert <Lambert.Heenan at aig.com> wrote:
>
> Sure.
>
> <AIR CODE>
>
> Dim qd as QueryDef
> Dim strSQL as String
>
>        For Each qd in QueryDefs
>                strSQL = qd.SQL
>                ' Save the sql somewhere
>        Next qd
>
> </AIR CODE>
>
> Lambert
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Sad Der
> Sent: Wednesday, July 11, 2007 2:45 AM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Upsize wizard => Queries
>
>
> Ok, was afraid of that. Is there a way to retrieve the
> SQL text of the query in a automated way?
>
> Regards,
>
> Sander
>
>
>
> --- "Robert L. Stewart" <robert at webedb.com> wrote:
>
> > The syntax for the queries is too different to be
> > migrated. You had to do that yourself.
> >
> > Or, you can change your default to be SQL-92 (I
> > think)
> > compliant. Then you can simply copy and paste the
> > SQL
> > between them. But, either way, you still have to do
> > it
> > yourself.
> >
> > Robert
> >
> > At 07:26 AM 7/10/2007, you wrote:
> > >Date: Tue, 10 Jul 2007 04:37:42 -0700 (PDT)
> > >From: Sad Der <accessd666 at yahoo.com>
> > >Subject: [AccessD] Upsize wizard => Queries
> > >To: Acces User Group <accessd at databaseadvisors.com>
> > >Message-ID:
> > <460545.53873.qm at web31611.mail.mud.yahoo.com>
> > >Content-Type: text/plain; charset=iso-8859-1
> > >
> > >Hi group,
> > >
> > >Using the upgrade wizard I can migrate the tables
> > and
> > >data in a couple of minutes to SQL Server.
> > >
> > >(Action) Queries are not migrated.
> > >
> > >Does anybody know of a tool that migrates queries
> > to
> > >sql server views and/or sp's?
> > >
> > >TIA!!!!
> > >
> > >Regards,
> > >
> > >Sander
> >
> >
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com 
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
>
>
>
>
>
> ______________________________________________________________________
> ______
> ________
> Be a PS3 game guru.
> Get your game face on with the latest PS3 news and previews at Yahoo!
> Games.
> http://videogames.yahoo.com/platform?platform=120121
> --
> 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