Stored Procedures in MS-Access

[vellaima]

Hello,

 

The details that are entered in a form in VB.NET are inserted in 3 different tables when "OK" button is clicked. I would like to either commit the transactions or rollback. Meaning if there any errors occurring while inserting into table2 during runtime then it shouldn't insert the values in table1 and table3 too.

 

Can the above mentioned problem be acheived in MS-ACCESS.

 

Please do help me by providing a sample.

[Derek Stone]

With Microsoft Access? No, I don't believe so, nor would it even be practical. Access is made for personal use, and wasn't designed to support transactions.

[Robby]

As Derek mentioned it wouldn't be practical in Access.

 

But you can can still create/use Stored Proc in Access....

 

   Private Sub ProductsProcs()
       Dim sSQL As String

       ' procProductsList - Retrieves entire table
       sSQL = "CREATE PROC procProductsList AS SELECT * FROM Products;"
       CreateStoredProc(sSQL)

       ' procProductsDeleteItem - Returns the details (one record) from the JobTitle table
       sSQL = "CREATE PROC procProductsDeleteItem(inProductID LONG) " & _
              "AS DELETE FROM Products WHERE ProductID = inProductID;"
       CreateStoredProc(sSQL)

       ' procProductsAddItem - Add one record to the JobTitle table
       sSQL = "CREATE PROC procProductsAddItem(inProductName VARCHAR(40), inSupplierID LONG, inCategoryID LONG) " & _
              "AS INSERT INTO Products (ProductName, SupplierID, CategoryID) Values (inProductName, inSupplierID, inCategoryID);"
       CreateStoredProc(sSQL)

       ' procProductsUpdateItem - Update one record on the JobTitle table
       sSQL = "CREATE PROC procProductsUpdateItem(inProductID LONG, inProductName VARCHAR(40)) " & _
              "AS UPDATE Products SET ProductName = inProductName WHERE ProductID = inProductID;"
       CreateStoredProc(sSQL)


   End Sub


   Private Sub CreateStoredProc(ByVal sSQL As String)
       Dim con As OleDbConnection
       Dim cmd As OleDbCommand = New OleDbCommand()
       Dim da As OleDbDataAdapter

       Dim sConStr As String = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=c:\Northwind.mdb"

       con = New OleDbConnection(sConStr)

       cmd.Connection = con
       cmd.CommandText = sSQL

       con.Open()
       cmd.ExecuteNonQuery()
       con.Close()

   End Sub

[Derek Stone]

Actually that's one of the little known features, since the Access interface doesn't allow the user to input them directly, for whatever reason. Thanks for reminding us about that, Robby.

[Nerseus]

I can't say if Access supports them or not, but a connection can call BeginTransaction, which returns a Transaction object that will do what you want. You must assign the connection's returned Transaction object to the Command object for each Command to be part of the same transaction.

 

Check out this link for more info.

 

-Nerseus

[vellaima]

Thanks a lot Nerseus. This is the solution i was looking for. Access is supporting the BeginTransaction.

[Mladen]

how can I use the stored procedures created in this way?

[quwiltw]

The same way you'd execute an sql statement except set the CommandType to CommandType.StoredProcedure. If the stored procedure accepts parameters then you'll need to add those to the Command object's parameters collection.