Somos Expertos Access. Contacto atravez de Skype Skype logo expertos_7

Use Batch Transactions with SQL Server to Guarantee Results

Batch transactions are a great way to avoid orphan records and to guarantee the database has performed all of the actions you have requested. This post will discuss how you can incorporate batch transactions in Access VBA using SQL Server.

Why Batch?
Normally you would use referential integrity and delete cascade to avoid orphan records, for example, if you delete a customer record all of the corresponding orders, order details, contacts, etc. get deleted too. The alternative would be to use batched transactions.

Another common scenario are two or more transactions that must occur together or you end up with mixed results. For example, if you wish to create an order and order detail records at the same time. You can’t have order details without a order record, so it’s critical the order record get’s created before your order details record.

Why would the record not get created, isn’t SQL Server robust?
SQL Server ‘s transaction capability is not the issue here, your program and users are. If you have a user with a read-write lock on the entire Orders detail table, any new records may not post till that user’s lock is cleared. Given that the user may have left for lunch and leave the table in that state, your program may error out before the Reuben on Rye is consumed. There are strategies you should follow so that your program doesn’t fall to this scenario, but that’s a topic for another, (great), post.

Consider using Stored Procedures First
There, I said it. Consider using SPs first before using the technique in this article. The advantages are legion and SQL will always do a better job than Access. Still need to use Access? Then read on.

Recipe Ingredients #1: Mix ADODB connection object with recordset and error handling
To batch your SQL transactions you need to use the BeginTrans , CommitTrans and RollbackTrans actions of the ADODB connection object. Here’s an example:

You will notice that if any error does occur, it will trigger the RollBackTrans method of the connection object, which would roll back any activity since the batch started, which in this case would be either the new tblOrders record, tblOrderDetails record or both.

Recipe Ingredients #2: Using DAO Workspace and Execute method.

You may not realize it but DAO also supports transactions just as well as ADO and thus is a good choice when we’re using only Access data sources. But this time, we’ll execute a SQL statement instead of opening a recordset:

Though both DAO and ADO differs slightly in how you manage transactions, you can certainly choose to open a recordset or just execute a SQL command with either technology.

Why can’t we do this with bound forms?

Behind the scenes, Access is actually managing the transactions and will be sending transaction control messages to the backend based on the user’s interactions. At the time of writing, there is no means to override this behavior. Therefore, if you have a need for batch transaction, it may be desirable to either consider using an unbound form or a form bound to either a SQL Server View with an INSTEAD OF trigger or a stored procedure that conforms to updatability rules and binding the form with an ADO recordset, or a disconnected ADO recordset to simulate batching.

With this in mind, Leigh Purvis, an Access MVP has a testbed sample of using transactions with forms that may be interesting toward providing additional insights in how Access manages the transactions behind the curtains. You can find his example at his website and downloading the «Transaction in Forms» sample.

Happy Batching!

Article written with contributions from Ben Clothier

Acerca de

Juan Soto es un desarrollador senior de Access en IT Impact Inc. y un MVP de Microsoft Access y se especializa en Access con bases de datos de SQL Server. Su pasión por el acceso lo ha llevado ayudar a una amplia gama de empresas a establecer un entorno seguro, estable y eficiente con SQL Server. Él es un orador frecuente en los grupos de usuarios de Access en todo el país y recientemente habló en el Orange County SQL Saturday # 73. Si desea que Juan hable en su próxima reunión de grupo, puede ponerse en contacto con él aquí.

Publicado en SQL Server, VBA

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *



Quienes Somos es un servicio de la empresa IT Impact, Inc., una compañía de programación y servicios para empresas en Latino América. Ofrecemos servicios en .Net, SQL Server y Microsoft Access. Muchos de nuestros desarrolladores han obtenido el galardón de Access MVP, un título proveído por Microsoft a aquellos que han hecho aportes a la comunidad y que han demostrado tener conocimientos superiores del producto.

Nuestro Equipo

  • Le ayudamos a "Descubrir el poder de sus datos™" con reportes y sistemas de Access excepcionales .
  • Creamos soluciones de bases de datos personalizadas utilizando Microsoft Access y / o SQL Server.
  • Nuestros consultores ganaron sus estrellas en las empresas de servicios y/o manufactura antes de convertirse en programadores.

Blogs anteriores