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
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" <aceca...@gmail.com> wrote in message
news:1126297697.5...@z14g2000cwz.googlegroups.com...
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.