Sub test()

Dim bCommit As Integer, bParam As Integer, bDoCmd As Integer
On Error GoTo e

For bDoCmd = True To False
    For bParam = True To False
        For bCommit = True To False
            Debug.Print bCommit, bParam, bDoCmd
            CurrentDb.Execute "delete * from t0"
            CurrentDb.Execute "INSERT INTO t0 ( f ) SELECT 1 AS Expr1"

            DBEngine( 0 ).BeginTrans
            If bDoCmd Then
                DoCmd.SetWarnings False
                If bParam Then
                    DoCmd.RunSQL CurrentDb.QueryDefs!a0.SQL, True
                    DoCmd.RunSQL CurrentDb.QueryDefs!a1.SQL, True
                    DoCmd.RunSQL CurrentDb.QueryDefs!a2.SQL, True
                Else
                    DoCmd.RunSQL CurrentDb.QueryDefs!a0.SQL, False
                    DoCmd.RunSQL CurrentDb.QueryDefs!a1.SQL, False
                    DoCmd.RunSQL CurrentDb.QueryDefs!a2.SQL, False
                End If
                DoCmd.SetWarnings True
            Else
                If bParam Then
                    CurrentDb.Execute "a0", dbFailOnError
                    CurrentDb.Execute "a1", dbFailOnError
                    CurrentDb.Execute "a2", dbFailOnError
                Else
                    CurrentDb.Execute "a0"
                    CurrentDb.Execute "a1"
                    CurrentDb.Execute "a2"
                End If
            End If
            If bCommit Then
                DBEngine( 0 ).CommitTrans
            Else
                DBEngine( 0 ).Rollback
            End If

            Debug.Print DCount("*", "t0")
        Next
    Next
Next

Exit Sub
e:
Resume Next

End Sub

test
- 1             - 1             - 1  
  9  
  0             - 1             - 1  
  9  
- 1               0             - 1  
  9  
  0               0             - 1  
  9  
- 1             - 1               0  
  7  
  0             - 1               0  
  1  
- 1               0               0  
  9  
  0               0               0  
  1  

DBEngine( 0 ).BegineTrans
    DoCmd.RunSQL "все что угодно"
DBEngine( 0 ).Rollback

DoCmd.RunSQL [Use Transaction]

Application.SetOption "Confirm Record Changes", False
Application.SetOption "Confirm Document Deletions", False
Application.SetOption "Confirm Action Queries", False
и т.п.

Dim q As DAO.QueryDef, p As DAO.Parameter 
  Set q = CurrentDb.QueryDefs("ИмяЗапроса") 'как обычного запроса Select,
  'так и INSERT/UPDATE; в запросах на удаление это не помогает
  For Each p In q.Parameters 
    p.Value = Eval(p.Name) 
  Next 
  q.Execute 
  q.close: Set q=Nothing

Dim q As DAO.QueryDef, p As DAO.Parameter 
  Set q = CurrentDb.QueryDefs("ИмяЗапроса")
  q.Parameters("[Введите начальную дату:]").Value=Cdate(Ваше_значение_параметра)
'и т.д. пока не переберете все параметры
  q.Execute 
  q.close: set q=Nothing

Dim q As DAO.QueryDef, p As DAO.Parameter 
  Set q = CurrentDb.QueryDefs("ИмяЗапроса")
  For Each p In q.Parameters 
    debug.print p.Name 
  Next 
  q.close: set q=Nothing

  Set rs = q.OpenRecordset(...)
  'а не Set rs = CurrentDb.OpenRecordset("ИмяЗапроса"), ...)

PARAMETERS [Forms]![ИмяФормы]![ИмяКонтрола] Text, ... ;
...
WHERE ИмяТаблицы.ИмяПоля=[Forms]![ИмяФормы]![ИмяКонтрола] ... 
...

Dim cmd As ADODB.Command
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim p As ADODB.Parameter
  Set cnn = CurrentProject.Connection
    'или если не используете текущее соединение:
    'Set cnn = New adodb.Connection
    'cnn.Open "Cтрока подключения по аналогии с CurrentProject.BaseConnectionString"
  Set cmd = New ADODB.Command
  With cmd
    .ActiveConnection = cnn
    .CommandText = "ИмяПроцедуры"
    .CommandType = adCmdStoredProc
    .NamedParameters = True
      'начиная с ADO версии 2.6 (???) без этого свойства работать не будет -
      'ADO будет заполнять коллекцию параметров не по имени, а по порядку
    For Each p In .Parameters
      'имена параметров равны именам контролов формы, только без @
      'Имя контрола NameFIO, тогда в процедуре @NameFIO. @- убираем с помощью Replace
      p.Value = Eval(Forms("ИмяФормы")(Replace(p.Name, "@", "",  1 ,  1 , vbTextCompare)))
    Next
    Set rs = .Execute(, , adExecuteNoRecords)
      'adExecuteNoRecords - можно и не указывать, если в процедуре стоит Set NoCount ON
      'или чтобы иметь возможность задать свойства rs (обновляемость, например):
      'Set rs = New ADODB.Recordset
      'rs.CursorType = adOpenDynamic
      'rs.Open cmd
  End With
  Set cmd = Nothing
  rs.Close: Set rs = Nothing
  cnn.Close: Set cnn = Nothing

Dim cmd As ADODB.Command
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim p As ADODB.Parameter
  Set cnn = CurrentProject.Connection
  Set cmd = New ADODB.Command
  With cmd
    .ActiveConnection = cnn
    .CommandText = "ИмяПроцедуры"
    .CommandType = adCmdStoredProc
    .NamedParameters = True
      'Cоздадим параметр nVarChar (500)
    .Parameters.Append .CreateParameter("@имяПарматра1", adVarWChar, adParamInput,  500 , "Некая строка")
      'создадим параметр типа int (SQL) - Long (VBA)
    .Parameters.Append .CreateParameter("@имяПараметра2", adInteger, adParamInput, , CLng( 111 ))
      'и т.д.
    Set rs = .Execute(, , adExecuteNoRecords)
  End With
  Set cmd = Nothing
  rs.Close: Set rs = Nothing
  cnn.Close: Set cnn = Nothing

Dim cmd As ADODB.Command
Dim cnn As ADODB.Connection
Dim p As ADODB.Parameter
  Set cnn = CurrentProject.Connection
  Set cmd = New ADODB.Command
  With cmd
  .ActiveConnection = cnn
  .CommandText = "ИмяПроцедуры"
  .CommandType = adCmdStoredProc
  For Each p In .Parameters
    Debug.Print p.Name
  Next
  Set cmd = Nothing
  cnn.Close: Set cnn = Nothing

On Error Goto mis
...
  DoCmd.SetWarnings False
  DoCmd.RunSQL ...
  DoCmd.SetWarnings True
...
Exit Sub
  mis:
    DoCmd.SetWarnings True

Public Function Test()
On Error Goto Rollback_Label
  DBEngine( 0 ).BeginTrans
    CurrentDb.Execute "Запрос1", dbFailOnError
    CurrentDb.Execute "Запрос2", dbFailOnError
    CurrentDb.Execute "Запрос3", dbFailOnError
  DBEngine( 0 ).CommitTrans
  Exit Function
Rollback_Label:
  DBEngine( 0 ).Rollback
End Function