Hey Chris,

Tho I've never used it myself & am always apprehensive about what the Jet
providers do & don't support, but ADO connection objects are supposed to
support transactions, thru the .BeginTrans and .CommitTrans (or
.RollbackTrans) methods.  So, you should be able to do something like:

' this code is untested!!!
Public Sub x()
Dim cnnAccess As ADODB.Connection
Dim cmdUpdate As ADODB.Command
Dim strSQL As String
Dim booInTrans As Boolean

Set cnnAccess = New ADODB.Connection
Set cmdUpdate = New ADODB.Command

On Error GoTo Finish

   booInTrans = False
   cnnAccess.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" _
                           & "Data Source=YourFile.mdb;" _
                           & "Persist Security Info=False"
   cnnAccess.Open
   
   Set cmdUpdate.ActiveConnection = cnnAccess
   cmdUpdate.CommandType = adCmdText
   
   cnnAccess.BeginTrans
   booInTrans = True
   
   strSQL = "UPDATE workorders SET finish_date = Now() WHERE wonum = '5555'"
   cmdUpdate.CommandText = strSQL
   cmdUpdate.Execute
   
   strSQL = "UPDATE steps SET finish_date = Now() WHERE stepid = 5"
   cmdUpdate.CommandText = strSQL
   cmdUpdate.Execute
   
   strSQL = "UPDATE steps SET finish_date = Now() WHERE stepid = 6"
   cmdUpdate.CommandText = strSQL
   cmdUpdate.Execute
   

Finish:
   Select Case Err.Number
      Case 0
         cnnAccess.CommitTrans
      Case Else
         cnnAccess.RollbackTrans
   End Select
   
   booInTrans = False

   cnnAccess.Close
   Set cmdUpdate = Nothing
   Set cnnAccess = Nothing

End Sub
============================================================

Failing that, the corresponding DAO code certainly will work.

HTH,

-Roy

-----Original Message-----
From: Chris Tacke [mailto:ctacke@r...]
Sent: Friday, November 10, 2000 10:56 AM
To: professional vb
Subject: [pro_vb] Batches in Access??


I need run a batch or transaction through an Access database, but I have
some very limiting constraints on implementing it.

Essentially I need to be able to run a set of UPDATE statements against
multiple tables through a direct SQL call, not through a recordset.

Example:

I have 2 tables on a server and client, "workorders" and "steps".  The
client data is a subset of the server's.

I want to run the following SQL against the server:

UPDATE workorders SET finish_date = Now() WHERE wonum = '5555'
UPDATE steps SET finish_date = Now() WHERE stepid = 5
UPDATE steps SET finish_date = Now() WHERE stepid = 6

I want them all to run, or none of them (a batch or transaction).

The only way I can call the server is through 'Connection.Execute {SQL
Statement}'

I know with SQL Server I could just send all 3 statements as one string and
it would process each, but I don't think Access likes more than one
statement at a time.  Any ideas?



--------------------------------------------
Christopher Tacke, MCSD
Rubicon Technologies
(xxx) xxx-xxxx  x 319