Several Insert Into sql statements in MS Access at same time

[ltanvo]

Hi,

I try to run several insert into sql statements in direct MS access at same time. For ex.

INSERT INTO Mytable(F1, F2) VALUES('V11', 'V12')
INSERT INTO Mytable(F1, F2) VALUES('V21', 'V22')
INSERT INTO Mytable(F1, F2) VALUES('V31', 'V32')
INSERT INTO Mytable(F1, F2) VALUES('V41', 'V42')

How can I do it? Any hints?

It seems that MS Access editor does not support several INSERT INTO statements running at the same time. Right?

Thanks in advance.

- Loi -

[PEB]

Yeah

It's thrut!

However you can pass a SQLs to a function that runs them
like:

Docmd.RunSql "SQL"

one after other

[NeoPa]

If you look up under help for the Execute method from a connection / querydef, you should find reference to

dbRunAsync Executes the query asynchronously (ODBCDirect Connection and QueryDef objects only).

You will have to manage determinating when they're all complete in your code.
The help explains it fairly fully.

[PEB]

If you look up under help for the Execute method from a connection / querydef, you should find reference to

You will have to manage determinating when they're all complete in your code.
The help explains it fairly fully.

Yeap maybe if the backend database is different from Access using db.Execute can execute multiple actions...

But different database... Not the Jet!

[NeoPa]

Sorry PEB.
This also supports QueryDef objects in Jet as well.
That means it must be a saved query (not just a SQL string - I think).

[PEB]

In fact in my database i'm using always
mydb.Execute (SQL)
with SQL string... And never tried to use it with QueryDef /Saved query/ And don't know if it works with saved query
I should test it!

But first create an Action query and save it... coz all my action queries aren't saved...

Maybe this is true for Make tables queries... that i don't use??? I've remembrerd thet there was a pb with action query that produces a table as result!

[NeoPa]

Don't forget that the Execute procedure works with Connections or Databases in one format, but also with QueryDefs in another.

Execute Method


Runs an action query or executes an SQL statement on a specified Connection or Database object.

Syntax

object.Execute source, options

querydef.Execute options

The Execute method syntax has these parts.

Part Description
object A Connection or Database object variable on which the query will run.
querydef An object variable that represents the QueryDef object whose SQL property setting specifies the SQL statement to execute.
source A String that is an SQL statement or the Name property value of a QueryDef object.
options Optional. A constant or combination of constants that determines the data integrity characteristics of the query, as specified in Settings.

[PEB]

Sure , and it seems that it was one of the methods to run a parameterd action query...

Before using the Querydef.execute,
declaring the parameters like

querydef("[Myparameter1]")="test1"
querydef("[Myparameter1]")="test2"
and so on..

And how difficulties i've had till i found this method...

[nico5038]

When saved queries are needed (like for some docmd.transfer... commands) I usually use the Tabledef object like:

dim td as DAO.querydef

set td = currentdb.querydefs ("qryDummy") ' this needs to be defined !

td.SQL = "INSERT bla bla bla..."

Now all processing with qryDummy can be executed with the filled SQL.
In this case of a number of INSERT queries you could import the statements into a table and add a recordset processing loop to execute the assign statement like:

td.SQL = rs!querySQL
docmd.runquery (td.name)

Idea ?

Nic;o)

[NeoPa]

Nice idea Nico.
I use a similar concept sometimes (only when there's some reason strSQL doesn't work properly).
I find that the Record Locks, Recordset Type and ODBC Timeout properties can be critical in the execution of some queries so I have a dummy QueryDef set with my default values that I reuse (I usually make a copy before amending though).

[Killer42]

From the context, I think the original problem that ltanvo had was that he/she was trying to do multiple INSERTs in the SQL view of the Query editor. Which you can't do, obviously.

ltanvo, if this is the case, you could create a macro to issue the series of INSERTs as a bunch of RunSQL commands.

[NeoPa]

LOL - that's ironic.

I think your interpretation must be the right one Killer. It didn't occur to me to look at it that way.
I thought it was a funny sort of (advanced) question for someone to ask in that way.
Well if anyone's interested in the other (probably unrequired) info - it's all there!

BTW That can be done on other servers (MS SQL for instance) by separating each SQL instruction with a ';'. I'm pretty sure that's not supported in Access though (except via Pass-Through Queries to other systems).

[MMcCarthy]

Create a loop.

Do until some trigger
Obtain two values and put into local variables.
DoCmd.RunSQL "INSERT INTO TableName (Field1, Field2) VALUES (" & variable1 & "," variable2 & ");"
Loop

Note: string variables will need to be enclosed with single quotes as well.