[AccessD] Upsize wizard => Queries

Sander,

Can you use the DOCUMENTER in MS Access...select your queries...when it 
opens as a report...goto file save as table...open table and filter OBJECT 
TYPE field for "SQL Statement"...and this will give you the SQL for each 
query?

Just an after thought...

Mark A. matte


>From: "Arthur Fuller" <fuller.artful at gmail.com>
>Reply-To: Access Developers discussion and problem 
>solving<accessd at databaseadvisors.com>
>To: "Access Developers discussion and problem 
>solving"<accessd at databaseadvisors.com>
>Subject: Re: [AccessD] Upsize wizard => Queries
>Date: Wed, 11 Jul 2007 15:20:34 -0400
>
>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

_________________________________________________________________
http://newlivehotmail.com