Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
silas2
Yak Posting Veteran
65 Posts |
 Posted - 2005-08-10 : 14:16:00
|
| I'm just moving from Access/DAO, and I'm trying to keep some of my apps with the same code but running against an MSDE backend, using SQL Passthrough, everything is fine except DBEngine.BeginTrans/CommitTrans. With DAO, against an mdb, say you want to add an invoice/then the invoiceitems, only committing the tranactions if all the invoiceitems are added ok. The ref-integrity forces you to have the InvoiceID for the new InvoiceItems. With DAO transactions you get an autoincrement number back before you commit the transaction so you can use that for adding the invoiceitems, (adding the invoice and it's items being considered to be one "atomic" transaction.) SQL Server's transactions seem not to give you back an autoincrement number until you commit so you seem to have to split the transaction into multiple transactions which seems to defeat the point of transactions in the first place. |
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2005-08-10 : 18:55:11
|
| Not sure about DAO, but I know that ADO supports transactions on SQL Server. The DBEngine object you refer to is for the Access JET database engine, so probably wouldn't be applicable to SQL Server (although feel free to correct me...). I can see two ways of going about this, however both need some coding work: -port the add invoice code into a stored procedure (assuming the input data is of a consistent format - ie if you're adding invoice lines it may not be the best option) - port the code to ADO. It's not a big deal - the logical structure of your code will remain, just the objects will change.HTH,Tim |
 |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2005-08-10 : 21:50:07
|
I don't think it's an atomic transaction at all, unless you're using the access forms, which is why you can't add details unless you have the headeroutside access, if you have DAO and a grid and you insert an item, it's committedanother solution to your autoincrement number is commit the header, then query the autoincrement value, then use that for the details  HTHquote:
Originally posted by silas2
SQL Server's transactions seem not to give you back an autoincrement number until you commit so you seem to have to split the transaction into multiple transactions which seems to defeat the point of transactions in the first place.
--------------------keeping it simple... |
|
|
|
|
|
|
|
|
Moving From DAO Transactions