How do I execute multiple SQL statements in Access??

[nickB]

I want to create a query that will use a series of INSERT statements to
update the database. Each INSERT statement will create a new product record
in the db. However, when I try to save this query, at the end of the first
SQL statement, right after it reads the semicolon at the end of the
statement, I get an error message: Characters found after end of SQL
statement. (Error 3142). But the only thing following is another INSERT
statement. Why will Access SQL not recognize and execute the 2nd INSERT
statement???

 

[John W. Vinson]

Why will Access SQL not recognize and execute the 2nd INSERT
statement???

Because in Access (unlike, say, SQL/Server) you can have only one SQL
statement in a query.

Sorry, but you'll need to run these multiple queries AS multiple queries; they
can all be run from one Macro or VBA procedure, though.

 

[David W. Fenton]

Because in Access (unlike, say, SQL/Server) you can have only one
SQL statement in a query.

Sorry, but you'll need to run these multiple queries AS multiple
queries; they can all be run from one Macro or VBA procedure,
though.

This question comes up a lot from people who just don't understand
Jet/ACE. There is no centralized process to marshall requests from
clients of the database, so it's impossible to logically serialize
and interleave a series of SQL commands for maximum efficiency and
reliability. It's only server databases that can reliably process a
batch of SQL statements, because only server databases have the
central point of control to make sure one client doesn't hog the
entire server while a string of 1000 SQL statements is executed.
With Jet/ACE, sharing of the data is cooperative, and thus SQL
statements have to be executed one at a time.

 

[david]

This comes up a lot of time from people who don't realise that
when Jet was first designed,

(1) The native languages for storing procedures in Jet were the
macro language, and Access Basic,

and (2) The main purpose of stored procedures in Server database
systems was to implement Referential Integrity, which was
implemented in JET using DRI, removing the main purpose of
having stored procedures,

and (3)
SQL Server was updated to add features (like DRI, and replication,
and long table names, and ....) from Jet, but Jet was never really
updated to add features from SQL Server, like Transact SQL.

However, Jet, when it was first built, had a perfectly good system
for serialising SQL commands, using Transactions, and the
locking system provided by SHARE, the MS OS database API.

SHARE was the basis for the centralized process to marshall
requests from the database clients: in the Windows NT stream
it was adsorbed into the network client.

The centralize process for marshalling requests still works except
when it is broken, people just have to remember that the embedded
language in this Relational Database Management System is VBA,
not TSQL.

(david)