Update Multiple Tables??

  • Thread starter Thread starter Charles A. Lackman
  • Start date Start date
C

Charles A. Lackman

Hello,

How do you make a SQL querry that will update multiple tables, in Access
(Jet 4.0 Database). I am using a transaction and parameters and have tried
the following with no success..

ACommand.CommandText = "INSERT INTO Loan VALUES(?,?); INSERT INTO Signature
VALUES(?,?)"

ACommand.Parameters.Add("@Value1", OleDb.OleDbType.WChar)
ACommand.Parameters(0).Value = Value1.Text
ACommand.Parameters.Add("@Value2", OleDb.OleDbType.WChar)
ACommand.Parameters(1).Value = Value2.Text
ACommand.Parameters.Add("@Value3", OleDb.OleDbType.WChar)
ACommand.Parameters(2).Value = Value3.Text
ACommand.Parameters.Add("@Value4", OleDb.OleDbType.WChar)
ACommand.Parameters(3).Value = Value4.Text

Try
AConnection.Open()
ATransaction = AConnection.BeginTransaction
ACommand.Transaction = ATransaction
ACommand.ExecuteNonQuery()
ATransaction.Commit()
AConnection.Close()
Catch ... etc
ATransaction.RollBack()
Finally
Clean Up ...
End Try

(this works if I am only doing one table)
what am I doing wrong?
Thanks,
Chuck
 
Jet won't process multiple SQL commands as you have them here -- the
syntax for passing multiple SQL statements with a ; delimiter works
with SQL Server, not Jet. Create two separate commands, each with
their own parameters, and wrap both in a single transaction.

--Mary
 
Back
Top