Arthur Fuller
fuller.artful at gmail.com
Wed Jul 11 14:20:34 CDT 2007
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 >