Multiple INSERT string

[IAW]

Can anyone help?

Have searched the net with no luck. This is my problem.

I am trying to use a multiple INSERT string to create records in a table, from a VB application and using ADO to do it.

If I send the single INSERT it works fine, but if I try to send more than one in the same string I get a 'MISSING SEMI COLON AT END OF STATEMENT' error.

I have tried putting one at the end of each statement, then just at the end of the INSERT statements string, but still get this error.

Can Access actually handle more than one SQL instruction at a time?

Thanks for any help.

 

[The_Doc_Man]

Yes and No.

No, in that a single thread must handle a single problem, be it query or report or form. Access is NOT written to be multi-threaded within a single workspace.

Yes, in that multiple copies of Access can run against the same back-end file, each doing a different thing. Each has its own single-threaded workspace, but each thread could be doing something different. The file-server will again force actions to be single-threaded because odds are the disk does not have multiple heads so you cannot drive the heads to two places at once. But you can have a queue of I/O actions waiting to support multiple queries.

Now, there is THIS to consider.

If you want to do INSERT a, b, c, d etc. etc. INTO table-x etc. etc. and then do another INSERT of e, f, g, h INTO table-y ... AND be sure that it is an all or nothing at all situation... look into BEGIN/COMMIT transaction processing. The Help Files will help you in this non-trivial topic.

 

[Pat Hartman]

You can't combine multiple SQL statements into a single string. If you have three inserts to do, you'll need to run three separate queries unless you are running an append query that takes data from one table and appends it to another.

 

[IAW]

Many thanks for that. Glad I am only planning to populate the table once!