running an sql script in Access

[rene reitsma]

Hi,

I wonder if anyone can tell me how to run an SQL script in Access.

I can run each of the commands in the script by running them as
separate queries,
(query-->SQL View-->run) but I don't know how to run the entire script
using a single command.

Any help will be appreciated.

RR

 

[John Vinson]

I don't know how to run the entire script
using a single command.

That's because Access' dialect of SQL does not run scripts.

You can create a Macro or (better) a VBA function/sub which executes
the queries in the desired sequence.

 

[Van T. Dinh]

If you are talking about Access ADP / MS-SQL Server, I
think the ADP doesn't like GO wheen you create SP.

Try replacing all the word "GO" in your script with semi-
colon.

Here is the String I tested in an AXP ADP

CREATE TABLE [dbo].[tblFactory]...;
CREATE TABLE [dbo].[tblInProcessData] ...;
CREATE TABLE ...


It seemed to work fine as the SP and 3 Tables were created
in MS-SQL Server. However, I could not open the SP in
DesignView again in the ADP (in MS-SQL OK) as Access
crashed when I tried).

HTH
Van T. Dinh
MVP (Access)