Collecting several SQL Statements and Run them at the same time

[Marcel Stoop]

Hi everyone

I guess I have a bit of an unusual problem. I hope some one could point me
in the right direction.

Situation:
On Form X I have a Button called "Add". Every time a user hits this button,
Data is collected and a SQL Statement is to be performed:
Dim sSQLinsert As String
sSQLinsert = "INSERT INTO q (q1, q2, q3) VALUES (a, b, c);
DoCmd.RunSQL sSQLinsert

On that same Form I have a second Button called "Delete", this will perform
a Delete statement.

Problem:
I want to change this whole procedure.
As soon as an user hits either the "add" and/or the "Delete" Button, the
relating SQL should be gathered but not yet be performed.
This would mean that several "INSERT" and or several "DELETE" statements
have to be collected.

As soon as the User hits the "Save" button, all those several SQL statements
have to be run.
As soon as the User hits the "Cancel" button, no SQL statement will be
performed, and all the collected SQL statements will be cleaned away.

I would sure be glad if someone could point me in the right direction.

Cheers
Marcel

 

[Brendan Reynolds]

The example below adds SQL statements entered into a text box to an array.
The cmd_Execute_Click event procedure loops through the array, executing
each query in turn.

Option Compare Database
Option Explicit

Private m_astrSQL() As String

Private Sub cmdCancel_Click()

ReDim m_astrSQL(0)

End Sub

Private Sub cmdExecute_Click()

Dim lngKey As Long
For lngKey = LBound(m_astrSQL) To UBound(m_astrSQL) - 1
CurrentDb.Execute m_astrSQL(lngKey), dbFailOnError
Next lngKey

End Sub

Private Sub cmdQueue_Click()

m_astrSQL(UBound(m_astrSQL)) = Me.txtSQL.Value
ReDim Preserve m_astrSQL(UBound(m_astrSQL) + 1)

End Sub

Private Sub Form_Open(Cancel As Integer)

ReDim m_astrSQL(0)

End Sub

 

[Marcel Stoop]

Thanks for the help
I will try to implement it

Cheers
Marcel