Stored Procedures in MS-Access

V

vellaima

Well-known member
Joined
Jan 29, 2003
Messages
109
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 shouldnt 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.
 
With Microsoft Access? No, I dont believe so, nor would it even be practical. Access is made for personal use, and wasnt designed to support transactions.
 
As Derek mentioned it wouldnt be practical in Access.

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

Code: 
    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
 
Actually thats one of the little known features, since the Access interface doesnt allow the user to input them directly, for whatever reason. Thanks for reminding us about that, Robby.
 
I cant 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 connections 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
 
Thanks a lot Nerseus. This is the solution i was looking for. Access is supporting the BeginTransaction.
 
The same way youd execute an sql statement except set the CommandType to CommandType.StoredProcedure. If the stored procedure accepts parameters then youll need to add those to the Command objects parameters collection.
 
Back
Top