Transactions for linked SQL Server Tables?

[Ace Calhoon]

Hello,
I have a VBA/Database application which reads files, analyzes them,
updates a database, and then moves them to an archive. I would like to
make this an atomic transaction -- that is, if the move to the archive
fails, I don't want the database to be updated (and if the database
update fails, I don't want the file moved...)

The logic I want is as follows:

Begin transaction

Read file

Update TableOne
Update TableTwo

Move file

End transaction

On Error: Rollback transaction

TableOne and TableTwo are linked tables from a SQL Server database.
TableTwo contains a foreign key which points to an entry in TableOne
(so adding the TableTwo entry fails if TableOne's entry hasn't been
added yet). They are accessed using the RecordSet object.

I have the code working without the transactions. But when I add them
(using DBEngine.Workspaces(0).BeginTrans, etc.) I recieve an ODBC error
when I attempt to update TableTwo.

Is there any other way to begin, end, and rollback a transaction
(perhaps on the SQL Server side?) And how would I invoke this in
Access 2003?

Thanks,
-Aaron

[david epsom dot com dot au]

Use Access97/Jet 3.5, or ADO, or stored procedures.

ODBC transactions are broken in DAO 3.6, and the more
work you put in, the more you realise how broken it
is.

Regarding your specific problem: you can't do that
with DAO 3.6. You might be able to make it work by
using an append query instead of an update query.

(david)

"Ace Calhoon" <ac********@gmail.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

Hello,
I have a VBA/Database application which reads files, analyzes them,
updates a database, and then moves them to an archive. I would like to
make this an atomic transaction -- that is, if the move to the archive
fails, I don't want the database to be updated (and if the database
update fails, I don't want the file moved...)

The logic I want is as follows:

Begin transaction

Read file

Update TableOne
Update TableTwo

Move file

End transaction

On Error: Rollback transaction

TableOne and TableTwo are linked tables from a SQL Server database.
TableTwo contains a foreign key which points to an entry in TableOne
(so adding the TableTwo entry fails if TableOne's entry hasn't been
added yet). They are accessed using the RecordSet object.

I have the code working without the transactions. But when I add them
(using DBEngine.Workspaces(0).BeginTrans, etc.) I recieve an ODBC error
when I attempt to update TableTwo.

Is there any other way to begin, end, and rollback a transaction
(perhaps on the SQL Server side?) And how would I invoke this in
Access 2003?

Thanks,
-Aaron

[Ace Calhoon]

Thanks for the help, I have it working now. Here are a couple of notes
for archival purposes.

Switching to ADO worked. However, it would require quite a bit of code
rewriting (because I was taking advantage of some queries to convert
names on the SQL Server database to something more recognizable).

I ran around in circles for a little while, because the database *had*
been working relatively recently. Eventually I just deleted all of the
links to the tables and recreated them... That appears to have done
the trick. I must have messed something up when I was experimenting
with the link table editor.

[Steve Jorgensen]

On 12 Sep 2005 08:30:11 -0700, "Ace Calhoon" <ac********@gmail.com> wrote:

Thanks for the help, I have it working now. Here are a couple of notes
for archival purposes.

Switching to ADO worked. However, it would require quite a bit of code
rewriting (because I was taking advantage of some queries to convert
names on the SQL Server database to something more recognizable).

I ran around in circles for a little while, because the database *had*
been working relatively recently. Eventually I just deleted all of the
links to the tables and recreated them... That appears to have done
the trick. I must have messed something up when I was experimenting
with the link table editor.

I just wanted to add a that holding transactions open on the front-end is not
a good practice if you can avoid it, regardless of the data access library
used - particularly for long-running batches. If I need to do batch
processing on the front-end, what I do is write records to a table that
describes the transactions to be done, then call a stored procedure on the
server to actually process the batch.

If multi-user updates are an issue, you can do a form of optimistic locking by
storing TIMESTAMP field values in the transaction table, and checking them
from inside the transaction in the stored procedure.