Devart Forums

Posted: **Mon 28 Mar 2016 22:22**

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

Posted: **Wed 30 Mar 2016 13:04**

hello,

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

Posted: **Thu 21 Apr 2016 11:31**

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;

Posted: **Mon 25 Apr 2016 08:39**

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

Devart Forums

Help with Multi-Statement SQLs in Microsoft Access

Help with Multi-Statement SQLs in Microsoft Access

Re: Help with Multi-Statement SQLs in Microsoft Access

Re: Help with Multi-Statement SQLs in Microsoft Access

Re: Help with Multi-Statement SQLs in Microsoft Access