Running multiple SQL statements in the one operation

Question

I'm trying to use ADO to create several tables at once, into MS Access. Is it possible to do multiple statements in the one operation? For instance:

...
// I have omitted the field details 
CString sQuery = "CREATE TABLE [Table1] (..., PRIMARY KEY ([ID])); \nCREATE TABLE [Table2] (..., PRIMARY KEY ([ID]));";
oRecordset.Open(oDatabase.m_pConnection, sQuery)

This fails due to a "Syntax Error in CREATE TABLE statement", although each of the create statements work on their own perfectly. Is there a way of doing this sort of thing? There will also be statements to add constraints, add indexing, etc., and I'd really like to be able to do it so that I don't have to split up the string into separate parts.

Answer 1

ADO isn't the issue: the ACE/Jet engine simply does not support multiple SQL statements within a single operation. In other words, ACE/JET SQL lacks procedural syntax found in most 'industrial-strength' SQL products. See @David-W-Fenton's answer for more detail.

Bottom line: You will need to issue a Connection.Execute for each CREATE TABLE statement i.e. client side procedural code. But they can (perhaps should) all be run in the same transaction, of course.

Answer 2

ADO to MS Access does not support batch SQL statements. You need to run each statement as a separate execution.

Answer 3

People who think you can send multiple SQL statements to Jet in a batch just aren't thinking.

Jet is a file-server database engine -- there is no centralized server process controlling interaction between clients and the actual data store. Instead, clients are all running individual instances of Jet and cooperatively editing a file in a way that is controlled by the Jet locking file (LDB). Without a centralized process to serialize and prioritize the SQL statements, you wouldn't want Jet to be able to process multiple statements in a batch.

Those who are offering the suggestion of using ADO and separating the statements with a CrLf should code it up and give it a try and then get back to us about how useful their speculative advice actually is.

Answer 4

If you're sample set of commands is typical, just do something like this in VBA or the language of your choice:

public sub ExeuteBatch(BatchString as String)
    var s as string  
    var abatch as array  
    sbatch = replace(sbatch, "\n", "")
    abatch = split(BatchString, ";")
    for each s in abatch
        ** adodb execute s here **
    next s
end sub

That's off the top of my head, but you should be able to take it from there I hope.

Answer 5

Crude but it works - create the necessary number of queries with one SQL statement each, then use a Macro to run the queries successively. That's about as good as can be done with ADO/Jet.

Answer 6

I don't know if ADO is constructed over JET OleDB Engine, which I suppose, if it is this way, The Jet Engine doesn't support execution of multiple statements in one single batch, we tryed separating with ; and with the GO reserved word, but it does not work.

Answer 7

I think you can run multiple commands in one ADO Command.

You just need proper line feeds between then. i.e. \n doesn't work.

Try something like this: (Using VB Syntaxish)

MyQuery = "Select * from Whatever " & vbLf <br>
MyQuery = MyString & "Select * from SomethingElse " & vbLF

oRecordset.Open(oDatabase.m_pConnection, MyQuery )