Running Multiple Sql Statements In The One Operation

September 28, 2023 Post a Comment

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

Solution 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.

Solution 2:

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

Solution 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.

Solution 4:

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

publicsub ExeuteBatch(BatchString asString)
    var s asstring  
    var abatch as array  
    sbatch = replace(sbatch, "\n", "")
    abatch = split(BatchString, ";")
    foreach s in abatch
        ** adodb execute s here **
    next s
endsub

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

Solution 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.

Lab Sql