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.