Help with Multi-Statement SQLs in Microsoft Access

[tbenci]

Good morning all,

What I am about to ask WORKS with TUNIQUERY for SYBASE. Further, using this syntax in SQLDBX (a SQL editor) works with the Jet Engine BUT it does not work for TUNIQUERY for MS Access.

I have a block of code that;

1. CREATES A TEMPORARY TABLE

Code: Select all

SELECT
  t.*
INTO
  tmp_table
FROM
  Table as t;

2. DOES SOMETHING WITH THIS TABLE

Code: Select all

SELECT
  tmp.*
FROM
  tmp_tabl as tmp;

3. DROPS THE TEMPORARY TABLE

Code: Select all

DROP TABLE tmp_table;

When I run this in Delphi I get a "characters found after end of SQL statement". It seems to me that its parsing to the first instance of a ";" (the first statement) and considering all after it to be rubbish.

Thus, no multi-statement SQLs for Access.

Am I missing something? As I said, works for SYBASE and the JET engine quite happily executes this code from any number of SQL editors (e.g. SQLDBX).

Any thoughts appreciated.

Regards
Tony Benci

[AlexP]

hello,

MS Access doesn't support multi-SQL in a single query

[tbenci]

Hi Alex,

Actually, it does I think... this code runs perfectly well in SQLDBX (using ODBC to connect to an Access table).

Perhaps I need to run it in a script. It seems to execute OK but how do you get to the result set?

Cheers
Tony

Code: Select all

SELECT DISTINCT
  sch.sch_prospect_code AS code
INTO
  tmp_prospects
FROM
  ait_schedule AS sch
ORDER BY
  sch.sch_prospect_code;

SELECT DISTINCT
  sch.sch_salesperson_code AS code
INTO
  tmp_salespeople
FROM
  ait_schedule AS sch
ORDER BY
  sch.sch_salesperson_code;

SELECT
  *
FROM
  tmp_prospects
UNION  
SELECT
  *
FROM
  tmp_salespeople;

DROP TABLE tmp_prospects;
DROP TABLE tmp_salespeople;

[AlexP]

This means that SQLDBX executes commands in turn. In UniDAC, TUniScript is used for this purpose.