[AccessD] Undocumented Access SQL syntax - Derived tables

Hi Charlotte et all

Did you see the thread "Exporting Using Code" by Paul?

I found out about the trailing dot in the [SQL]. syntax for a virtual
table.

First, of course, it shows that we have a virtual table and not a
bracketed field name.
Second, if you, say, export to a text table via SQL, the "database
name" is the _directory_ where the file will be created while the file
name is the "table name" which is appended in brackets as well.
So here comes the dot in:

  SELECT
    *
  INTO
    [Text; Database=d:\tempdir].[export.txt]
  FROM
    tblSomeTable;

This I haven't seen elsewhere.

/gustav


> We've discussed this trick before in the list.  I found it back in A97,
> where it was problematic and had to be used with care, and I've used it
> in 2k and XP.  There was a tip on this in the December 2001
> Access-VB-SQL Advisor magazine, called "Create Derived Tables in Jet".
> As I recall, they are covered in O'Reilly's Access Database Design &
> Programming as well.

> Charlotte  Foust


> -----Original Message-----
> From: Michael Maddison [mailto:michael at ddisolutions.com.au] 
> Sent: Thursday, July 29, 2004 8:15 PM
> To: Access Developers discussion and problem solving
> Subject: [AccessD] Undocumented Access SQL syntax - Derived tables


> Hi list,

> I've been mainly working in SQL 2K for a while now and have grown to
> love using derived tables in my sql statements.  A shame you can't do
> the same in Access! You thought so, well so did I.  A colleague came
> across a website (no link,sorry) which shows how to do derived tables in
> MS Access.

> Undocumented Syntax
> SELECT .* 
> FROM
> [SELECT sum(x) FROM FOO]. BAR

> ***Notice the [] and the space after the .

> This works like a charm, even the query designer likes it!  The only
> drawback I've found (apart from being undocumented) is the derived
> tables cannot have column names with square brackets arround them. So 
>         SELECT .* 
>         FROM
>         [SELECT sum([My Badly Named Field]) FROM FOO]. BAR
> Will not parse correctly.

> Try it out.

> cheers

> Michael M