If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How do I execute multiple SQL statements in Access??
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??? |
#2
|
|||
|
|||
How do I execute multiple SQL statements in Access??
On Sat, 13 Mar 2010 14:25:02 -0800, nickB
wrote: 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. -- John W. Vinson [MVP] |
#3
|
|||
|
|||
How do I execute multiple SQL statements in Access??
John W. Vinson wrote in
: On Sat, 13 Mar 2010 14:25:02 -0800, nickB wrote: 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. 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 W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ |
#4
|
|||
|
|||
How do I execute multiple SQL statements in Access??
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) "David W. Fenton" wrote in message 36.82... John W. Vinson wrote in : On Sat, 13 Mar 2010 14:25:02 -0800, nickB wrote: 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. 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 W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ |
Thread Tools | |
Display Modes | |
|
|