Re: Buggy SQL Syntax for External File Path in Access 2007
- From: RD <progr@xxxxxxxxx>
- Date: Thu, 19 Mar 2009 13:14:50 -0700
On 6 Mar 2009 23:55:33 GMT, "David W. Fenton"
<XXXusenet@xxxxxxxxxxxxxxxxxxx> wrote:
<snip>
Hoping someone at least has an explanation,
[]. AS Alias is Jet SQL's syntax for derived tables. Standard ANSI
syntax is () As Alias, and when you type that in, you find that
Access converts it to Jet's syntax.
This is a good thing, seems to me.
And I've used it for years (I do lots of SQL with derived tables,
though mostly not in saved queries).
One option you might consider is setting your database to use ANSI
92 SQL, which will mean that you can use the standard () AS Alias
syntax. But it also means you have to use ANSI wildcards, % in place
of * and _ in place of ?.
Now, as to your other assertion, that the SQL breaks if you edit it,
I've never had that issue myself. Perhaps your edit is in error?
It's certainly the case that the bracket syntax means you can't have
any brackets inside the SELECT statement of your derived table, so
if you're introducing the need for brackets (e.g., by adding a field
to the SELECT clause that has a space or non-standard character in
its name), then that would definitely break it. If you've got
fields/tables with badly-designed names (i.e., with spaces and
non-alphanumeric characters) then you're going to have to use
brackets and in that case, ANSI 92 mode is probably going to make
life easier for working with derived tables (though it means
converting all the wildcards...).
But the better alternative is to NOT USE STUPID NAMES for fields and
tables. That means no spaces and no funny characters.
Of course, often you have no control over the schema so you're
forced to use badly-named fields/tables. In that case, I'd likely
create saved queries that alias the fields to good names, or if
using SQL Server, create a server-side view with the aliases and use
that in place of the basic table. That way, you don't have to worry
about mucking around with ANSI 92 mode and finessing issues with
brackets in SQL derived tables.
Thanks for that. (Been on vacation, sorry for delay)
I didn't know that about derived tables. I should probably delve a
little deeper into Jet.
I've thought about using ANSI 92, just haven't moved forward with it.
I rarely use wildcards, if I can help it.
Lol about the STUPID NAMES. I use perfectly good names in the
databases I design. Unfortunately, most of the databases I support
were created by a complete idiot (and she was well paid). I've
considered posting some of what I find on the Daily WTF but there are
too many snobs there that would reply, "TRWTF is Access!"
I often use intermediate queries to rename fields into someting sane.
Anyway, thanks.
RD
.
- References:
- Re: Buggy SQL Syntax for External File Path in Access 2007
- From: RD
- Re: Buggy SQL Syntax for External File Path in Access 2007
- From: David W. Fenton
- Re: Buggy SQL Syntax for External File Path in Access 2007
- Prev by Date: Re: Import a txt file into table.
- Next by Date: Re: Buggy SQL Syntax for External File Path in Access 2007
- Previous by thread: Re: Buggy SQL Syntax for External File Path in Access 2007
- Next by thread: Re: Buggy SQL Syntax for External File Path in Access 2007
- Index(es):
Relevant Pages
|
