jwcolby
jwcolby at colbyconsulting.com
Wed Jul 11 14:38:20 CDT 2007
LOL, Right you are Arthur. And stop using static functions, and references to controls on forms and functions that are VBA but don't exist elsewhere and... Hmmm... You are correct on all counts Arthur, however I think that if a move to SQL Server is in order, then they probably got a huge usage out of the database and should just pay up to do a conversion. All of the things mentioned are shortcuts that only work in Access and are part of what makes Access a RAD environment. Moving to client server is a whole nother ball game and trying to build every app in anticipation of that trip is not useful IMHO. Those who need it pay for it, those who don't, don't. It is relatively trivial to write code to open forms and look for SQL statements in combos etc, and even to build saved queries using that SQL statement, embedding the object name in the querydef name. For example qfrmXyzCboAbc. That would "build stored queries" and voila, you can then try the upsize and see what fails now. If I have a major upgrade happening I could write such code quickly enough. John W. Colby Colby Consulting www.ColbyConsulting.com -----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